Generalized database references

Question

Generalized database references

carllgrant

SSC-Addicted

Points: 460
More actions
November 14, 2002 at 3:56 pm

#80402

Does anyone have a suggestion for how to generalize database references in stored procedures to minimize code changes when moving code to production? I realize this can be done using EXEC with constructed strings, but I wonder if there's an easier way.
For example, I want a way to express either of the following by a functionally substituting a variable for an object at any level in the hierachy:
[TestMachine\TestServer].TestDB.dbo.TestTable
[ProductionMachine\ProductionServer].ProdDB.dbo.ProdTable
-Carl

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Unfortunately, that is not allowed in TSQL at this point, don't foresee it happening in the future either. As you said EXEC or even sp_executsql could be used. I personally would just make a template with the values that would need to change and save the script then when needed you change the values and run against the prod server but again that is not what you want just a way you can get around the fact it cannot be done.

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 2

You can do it by creating a linked server with an aliased name then write all your code against the aliased name.

For example:

In Development the server name is DEVSERVER

Alias a linked server connection to MarketingApp

In Test or QA the server name is TESTSERVER

Alias a linked server connection to MarketingApp

ETC...

then your references in code would be to MarketingApp.Database.owner.table

and so forth....

Same code, different servers....

In each enviroment, the aliased name would of course point to the correct server. Code would not change between enviroments at all.

Now, that said, let me mention the down side. By addressing a linked server for the local connection, you take a transaction which would be run locally by default and cause it to be a distributed transaction because it runs against the linked server even though its really on the same machine. To date, this has caused me no problems, but I couldn't swear that it wouldn't have consequences in some different enviroment. I would suggest testing your application and current connections against a linked server connection rather than the server and see what happened in a controlled enviroment first. If it works as well for you as it does for me, your in business.

Edited by - Scorpion_66 on 11/14/2002 4:35:20 PM

carllgrant SSC-Addicted Points: 460 More actions · Answer 3

Thanks for your speedy responses - I appreciate it. Given the constraints, I think I'll settle for the template option. It would be great if SQL Server had global search and replace for SPs!

- Carl

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

Carl, In SQL, global search and replace for procs is a query useing the command replace run against the right sys table...

Edited by - Scorpion_66 on 11/14/2002 4:56:34 PM

carllgrant SSC-Addicted Points: 460 More actions · Answer 5

quote:
Carl, In SQL, global search and replace for procs is a query useing the command replace run against the right sys table...
Thanks - it was too obvious for me, I guess!
Edited by - Scorpion_66 on 11/14/2002 4:56:34 PM