Queries between databases

We have some diffrent databases on our servers and we sometimes want to
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?You can add views to your database which include the database name for the
external objects then you can just access the view name.
It means releasing different views in the different envronments but the rest
of the code will stay the same.
"stuckish@.gmail.com" wrote:

> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
[vbcol=seagreen]
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegroups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>
>