Re: DB name changes daily

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 05/14/04 

Date: Fri, 14 May 2004 12:11:59 +0530

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

Relevant Pages

  • cdt glossary 0.1.4
    ... This glossary seeks to limit lengthy misunderstandings ... basic database research and mathematics. ... When context matters, it is provided. ... It is /not/ the same as a reference. ...
    (comp.databases.theory)
  • Re: Database design, Keys and some other things
    ... except maybe that a database contains dead objects in the sense then as soon as they are in the database they stop behaving - food for another thread). ... some of their facts to establish that reason. ... to a refutation of the idea that there's any essential difference between the industry standard external identifier and the database-specific surrogate key: it's a matter of context merely, and not anything intrinsic to that data, or how it is managed. ... What is essential to this question is what their nature is. ...
    (comp.databases.theory)
  • Re: Problems binding to LDAP
    ... context in the LDAP naming services. ... # Sample access control policy: ... # ldbm and/or bdb database definitions ... // of this initial directory context. ...
    (comp.lang.java.programmer)
  • Re: SQL 2005 ALTER ANY LOGIN trouble
    ... There are two types of principals in SQL Server - logins (server wide ... connect to a specific database, your login will be mapped to a user. ... you only get the database context - no server ...
    (microsoft.public.sqlserver.security)
  • Re: Basic data definitions
    ... >> I need some basic database definitions. ... >> not want any ridicule. ... Information can be defined as, knowledge that, within a certain context, has ... since data implies meaning or informational ...
    (microsoft.public.sqlserver.programming)