SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
pjlewis
pjlewis
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 108
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?
Vladan
Vladan
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9078 Visits: 763
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.



pjlewis
pjlewis
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 108
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.
pjlewis
pjlewis
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 108
I'm sorry, but I misread your ahswer. I will try to research a way to lengthen the "life" of the Use command results.
pjlewis
pjlewis
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 108
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search