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.

Andrewyour 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