Post reply

Paramaterized linked server references

  • May 1, 2003 at 3:49 pm

    #58883

    Is there any way to pass in the linked server name to a stored proc in order ot run against a separate server?

    For example, to pass in @server as a varchar(15) and then query

    @server.db_name.owner.table_name

    I can't find anything on this anywhere on the web.

    Thanks in advance for any comments.

  • May 1, 2003 at 6:42 pm

    #455961

    For example;

    declare @cmd varchar(255)

    declare @server varchar(20)

    set @server = 'PROFILES'

    set @cmd = 'select * from ' + @server + '...RAM'

    exec (@cmd)

  • May 2, 2003 at 7:08 am

    #455983

    I suppose I should have clarified... I want to do this without using dynamic SQL.

  • May 28, 2003 at 1:11 am

    #458760

    Because, by varying the server name, you are varying the object you a referring to, that is dynamic SQL.

    Except, we have objects that "alias" to other objects .. views to tables, linked servers to servers.

    So you could create a view that references what you want. Although creating the view is dynamic SQL.

    I have dynamically created linkedservers. Actually I setup some linkedservers, passed the name of one of them to a SP, which made a copy of the linkedserver under a different name, so it is effectively a variable linked server. No dynamic SQL is required because everything is parameters to sp_addlinkedserver.

    An obvious problem is contention for the 'variable' linkedserver - two processes trying to use it differently. Its only appropriate for overnight sorts of things, and even then I created a locking record.

Viewing 4 posts - 1 through 3 (of 3 total)

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