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.