Changing DB with a script using a variable.

I am trying to write a TSQL Script for maintenance that willl read the
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCTYou can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT|||Hi,
the problem with your command is, that EXEC will open a connection, change
the database on close the connection again. EXEC will use another scope than
the actual script is running in. As Manikanth said, you will have to include
the Script within your EXEC call to execute it on the same execution
context.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT
>