Post reply

Query linked server where linked server name not known at design time

  • November 12, 2010 at 8:42 pm

    #228973

    Looking for best way to, with T-SQL only, remotely execute DML when remote server is not known at design time.

    Scenario - Caller passes name of linked server to stored proc. Store procedure then:

    1) Execs DML at remote server to have remote server create a temp table:

    SELECT TOP(10000) id INTO @linkedservername.dbname.schema.temptable2

    FROM @linkedservername.dbname.schema.temptable1 -- ERROR! Need best equiv

    2) Chunk rows:

    WHILE EXISTS (SELECT * FROM @linkedservername.dbname.schema.temptable2) -- ERROR!

    BEGIN

    UPDATE localtable t1 INNER JOIN @linkedservername.dbname.schema.temptable2 t2

    ON t1.id = t2.id -- ERROR! Need best equiv

    DROP TABLE @linkedservername.dbname.schema.temptable2

    SET ROWCOUNT 10000

    DELETE @linkedservername.dbname.schema.tablename1

    SELECT TOP(10000) id INTO @linkedservername.dbname.schema.temptable2

    FROM @linkedservername.dbname.schema.tablename1 -- ERROR! Need best equiv

    END

    No client involved. Run as Sql Agent job. Can't create sprocs at remote server. Can't use/redefine synonym. Want to be able to run against multiple linked servers at the same time. Could write separate sprocs for each linked server, but that's no fun slash extra work when linked servers are added over time.

    These don't work:

    OPENQUERY( @linkedservername, @DML) -- doesn't accept variables as params.

    EXECUTE (@DML) AT @linkedservername -- errors out

    This works (but is nasty! And will take forever to figure out the join across the wire):

    DECLARE @sourceLinkedServer NVARCHAR(30), @sql NVARCHAR(500)

    SET @sourceLinkedServer = N'[linkedservername]'

    SET @sql = N'SELECT * INTO tempTblInsideLinkeServerDB FROM table WHERE col1 = x'

    SET @sql = @sourceLinkedServer + N'.master.sys.sp_executesql N''' + @sql + ' '' '

    EXECUTE sp_executesql @sql

    Help much appreciated.

  • November 15, 2010 at 11:46 am

    #1250881

    The last one is the correct method.

    For linked servers (or server name in the 4 part name), you cannot use variables directly.

    Therefore, you need to store your SQL statement in a string and insert the server name there, and then execute your string with EXEC or sp_executesql.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply