Alternative to link server

Hi all,

I am having two databases(MS-Sql) on two different servers.Let say they are Server1 and Server2.I am having some stored procedures(sps) which are executing on Server1.Results given by these sps are 6 different tables(Theses tables are temporary tables e.g #Table1 and they are created in one of the sps on Server1). And I want to use these 6 tables on Server2.
But constraint here is, i can create link server for Server1 from Server2 but not from Server1 to Server2.So i can not access Server2 directly from Server1.
Even if i am using custom tables here instead of temp tables(#) it will take me to solution but that is again a constraint i can not do this.
Is there any alternative solution for Link server in this case?
I dont want to go for OPENROWSET and OPENDATASOURCE b'coz of performance issue.

Thank you in advance!!

Kedar Waghmode.

Why can't create linked server? Did you get error or something else?

No i can create a link server but its a constraint put by administrator that he dont want any link server on his Server.


Thanks for help Rmiao.

Kedar Waghmode

Then nothing you can do.

Declare @RemoteServer Varchar(150)
Set @RemoteServer = 'XP_CMDShell ''OSQL -S ServerName -d DataBase -U UserName -P Password -Q "Select * From Table1"'''
EXEC(@RemoteServer)

Not sure if this solves original issue.

Facts :
1. No Linked Server can be used from Server1 to Server2.
2. Only temporary tables can be populated by the sps running on Server1

Following is a suggestion that might or not work (nothing is lost if you try it, except of 'valuable' time).

1. #temporary tables are valid only for the session of the code execution. Thus this is a GREAT restriction. In order to make "temporary" data available outside the session of the exetuting code you need to keep data in a different "container", a container that can be accessible outside running code. The only way is to use a global ## temporary table.

2.There has to be a way of communication between two servers. Since no Linked server can be configured for both servers/sides, "efficient" communication can be established only from one side (linked server from 2 to 1).
In order to trigger the communication at server1 side, you need some kind of statement and in that case OPENROWSET or OPENDATASOURCE is the only way.
Notice: OPENROWSET will NOT be used to transfer data from 1 to 2 (since you are worried for performance issues) BUT for starting/firing the communication from server 2 to 1.

Thus, use OPENROWSET from within the server1_procedure that creates 6 ##tables to start a server2_JOB. In that case
OPENROWSET will be executed in couple of msecs (no performance issues).
[
start a job from tsql: EXEC msdb.dbo.sp_start_job 'my_job_name'
execute a procedure using OPENROWSET: SELECT *
FROM OPENROWSET('SQLOLEDB', 'my_sqlserver_name';'my_sqluser_login';'my_sqlpass word',
'EXEC mydatabase.dbo.mystoredprodedure') AS tmp

so finally: SELECT *
FROM OPENROWSET('SQLOLEDB', 'my_sqlserver_name';'my_sqluser_login';'my_sqlpass word',
'EXEC msdb.dbo.sp_start_job ''my_job_name''') AS tmp
]
server2_JOB, will try to get data (using linked server from 2 to 1) that reside in the ##global temporary tables on server 1.

The most hard part of this scenario (if all other parts are possible) is how to keep ##global tables on server1 "alive", so that all data are "consumed" by
server2 side.

Since nothing of the above is tested, it might not work at all. (just an idea)

--HTH--

Following code tested in a combination of SQL2000 (server2) that has a linked server configuration to an installation of SQL2005 Express. OPENQUERY was used to pull data out of global temporary tables on server1, since it was not successfull to reference a global table with a four part name, like serverName.tempdb.dbo.##globaltmptable.
(Is that possible ???, so would apreciate if anyone could share information on that)

Description: Procedure of server1 keeps data in global temporary tables (two in this example).
There are two jobs on server2 each one transfering data from global temporary tables of server1 to corresponding tables on server2 :tbl1_of_server1, tbl2_of_server1 .
Jobs were configured to 'sa' owner, and also OPENROWSET in the stored procedure (at server1 side) used login credentials of 'sa' to connect to server2 and activate the jobs.
Test procedure on server1 has an additional ##control table to keep track if a job is connected to a global table and pulls data out of it, so that procedure code will not finish before ##globaltables are referenced (a way to keep ##tables "alive")

In order to configure code for testing:
Replace mylinked_server with the name of the linked server (server1). Replace my_server2 with a valid name for server2 'sa' with a valid username for connecting to server2 and also assign a valid my_server2_password .

Note: use sp_serveroption procedure on server2 to enable RPC,
RPCOUT for linked server1, if not enabled.


/********* script for server2 (SQL2000) **********/
--create tables on server2 that will keep data of global temporary tables created on server1
USE master
GO
create table dbo.tbl1_of_server1 (id INT, fld1 VARCHAR(10))
create table dbo.tbl2_of_server1 (id INT, fld2 VARCHAR(10))
GO

--create jobs on server2 that will transfer data from ##global tables of server1
--to tblx_of_server1 tables

DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

--# first job
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'getdata_from_linkedserver1', @owner_login_name = N'sa', @description = N'Get data from linked server (##global temporary table1)', @category_name = NULL, @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'

--notify that data retrieval started
INSERT INTO OPENQUERY(mylinked_server , ''SELECT fld FROM ##control'')
VALUES (''table1'')

--get data of global temporary table1 on linked server
INSERT INTO dbo.tbl1_of_server1(id, fld1)
SELECT a.id, a.fld1
FROM OPENQUERY((mylinked_server, ''SELECT * FROM ##tbl1'') AS a

--drop table on linked server
exec (mylinked_server.master.dbo.sp_executesql N''drop table ##tbl1''

', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
--# end of first job


--# second job
SET @JobID = NULL
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'getdata_from_linkedserver2', @owner_login_name = N'sa', @description = N'Get data from linked server (##global temporary table2)', @category_name = NULL, @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'
--notify that data retrieval started
INSERT INTO OPENQUERY((mylinked_server, ''SELECT fld FROM ##control'')
VALUES (''table2'')

--get data of global temporary table2 on linked server
INSERT INTO dbo.tbl2_of_server1(id, fld2)
SELECT a.id, a.fld2
FROM OPENQUERY((mylinked_server, ''SELECT * FROM ##tbl2'') AS a

--drop table on linked server
exec (mylinked_server.master.dbo.sp_executesql N''drop table ##tbl2''

', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
--# end of second job

/********* end of script for server2 (SQL2000) **********/



/********* script for server1 (SQL2005 Express) **********/
USE master
GO

CREATE PROCEDURE dbo.transferdata_to_unlinked_server
AS
SET NOCOUNT ON
DECLARE @dummy TINYINT
DECLARE @runner INT

--table that keeps track of unlinked_server_job progress
CREATE TABLE ##control (fld VARCHAR(10))

--global table 1
CREATE TABLE ##tbl1 (id INT, fld1 VARCHAR(10))

--fill global table1 with 10000 records
SET @runner = 1
WHILE @runner <= 10000
BEGIN
INSERT INTO ##tbl1(id, fld1)
SELECT @runner, CAST(@runner AS VARCHAR(10))
--move to next iterator
SET @runner = @runner + 1
END

--call unlinked_server2 job1, to start consuming data out of global table 1
BEGIN TRY
SELECT @dummy = 1
FROM
OPENROWSET ('SQLOLEDB', 'SERVER=my_server2;UID=sa;PWD=my_server2_password', --fill in login details for server2 (=unlinked_server)
'EXEC msdb.dbo.sp_start_job N''getdata_from_linkedserver1''; select 1' ) as a
END TRY
BEGIN CATCH
--do nothing
END CATCH



--keep code in a loop till job of unlinked server pulls data
--out of global table 1: ##tbl1
WHILE NOT EXISTS (SELECT * FROM ##control WHERE fld = 'table1')
BEGIN
CONTINUE
END


--move to table 2
CREATE TABLE ##tbl2 (id INT, fld2 VARCHAR(10))

--fill global table2 with 30000 records
SET @runner = 1
WHILE @runner <= 30000
BEGIN
INSERT INTO ##tbl2(id, fld2)
SELECT @runner, CAST(@runner AS VARCHAR(10))
--move to next iterator
SET @runner = @runner + 1
END

--call unlinked_server2 job2, to start consuming data out of global table 2
BEGIN TRY
SELECT @dummy = 1
FROM
OPENROWSET ('SQLOLEDB', 'SERVER=my_server2;UID=sa;PWD=my_server2_password', --fill in login details for server2 (=unlinked_server)
'EXEC msdb.dbo.sp_start_job N''getdata_from_linkedserver2''; select 1' ) as a
END TRY
BEGIN CATCH
--do nothing
END CATCH

--keep code in a loop till job of linked server pulls data
--out of global table 2: ##tbl2
WHILE NOT EXISTS (SELECT * FROM ##control WHERE fld = 'table2')
BEGIN
CONTINUE
END


--at this point data transfer to unlinked server has been started
SELECT 'data transfer has been started' as message

DROP TABLE ##control


/********* enf of script for server1 (SQL2005 Express) **********/




--use the following code to clean up testing procedures, jobs, tables
/****** clean up, must be executed on server2 (SQL2000) ******/
--drop tables
DROP TABLE master.dbo.tbl1_of_server1
DROP TABLE master.dbo.tbl2_of_server1

--drop jobs
EXEC msdb.dbo.sp_delete_job @job_name = N'getdata_from_linkedserver1'
EXEC msdb.dbo.sp_delete_job @job_name = N'getdata_from_linkedserver2'

--drop procedure on linked server1 (SQL2005 Express)
exec mylinked_server.master.dbo.sp_executesql N'USE master; DROP PROCEDURE dbo.transferdata_to_unlinked_server'

/****** end of clean up ******/

--HTH--

double post, sorry :-(

Sorry for late reply.

I thank you all for your suggestions.

@pandians
In you reply when u say "Select * From Table1",Are you saying Table1 is custom table not temporary table?.If yes then also your suggestion is not complete solution for this case.We have thought about it and kept it as last option.
Please let me know you suggestion.

@mikr0s
This may sound so much funny but we had used these global temp table(##) idea but it was creating some problems while two users are working on web portal(data mismatch).Another stand alone application is also using this database.
So we can not go for that.
I will check for your second SQL2000-SQL2005 solution and i will update you.

I again thank you all for valuable suggestions.

Kedar Waghmode