October 1, 2008 at 10:44 pm
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?
October 2, 2008 at 5:07 am
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.
October 2, 2008 at 9:02 am
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.
October 2, 2008 at 9:44 am
I'm sorry, but I misread your ahswer. I will try to research a way to lengthen the "life" of the Use command results.
October 2, 2008 at 9:58 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply