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 statemen

Use @dbnam
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

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


[quoted text, click to view]
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
[quoted text, click to view]