November 11, 2002 at 3:56 pm
Hello again,
I've written some import scripts that move data from one database to another.
insert into mig_test.dbo.customer(...)
select (...)
from rmatv.dbo.customer
is it possible to alias the names of the databases so, if the name of the database changes I just change the alias. I've tried
doing
Declare @@database varchar(50)
set @@database = 'mig_test2.dbo'
select * from @@database.customer
but this always fails.
any suggestions
Thanks
Randy
November 11, 2002 at 4:06 pm
We alias databases for our different enviroments. We create a linked server pointed at the database and give it whatever name we like, In our case: Prod, QA, TEST, and DEV
We also have a few where the alias is consistent through the enviroments but point at different databases. This way, code written to work against one enviroment does not have to have code rework to address different servers in the next enviroment. ie. "Address" points to three different address repositories in the three different real enviroments involved. The schema is the same, just the server pointed to is different.
This also allows for generic scripts to work with multiple enviroments by simply pointing the alias linked server to a new location.
Hope that helps...
November 11, 2002 at 4:15 pm
The only way you will be able to do what you are asking is to build a dynamic statment so that you can use a variable to reference the database name.
Something like this
Declare @database varchar(50)
DECLARE @sqltext varchar(1000)
set @database = 'mig_test2.dbo'
set @sqltext = 'select * from ' + @database + '.customer'
EXEC (@sqltext)
November 11, 2002 at 6:18 pm
Scorpion, thats an interesting idea. Not sure I like it yet, but interesting! Typically we will use conditional compilation in our apps to vary the server, dbname will be same for dev and production.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply