DB name changes daily

Hi,
I am trying to write SQL automation that runs SQL scripts against the daily drop of the database. Each day, a new DB is created using the format (Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name might be TEST12345 and tomorrow wo
uld be Test12346. I have a script that creates the dbname such as Test12345 and when I run it, I get the expected result, but when I try this statement
Use @.dbname
I get an error.
Is there a way to introduce a variable with a Use statement so that I can automatically open the latest db?
Thanks
Hi,
'USE' statement will change the DB context for the current connection and
then go back to where it was before (Default datbase context).
Doing this in an exec will change the context until the exec is completed
and then go back to default database context.
This is small example to show how to do it. This example uses the Northwind
database.
This database has a stored procedure 'emp_list' . From the master database
and execute the following:-
set quoted_identifier off
declare @.dbname varchar(15), @.procname varchar(25)
declare @.startdate varchar(10) , @.enddate varchar(10)
set @.dbname = 'northwind'
set @.procname = '[emp_list]'
exec ('USE ' + @.dbname + ' execute ' + @.procname )
the last statement translates to:
exec ( USE northwind execute [emp_list])
But after the execution again the context will go to the default database.
Note:
The best option rather than using the above is:- (SP_DEFAULTDB)
1. Have a common login
2. Every time after creating the new database use the sp_defaultdb procedure
to change the default db of the login
3. After this when ever that user logins the database context will be the
new database and there is nolt required to give USE XXXXX
Thanks
Hari
MCDBA
"Tomas" <anonymous@.discussions.microsoft.com> wrote in message
news:F9E1B6D0-5378-45AD-8A2C-6DF194B86B0A@.microsoft.com...
> Hi,
> I am trying to write SQL automation that runs SQL scripts against the
daily drop of the database. Each day, a new DB is created using the format
(Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name
might be TEST12345 and tomorrow would be Test12346. I have a script that
creates the dbname such as Test12345 and when I run it, I get the expected
result, but when I try this statement
> Use @.dbname
> I get an error.
> Is there a way to introduce a variable with a Use statement so that I can
automatically open the latest db?
> Thanks