469,366 Members | 2,286 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

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

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.

Jul 23 '05 #1
2 1750
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
--

Jul 23 '05 #2
execute('use ' + @dbname + ';select db_name()')
HTH

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Igor2004 | last post: by
reply views Thread by Igor Nikiforov | last post: by
29 posts views Thread by A.P. Hofstede | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.