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 + ']';
However it seems to ignore the change and puts the new view into the Mgmt Database.
How do I get around this?