dynmica use @db

Hi There,
I've number of databases, each one is having invoice table and a amount
fields in it.
I want to use somethig like this in a dynamic way using loop, i can generate
all database what all i need in a cursor, but i can not use like this
use @.db_name in a loop
use db1
select @.amt = sum(amount) from invoice
use db2
select @.amt = sum(amount) from invoice
@.tot = @.tot + @.amt
print @.tot
Thanks
GaneshYou can use 3-part naming like :
SELECT @.tot = SUM( amount )
FROM ( SELECT SUM( amount ) FROM db1.dbo.invoice
UNION ALL
SELECT SUM( amount ) FROM db2.dbo.invoice
UNION ALL
... ) D ( amount ) ;
PRINT @.tot
Anith|||If you really want to do it in a loop...and already have the Cursor logic
setup, you need to do something like this:
DECLARE db_cursor CURSOR FOR
SELECT dbname FROM yourtable
--Get first name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE (@.@.fetch_status <> -1)
BEGIN
SELECT @.cmd = 'use ' + @.dbname
EXEC (@.cmd)
select @.amt = sum(amount) from invoice
set @.tot = @.tot + @.amt
FETCH NEXT FROM db_cursor INTO @.dbname
END
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:22D76D45-8A91-4DFD-84DC-1BB2D15DEA97@.microsoft.com...
> Hi There,
> I've number of databases, each one is having invoice table and a amount
> fields in it.
> I want to use somethig like this in a dynamic way using loop, i can
> generate
> all database what all i need in a cursor, but i can not use like this
> use @.db_name in a loop
> use db1
> select @.amt = sum(amount) from invoice
> use db2
> select @.amt = sum(amount) from invoice
> @.tot = @.tot + @.amt
> print @.tot
>
> --
> Thanks
> Ganesh|||I got error message like this
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Invoice'.
Thanks
Ganesh
"VC" wrote:

> If you really want to do it in a loop...and already have the Cursor logic
> setup, you need to do something like this:
> DECLARE db_cursor CURSOR FOR
> SELECT dbname FROM yourtable
> --Get first name
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> SELECT @.cmd = 'use ' + @.dbname
> EXEC (@.cmd)
> select @.amt = sum(amount) from invoice
> set @.tot = @.tot + @.amt
> FETCH NEXT FROM db_cursor INTO @.dbname
> END
>
> "Ganesh" <gsganesh@.yahoo.com> wrote in message
> news:22D76D45-8A91-4DFD-84DC-1BB2D15DEA97@.microsoft.com...
>
>|||Well, then the table Invoice must not exist in the particular db that you
are in when the error occurs.
I would add a statement "print @.cmd" before the "select @.amt..." statement
to see which db you are currently using when the error occurs.
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:8C338237-CD72-4553-BEBF-2FE48E6CF804@.microsoft.com...
>I got error message like this
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Invoice'.
>
> --
> Thanks
> Ganesh
>
> "VC" wrote:
>|||No Invoice exists, But the Exec is a kind of connection, once we exected
after that it closes the connection, That's why i could not execute next
statment using exec
then i changed the select statement into the same exec now it works
Thanks for your help
Thanks
Ganesh
"VC" wrote:

> Well, then the table Invoice must not exist in the particular db that you
> are in when the error occurs.
> I would add a statement "print @.cmd" before the "select @.amt..." statement
> to see which db you are currently using when the error occurs.
>
> "Ganesh" <gsganesh@.yahoo.com> wrote in message
> news:8C338237-CD72-4553-BEBF-2FE48E6CF804@.microsoft.com...
>
>