[TRANSACT]Can't switch from master to another database

[pierig.gueguen]

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..sysdata bases
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.

[David Portas]

USE is scoped only within the EXEC statement so you'll have to combine
the two:

EXEC ('USE ' + @dbname+' SELECT DB_NAME()')

If this is for non-production code you might consider using the
following undocumented proc instead of the cursor:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This still implements a cursor behind the scenes but keeps some of the
complexity out of your own code.

--
David Portas
SQL Server MVP
--

[strider5]

execute('use ' + @dbname + ';select db_name()')
HTH