how to use a variable to reference database names

Hi, I found your email address from discuss you posted on
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
I need to have a query like below to run to select all the fields from
different databases that each database have a table named 'table1'
select * from test...table1
select * from test1..table1
I have the following code sample:
declare @.dbname varchar(50)
set @.dbname='test'
select @.dbname
select * from @.dbname..table1
However I received the error messge when I ran this code. Can anyone
help to resolve this?
ThanksThats not working, you have to use dynamic sql then:
DECLARE @.SQLString VARCHAR(200)
SET @.dbname='test'
SET @.SQLString = 'SELECT * FROM ' + @.dbname + '..table1'
EXEC(@.SQLString)
HTH, jens Suessmeyer.|||set nocount on
go
set transaction isolation level read uncommitted
go
set quoted_identifier off
go
declare @.dbname varchar(128)
declare @.table varchar (128)
set @.table='table1'
declare cr_opendb cursor fast_forward for select name from
master.dbo.sysdatabases
open cr_opendb
fetch next from cr_opendb into @.dbname
while @.@.fetch_status = 0
begin
EXEC ("select b.name from "+@.dbname+".dbo.sysobjects a join
"+@.dbname+".dbo.syscolumns b on a.id=b.id where a.name='"+@.table+"'")
fetch next from cr_opendb into @.dbname
end
close cr_opendb
deallocate cr_opendb|||foru123 schrieb:
> Hi, I found your email address from discuss you posted on
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
> I need to have a query like below to run to select all the fields from
> different databases that each database have a table named 'table1'
> select * from test...table1
> select * from test1..table1
> I have the following code sample:
> declare @.dbname varchar(50)
> set @.dbname='test'
> select @.dbname
> select * from @.dbname..table1
> However I received the error messge when I ran this code. Can anyone
> help to resolve this?
> Thanks
>
Dynamic SQL is what you are looking for. Look at "sp_executesql" in the
SQL Server BOL:
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
hth
Stefka|||Thats not working, you have to use dynamic sql then:
DECLARE @.SQLString VARCHAR(200)
SET @.dbname='test'
SET @.SQLString = 'SELECT * FROM ' + @.dbname + '..table1'
EXEC(@.SQLString)
HTH, jens Suessmeyer.|||Thats not working, you have to use dynamic sql then:
DECLARE @.SQLString VARCHAR(200)
SET @.dbname='test'
SET @.SQLString = 'SELECT * FROM ' + @.dbname + '..table1'
EXEC(@.SQLString)
HTH, jens Suessmeyer.|||set nocount on
go
set transaction isolation level read uncommitted
go
set quoted_identifier off
go
declare @.dbname varchar(128)
declare @.table varchar (128)
set @.table='table1'
declare cr_opendb cursor fast_forward for select name from
master.dbo.sysdatabases
open cr_opendb
fetch next from cr_opendb into @.dbname
while @.@.fetch_status = 0
begin
EXEC ("select b.name from "+@.dbname+".dbo.sysobjects a join
"+@.dbname+".dbo.syscolumns b on a.id=b.id where a.name='"+@.table+"'")
fetch next from cr_opendb into @.dbname
end
close cr_opendb
deallocate cr_opendb|||Thats not working, you have to use dynamic sql then:
DECLARE @.SQLString VARCHAR(200)
SET @.dbname='test'
SET @.SQLString = 'SELECT * FROM ' + @.dbname + '..table1'
EXEC(@.SQLString)
HTH, jens Suessmeyer.|||Could not figure out why I am getting this error message when executing
your following code. If you scroll down the message tab you will see the
following error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '3'.
*** Sent via Developersdex http://www.codecomments.com ***|||Never mind. The error message comes from a database name started with a
number.
*** Sent via Developersdex http://www.codecomments.com ***