[TRANSACT] Can't switch from database to another

Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.sql