Unanswered: parametrised servername and catalog name
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:
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.
your example wont work, though you can use
sp_executesql (see BOL) to run commands with variable object names,
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