Calling different DB in a stored proc

[jazerr]

MSSQL2000
Windows2000

We store user activity logs in a global database. I have a SP that reads those logs and outputs basically 'who did what, and how many times?'

I need to be able to run the SP from a specific customer database, and have it use the global database.

I tried using 'USE dbname', but SQL doesn't allow me to use 'USE' in a SP.

QUESTION: What is the best way to 'use' a different database in a SP?

 

[AshleyRickards]

I think what you can do is use the full name: DBNAME.OWNER.OBJECTNAME Ashley L Rickards
SQL DBA

 

[lpandya]

HI,

I think that the solution you mentioned would work only if there is one db.
I have a similar problem where my SP resides in the enterprise db and there are multiple containers.
I need to execute the SP from the enterprise db against all the containers. The information about each container is there in the enterprise db.
Also, the SP writes back to the enterprise tables.
I tried to use "USE dbname" using dynamic sql. But it does not work.
Is there a solution to this?

 

[tlbroadbent]

Ashley's answer is correct. You can reference an object in another database with the fully qualified name. With SQL 7 and 2000, you can add the server as an additional qualifier and reference tables or views on other servers.

If you execute dynamic SQL with a USE statement, the database context changes only for the duration of the Execute statement.

Example:
Exec ('Use HR_DB Select * From employees Where EmpID=42')
Once this statement completes the database context is restored as it was prior to executing the statement.

Of course, the query above is the same as selecting from the fully qualified table name.

Exec ('Select * From HR_DB..employees Where EmpID=42')

The USE statement has no effect in a stored procedure.

You can execute system stored procedures with any database name and the SP will execute in the context of that database. A system stored procedure is one that resides in the master database and starts with 'sp_'.

Example: Execute sp_heluser in the context of the Accounting database. The SP is found in the master database but this execution will return user information from the Accounting database.

exec Accounting..sp_helpuser Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.