Results 1 to 10 of 10

Thread: Alternative to link server

  1. #1
    Join Date
    Oct 2007
    Posts
    8

    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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Why can't create linked server? Did you get error or something else?

  3. #3
    Join Date
    Oct 2007
    Posts
    8
    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Then nothing you can do.

  5. #5
    Join Date
    Oct 2007
    Location
    Chennai
    Posts
    1

    Thumbs up Yes U Can


    Declare @RemoteServer Varchar(150)
    Set @RemoteServer = 'XP_CMDShell ''OSQL -S ServerName -d DataBase -U UserName -P Password -Q "Select * From Table1"'''
    EXEC(@RemoteServer)
    Last edited by pandians; 10-11-2007 at 08:38 AM.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Not sure if this solves original issue.

  7. #7
    Join Date
    Sep 2005
    Posts
    168
    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--

  8. #8
    Join Date
    Sep 2005
    Posts
    168
    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--

  9. #9
    Join Date
    Sep 2005
    Posts
    168
    double post, sorry :-(
    Last edited by mikr0s; 10-14-2007 at 12:00 PM.

  10. #10
    Join Date
    Oct 2007
    Posts
    8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •