Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating Database Objects from a Trigger-invoked stored procedure.... Expand / Collapse
Author
Message
Posted Wednesday, October 01, 2008 10:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, October 02, 2008 5:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Thursday, October 02, 2008 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, October 02, 2008 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, October 02, 2008 9:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse