Dynamic Server Name in SQL Statements (from clause)

Question

My scenario is I have a thick client side app using a SQL DB. The use of the app is divided into 10 "sites" mostly by Geography but some by Usage. I've written an SP to archive selected data out of the DB and put it elsewhere as the DBs are growing to nearly 500GB in size at this point. Further whilst most servers host one application DB there is one server (and instance) that hosts 3 DBs.

My dilemma is I want to write code that is the same everytime. Ideally I would write something like

declare @dbname varchar(20)
set @dbname = 'Archive' + DB_NAME(DB_ID())

INSERT INTO @dbname.dbo.ArchiveTable (ID, DATA)
SELECT (ID, DATA) from TheData
WHERE Archive = 1

That way I can have the same SP installed on each server, any bug/feature fixes doesn't need me to manually type up 10 different SPs to install.

In the real world I'm also limited by SQL2000 however I'd be interested to hear in ideas using SQL2000 and/or SQL2008R2. The servers are a mixture of both.

As I understand it Synonyms wouldn't help as on the instance with 3 DBs I'd still end up with each DB having it's own copy of the SP with it's own hard coded definition of which named synonym to use.

I also don't feel dynamic SQL statements would be a good fit. There's more to it than the example snippet above and I use table variables to marshal all the work to be done- so that would be out of scope for all the other statements I need to work with.

Answer 1

Dynamic SQL will work. I have written similar things in SQL Server 2000 this way.

EXEC() can take a string variable as input. Construct this string as you show in the question, using the current DB context.

Instead of table variables, however, use temporary tables (#SomeName). These are visible in nested scopes, once defined. So create the #Table, then construct the SQL string referencing the temp table, then EXEC that string.

Answer 2

The synonym is owned by the database so you can have the same synonym name in different databases pointing to tables in different archive databases. The stored procedure can then be the same in all databases using the synonym(s).

You do of course have to create one synonym for each table in the archive db that you want to use from the SP.

Answer 3

I see where you said that dynamic SQL might not work for you and maybe my suggestion is an example of that. One option you 'might' consider is having an outer SP that would cursor through sys.databases and dynamically drop/create the static named synonym and then call your main archive SP which would use the 'current' synonym database