Query question

Hi,
I am trying to use two databases in one query... and here is the simple
query I have created but it it throwing an error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.db1'.
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE @.db1
USE @.db2
SELECT * from authors
SELECT * from customers
What is that I am doing wrong... I went through SQL manual but no
help..
HJThe line number is wrong...
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.db1'.|||Oh by the way I am a complete novice to SQL and SQL server 2000 so
please exuse my stupid question...|||You can't have a variable for the USE command, quite simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147809433.886525.234400@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to use two databases in one query... and here is the simple
> query I have created but it it throwing an error:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '@.db1'.
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE @.db1
> USE @.db2
> SELECT * from authors
> SELECT * from customers
> What is that I am doing wrong... I went through SQL manual but no
> help..
> HJ
>|||got ya.. so how about something like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE PUBS
USE NORTHWIND
SELECT * from @.db1.dbo.authors
SELECT * from @.db2.dbo.customers|||Same problem. You cannot have a variable as the database qualifier in a SELECT statement.
Perhaps you can explain what you want to achieve in the end, and you might get some suggestions...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147810701.999538.273200@.38g2000cwa.googlegroups.com...
> got ya.. so how about something like this...
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE PUBS
> USE NORTHWIND
> SELECT * from @.db1.dbo.authors
> SELECT * from @.db2.dbo.customers
>|||Here is what I want to do... I want to use two different tables from
two different databases on the same server. I could always write full
names of the tables and databases in my script but as I am a novice, I
thought there must be someway to put table_names and db_names in a
variable...
After doing some research and copy & paste... I came with something
like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
Declare @.tbl_name1 varchar (40)
Declare @.tbl_name2 varchar (40)
select @.db1 = 'PUBS'
select @.db2 = 'NORTHWIND'
select @.tbl_name1 = 'authors'
select @.tbl_name2 = 'customers'
/*USE PUBS
USE NORTHWIND */
EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
That worked but still do not understand that EXEC () thingy...
anyother way to accomplish this?
Thank you
HJ|||On 16 May 2006 13:37:04 -0700, Hitesh Joshi wrote:
(snip)
>EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
>EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
>That worked but still do not understand that EXEC () thingy...
>anyother way to accomplish this?
Hi HJ,
http://www.sommarskog.se/dynamic_sql.html
--
Hugo Kornelis, SQL Server MVPsql