I need to select database name from sysdatabases & make dynamic 'u

I am trying to make a dynamic 'use' statement so that some code can loop
through the databases listed in sysdatabases, pull the name and place it into
a variable after the 'use ' keyword. My test code thus far looks like this:
----
begin
declare @.db_name varchar(200)
declare @.code varchar(200)
set @.db_name = 'nic_int_ldr'
select @.code = 'use ' + rtrim(ltrim(@.db_name))
print @.code
exec (@.code)
--use nic_int_ldr
select max(id_bat) from ldr_batch
en
----
At this point I'm just trying to get it to work with a hard coded database
name and I can't even get that to go.
Is it not possible to make a 'use ' statement work this way?
Thanks,
John MiceliUse is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
inside the same EXEC.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
>I am trying to make a dynamic 'use' statement so that some code can loop
> through the databases listed in sysdatabases, pull the name and place it into
> a variable after the 'use ' keyword. My test code thus far looks like this:
> ----
> begin
> declare @.db_name varchar(200)
> declare @.code varchar(200)
> set @.db_name = 'nic_int_ldr'
> select @.code = 'use ' + rtrim(ltrim(@.db_name))
> print @.code
> exec (@.code)
> --use nic_int_ldr
> select max(id_bat) from ldr_batch
> end
> ----
> At this point I'm just trying to get it to work with a hard coded database
> name and I can't even get that to go.
> Is it not possible to make a 'use ' statement work this way?
> Thanks,
> John Miceli|||Thank you Tibor for your response!
If I have to put all of what follows into the same exec, I think I'm going
to have problems because there are multiple, very large queries that have to
run against the same database. Then when the next database cycles through,
the same statements have to be run against each one (10 total).
Do you have any other ideas of how to make that persistent? Thanks again!
"Tibor Karaszi" wrote:
> Use is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
> inside the same EXEC.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
> news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
> >I am trying to make a dynamic 'use' statement so that some code can loop
> > through the databases listed in sysdatabases, pull the name and place it into
> > a variable after the 'use ' keyword. My test code thus far looks like this:
> > ----
> > begin
> > declare @.db_name varchar(200)
> > declare @.code varchar(200)
> >
> > set @.db_name = 'nic_int_ldr'
> > select @.code = 'use ' + rtrim(ltrim(@.db_name))
> > print @.code
> > exec (@.code)
> >
> > --use nic_int_ldr
> > select max(id_bat) from ldr_batch
> > end
> > ----
> > At this point I'm just trying to get it to work with a hard coded database
> > name and I can't even get that to go.
> >
> > Is it not possible to make a 'use ' statement work this way?
> >
> > Thanks,
> > John Miceli
>
>|||> Do you have any other ideas of how to make that persistent?
Not that I can think of, I'm afraid... Sorry :-(
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<John Miceli>; "MCDBA" <jdmiceli@.hotmail.com> wrote in message
news:BA5C17DB-F769-47DA-AF09-7AA414561BBE@.microsoft.com...
> Thank you Tibor for your response!
> If I have to put all of what follows into the same exec, I think I'm going
> to have problems because there are multiple, very large queries that have to
> run against the same database. Then when the next database cycles through,
> the same statements have to be run against each one (10 total).
> Do you have any other ideas of how to make that persistent? Thanks again!
>
> "Tibor Karaszi" wrote:
> > Use is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
> > inside the same EXEC.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
> > news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
> > >I am trying to make a dynamic 'use' statement so that some code can loop
> > > through the databases listed in sysdatabases, pull the name and place it into
> > > a variable after the 'use ' keyword. My test code thus far looks like this:
> > > ----
> > > begin
> > > declare @.db_name varchar(200)
> > > declare @.code varchar(200)
> > >
> > > set @.db_name = 'nic_int_ldr'
> > > select @.code = 'use ' + rtrim(ltrim(@.db_name))
> > > print @.code
> > > exec (@.code)
> > >
> > > --use nic_int_ldr
> > > select max(id_bat) from ldr_batch
> > > end
> > > ----
> > > At this point I'm just trying to get it to work with a hard coded database
> > > name and I can't even get that to go.
> > >
> > > Is it not possible to make a 'use ' statement work this way?
> > >
> > > Thanks,
> > > John Miceli
> >
> >
> >