Creating Database Objects from a Trigger-invoked stored procedure....

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Creating Database Objects from a...

Creating Database Objects from a Trigger-invoked stored procedure....

Rate Topic

Display Mode

Topic Options

Author

Message

pjlewis

Posted Wednesday, October 01, 2008 10:44 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 9:29 PM
Points: 14, Visits: 94

I have a client who wants a database created for each job they undertake.

It works like this: In the Mgmt Level database, there is a database description table that has an INSERT-AFTER trigger attached.

This trigger, when invoked, calls a stored procedure that adds a four-digit job number to the standard database name. Then it creates a database and all its properties. Next, it creates all tables, indexes and foreign keys.

When Finished, the database and all the above objects exist.

The problem I am having is setting up views and stored procedures. SQL Server will not let me use the database name to fully qualify the CREATE VIEW statement (e.g., CREATE VIEW [db1234].[dbo].[vwAttachments]).

I tried to use the following to change to the new database:

SET @sql = 'USE [' + @strDBName + ']';
EXEc (@sql);

However it seems to ignore the change and puts the new view into the Mgmt Database.

How do I get around this?

Post #579477

Vladan

Posted Thursday, October 02, 2008 5:07 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604

If you put USE into dynamic SQL, you switch the database just for the duration of EXEC of dynamic SQL, because it runs in a different scope. Next execute (with CREATE VIEW) will run again in the original context.
Originally I wanted to suggest to put USE and CREATE VIEW in the same statement, but that won't work either (error: 'CREATE VIEW' must be the first statement in a query batch). I have no idea whether there is a way to solve it using dynamic SQL or not... I try to avoid dynamic SQL if possible, so I don't have that much experience with it.

Post #579560

pjlewis

Posted Thursday, October 02, 2008 9:02 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 9:29 PM
Points: 14, Visits: 94

Thank you for your reponse. Since I am new at this, do you know where I can find an example of the USE statement in a dynamic SQL statement?

I used this one, but it did not seem to work:

SET @sql = 'USE [' + @strDBName + ']';
EXEC (@SQL)

Then I try to create the view...
SET @sql = 'CREATE VIEW .....
EXEC (SQL)

After the procedure runs, the view is created but in the original database, not the new one specified.

Thank you very much.

Post #579714

pjlewis

Posted Thursday, October 02, 2008 9:44 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 9:29 PM
Points: 14, Visits: 94

I'm sorry, but I misread your ahswer. I will try to research a way to lengthen the "life" of the Use command results.

Post #579745

pjlewis

Posted Thursday, October 02, 2008 9:58 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 9:29 PM
Points: 14, Visits: 94

I may have found a way, from another blog....

If I put my CREATE VIEW and CREATE PROCEDURE statements into a .sql file, then I may be able to call them from my procedure with:

exec master..xp_cmdshell 'osql -E -ix:\path\filename.sql'

I will let you know if it works.

Thanks.

Post #579762

« Prev Topic | Next Topic »

Permissions