Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Unanswered: parametrised servername and catalog name

    Hi,

    I intend to use four-part table names to select data from a Linked Server into local tables. There will be stored procedures containing Insert-Select statements.

    While I'm developing, I'm pointing to a development version of the remote server. In production, the remote server will be different. There will be many other situations, where I will need to link to one remote server or another. But I don't want to recompile my stored procs every time.

    The question is: can I use variables for the first two parts of a four-part table name. Something like:

    declare @svr varchar(20)
    declare @dsn varchar(20)
    select @svr = 'Pervasive_Test', @dsn = 'D_drive'
    SELECT * from @svr.@dsn..remote_table

    Would the above work ?

    I have to go through the ODBC Provider. The only way my query works is when I also specify the DSN as "catalog" in the second position of the table name. I definitely do not want the name of the DSN to be hardwired into my stored procs.

    Andrew

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    your example wont work, though you can use
    sp_executesql (see BOL) to run commands with variable object names,

    eg.

    set @statement = N'select user_id from users
    where cc_number = '+''''+ltrim(rtrim(@cc_number))+''''

    insert into #master_id (user_id)
    exec sp_executesql @statement


    though for the sort of thing you are doing, I usually create a batch file which uses a tool like SED to replace each token (defined earlier on object names) with the desired values , and then use the relevant modified script for each installation...just have the bat file receive the server/tablname as params

Posting Permissions

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