[TRANSACT]Cant 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 @.dbnamesysname
declare @.ret_codeint

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.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
--|||execute('use ' + @.dbname + ';select db_name()')
HTH

[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 @.dbnamesysname
declare @.ret_codeint
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.

[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 Süßmeyer.
--
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.

[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

[novice Q] sequence in restoring system db

hi,
is there sequence in restoring system db?
shall i start with master, msdb followed by temp or the sequence doesnt
matter?
TIA"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:1A6DE952-27A0-4C19-B18E-AAF1C69F8A59@.microsoft.com...
> hi,
> is there sequence in restoring system db?
> shall i start with master, msdb followed by temp or the sequence doesnt
> matter?
> TIA
You will need to restore the master db first. You should check the BOL on
restoring the master database.
Do a search for "Restoring the master Database from a Current Backup"
After that, the other databases order doesn't matter a whole lot.
You do not restore tempdb, it is recreated every time the SQL Server is
restarted.
Rick Sawtell
MCT, MCSD, MCDBA|||got it...thanks rick
"Rick Sawtell" wrote:
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:1A6DE952-27A0-4C19-B18E-AAF1C69F8A59@.microsoft.com...
> > hi,
> > is there sequence in restoring system db?
> > shall i start with master, msdb followed by temp or the sequence doesnt
> > matter?
> >
> > TIA
> You will need to restore the master db first. You should check the BOL on
> restoring the master database.
> Do a search for "Restoring the master Database from a Current Backup"
> After that, the other databases order doesn't matter a whole lot.
> You do not restore tempdb, it is recreated every time the SQL Server is
> restarted.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

[novice Q] sequence in restoring system db

hi,
is there sequence in restoring system db?
shall i start with master, msdb followed by temp or the sequence doesnt
matter?
TIA
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:1A6DE952-27A0-4C19-B18E-AAF1C69F8A59@.microsoft.com...
> hi,
> is there sequence in restoring system db?
> shall i start with master, msdb followed by temp or the sequence doesnt
> matter?
> TIA
You will need to restore the master db first. You should check the BOL on
restoring the master database.
Do a search for "Restoring the master Database from a Current Backup"
After that, the other databases order doesn't matter a whole lot.
You do not restore tempdb, it is recreated every time the SQL Server is
restarted.
Rick Sawtell
MCT, MCSD, MCDBA
|||got it...thanks rick
"Rick Sawtell" wrote:

> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:1A6DE952-27A0-4C19-B18E-AAF1C69F8A59@.microsoft.com...
> You will need to restore the master db first. You should check the BOL on
> restoring the master database.
> Do a search for "Restoring the master Database from a Current Backup"
> After that, the other databases order doesn't matter a whole lot.
> You do not restore tempdb, it is recreated every time the SQL Server is
> restarted.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

[novice Q] sequence in restoring system db

hi,
is there sequence in restoring system db?
shall i start with master, msdb followed by temp or the sequence doesnt
matter?
TIA"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:1A6DE952-27A0-4C19-B18E-AAF1C69F8A59@.microsoft.com...
> hi,
> is there sequence in restoring system db?
> shall i start with master, msdb followed by temp or the sequence doesnt
> matter?
> TIA
You will need to restore the master db first. You should check the BOL on
restoring the master database.
Do a search for "Restoring the master Database from a Current Backup"
After that, the other databases order doesn't matter a whole lot.
You do not restore tempdb, it is recreated every time the SQL Server is
restarted.
Rick Sawtell
MCT, MCSD, MCDBA|||got it...thanks rick
"Rick Sawtell" wrote:

> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:1A6DE952-27A0-4C19-B18E-AAF1C69F8A59@.microsoft.com...
> You will need to restore the master db first. You should check the BOL on
> restoring the master database.
> Do a search for "Restoring the master Database from a Current Backup"
> After that, the other databases order doesn't matter a whole lot.
> You do not restore tempdb, it is recreated every time the SQL Server is
> restarted.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s=20
returned NULL value?
Should i do an INSERT in the master..sysservers? or can=20
you tell me if exists something that correct this=20
situation?
Best RegardsMake a backup of master first
do sp_dropserver 'servername'
go
sp_addserver 'servername', local
go
then stop and start your server.
Then select @.@.servername
Jeff
MCDBA, MCSE+I|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s=20
returned NULL value?
Should i do an INSERT in the master..sysservers? or can=20
you tell me if exists something that correct this=20
situation?
Best Regards
Make a backup of master first
do sp_dropserver 'servername'
go
sp_addserver 'servername', local
go
then stop and start your server.
Then select @.@.servername
Jeff
MCDBA, MCSE+I
|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s returned NULL value?
Should i do an INSERT in the master..sysservers? or can you tell me if exists something that correct this situation?
Best RegardsMake a backup of master firs
do sp_dropserver 'servername
g
sp_addserver 'servername', loca
g
then stop and start your server
Then select @.@.servernam
Jef
MCDBA, MCSE+|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>