Showing posts with label informing. Show all posts
Showing posts with label informing. Show all posts

Friday, March 9, 2012

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:
>