Reference another database without hardcoding the name of it...

Hi,
I s there any way to access another database without hardcoding the name in
the stored procedure?
for example:
database1.dbo.sp_Test()
begin
select * from database2.dbo.supertable
end
So instead of writing "database2" (since this name can change in test
environments) is there another way to reference it? One way could be to
dynamicly create some sql, where the name is found in a table - are there
others?
So the database name must set at runtime rather than compile time...
CheersIn SQL Server 2005, you could create a synonym.
In SQL Server 2000, the method I use is what you describe, get the name of
the server and/or database and build a dynamic string. <yuck>
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
> Hi,
> I s there any way to access another database without hardcoding the name
> in
> the stored procedure?
> for example:
> database1.dbo.sp_Test()
> begin
> select * from database2.dbo.supertable
> end
> So instead of writing "database2" (since this name can change in test
> environments) is there another way to reference it? One way could be to
> dynamicly create some sql, where the name is found in a table - are there
> others?
> So the database name must set at runtime rather than compile time...
> Cheers|||In SQL Server 2000, you can't dynamically change the database or table
referenced in a select, update, delete statement without resorting to
building the statement in a varchar and then executing it using Exec. Is it
really necessary to prefix the table name with the database? It's best for
the development / test database to be deployed on a seperate server or
instance.
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
> Hi,
> I s there any way to access another database without hardcoding the name
> in
> the stored procedure?
> for example:
> database1.dbo.sp_Test()
> begin
> select * from database2.dbo.supertable
> end
> So instead of writing "database2" (since this name can change in test
> environments) is there another way to reference it? One way could be to
> dynamicly create some sql, where the name is found in a table - are there
> others?
> So the database name must set at runtime rather than compile time...
> Cheers

Reference another database without hardcoding the name of it..

Thanks Aaron
Sorry for not informing on the product, but i am working on 2000.
Some think like this could be nice:
declare @.database varchar(50)
set @.database = 'Database2'
select * from object_id(@.database).dbo.Test_Table
But your solution is like this:
declare @.database varchar(50)
declare @.sql varchar(1000)
set @.database = 'Database2'
set @.sql = 'select * from '+@.database+'.dbo.Test_Table'
executesql @.sql
Cheers
"Aaron Bertrand [SQL Server MVP]" wrote:

> In SQL Server 2005, you could create a synonym.
> In SQL Server 2000, the method I use is what you describe, get the name of
> the server and/or database and build a dynamic string. <yuck>
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
>
>But that's not valid T-SQL syntax.
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:B281CBFB-3B65-4A75-A728-DF6C151105A4@.microsoft.com...
> Thanks Aaron
> Sorry for not informing on the product, but i am working on 2000.
> Some think like this could be nice:
> declare @.database varchar(50)
> set @.database = 'Database2'
> select * from object_id(@.database).dbo.Test_Table
> But your solution is like this:
> declare @.database varchar(50)
> declare @.sql varchar(1000)
> set @.database = 'Database2'
> set @.sql = 'select * from '+@.database+'.dbo.Test_Table'
> executesql @.sql
> Cheers
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>