Executing SQL statements in another database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Executing SQL statements in another database

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-09 : 17:36:17

I have a stored procedure named usp_CreateSomeAdminObjects that resides in say a database named AdminStuff (ok, fictional but it gets the point across :) ). This proc generates a series of dynamic SQL statements (DROP TRIGGER, CREATE TRIGGER and EXECUTE sp_settriggerorder) against tables that reside in different databases. However I am unable to change the context of the database from AdminStuff to the database I am cycling. Any suggestions on how I can have execute this dynamic SQL in the context of a specific database (other than the one the proc resides in)?

I can not put the USE statement in a stored procedure. Attempts to use sp_executesql, EXECUTE and even run the script through sqlcmd using either (a) the -d option or (b) a USE <dbname>; statement as part of the -q parameter have all been unsuccessful.

Heck, even if I execute this code through SSMS it does not work as I need it to:

USE OtherDatabaseName;
EXEC DBA.dbo.usp_CreateSomeAdminObjects

I confirmed this by adding these statements to the proc:

/* Gets list of tables from the current database */
SELECT name, create_date FROM sys.objects WHERE type = 'U' ORDER BY name

SELECT DB_NAME();

These statements return a list of user tables that reside in AdminStuff and not OtherDatabaseName (the same goes for the DB_NAME() statement.

Any suggestions on how I can accomplish what I'm looking to do - have this proc execute dynamically generated DDL scripts for tables that reside in a different database. I'd rather not name this procedure sp_CreateSomeAdminObjects and compile it in the master database. If that is my only option I will but figured I'd check out other options first.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 17:39:05

What I would do is add a parameter to your stored procedure that would accept the database name. Then use the three part naming convention for the object, such as @dbName + .'sys.objects' via dynamic SQL.

Putting it in the master database does not help this problem.

If you want more specific help, you'd need to post the stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-10 : 08:56:43

Putting this type of procedure will totally fix the problem (that's where the current version resides prior to our changes). Having it in the master database allows you to set the database context first (via USE) and then anything referenced or statements executed occur in the context of the current database. As long as the procedure is prefixed with sp_ it can be executed from any other database and act upon that database.

As far as adding the database name in the DROP TRIGGER, CREATE TRIGGER and sp_settriggerorder queries, that's not valid syntax in these statements. These statements act upon the current database context.

Also, another caveat I am facing is that I am unable to execute a dynamic SQL string with a USE statement first because the CREATE TRIGGER statement requires that it's always the first statement in a batch.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-10 : 15:46:04

I think I found the culprit. As part of this change I converted all system objects that are being referenced by the code to the equivalent 2005 catalog views. In my case I changed a reference from SYSOBJECTS to sys.objects.

Take this example:

USE master
GO

IF OBJECT_ID('dbo.sp_GetObjects') IS NOT NULL
DROP PROCEDURE dbo.sp_GetObjects
GO

CREATE PROCEDURE dbo.sp_GetObjects
AS
SELECT 'sys.objects', * FROM sys.objects WHERE type = 'U' ORDER BY name
SELECT 'SYSOBJECTS', * FROM SYSOBJECTS WHERE xtype = 'U' ORDER BY name
GO

Now run it against a different database than master:
USE AdventureWorks
EXEC sp_GetObjects
GO

You'll see that sys.objects is bound to the database the procedure was compiled in while SYSOBJECTS is is bound to whatever the database context currently is. Is this expected behaviour?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 16:10:19

You can use sp_executesql in the context of another database.
Example:


exec tempdb.dbo.sp_executesql N'select db_name() as DB'


Results:
DB
---------------------------
tempdb

(1 row(s) affected)

CODO ERGO SUM

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-10 : 16:16:32

I'm not sure how that will really help. Unless I make the entire set of queries I have dynamic - which I'd rather avoid if possible. Basically, there is a difference between SYSOBJECTS and sys.objects (or sys.all_objects, sys.tables, etc). It appears that if added in a stored procedure, they are bound to the database that procedure is compiled in. Unlike SYSOBJECTS where querying it will always return objects in the current database context.

Can anyone confirm this?

Take my previous example (of a sp_ proc compiled in master):
USE AdventureWorks
EXEC sp_GetObjects
GO
USE AdventureWorks
EXEC AdventureWorks.dbo.sp_GetObjects
GO

The sys.objects query still returns data from the master database...

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-10 : 16:21:36

Straight from BOL:

USE is executed at both compile and execution time and takes effect immediately. Therefore, statements that appear in a batch after the USE statement are executed in the specified database.

This indicates to me that my assumptions should work... am I just being dense here? :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 16:24:41

You said you were unable to get sp_executesql to work, and I was showing you how to execute it in the context a a database you chose. See below for an even more dynamic example.

Any stored procedure you compile runs in the context of the database it is in, so I do no see how you expect to avoid dynamic SQL.


declare @proc  nvarchar(300)

set @proc = 'master.dbo.sp_executesql'
exec @proc N'select db_name() as DB'

set @proc = 'tempdb.dbo.sp_executesql'
exec @proc N'select db_name() as DB'


Results:
DB
-----------------------
master

(1 row(s) affected)

DB
-----------------------
tempdb

(1 row(s) affected)

CODO ERGO SUM

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-10 : 16:39:12

Michael,

Thanks for the info. I'll give it a shot embedding a method of executing dynamic SQL (sp_executesql) within another method of executing dynamic SQL (EXEC - I even bet I can use sp_executesql too).

My concern though is the behavior has changed in the catalog views. Even in SQL Server 2005, I can reference SYSOBJECTS and it will return data for whatever database context I set in the batch. I am unable to do that with sys.tables as it's bound to the database the procedure is compiled in - regardless of any code that changes the database context. That really breaks a few things IMO.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 16:46:02

I think you missed the point of what I showed you.

You do not have to embed the execution of sp_executesql inside a dynamic SQL statement. You can dynamically set the procedure name you execute, so you just set the 3 part procedure name in a local variable, and then execute it:

set @proc = 'MyDB.dbo.sp_executesql'
execute @proc 'Select ...'

CODO ERGO SUM

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-10 : 16:53:26

I got it, but I didn't quite understand it at first. I do now. I'll give that a shot. What would be really nice is if I could just create a global trigger to do what I'm looking to accomplish... I'm looking at the ALL_SERVER and DATABASE options now of the CREATE TRIGGER statement... this might save me some grief.

Basically, we want to maintain some auditing fields on all tables in a database (well several). To support this we generate triggers on every single table with the same update statement. Any suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources