The aim is to dynamically generate new databases for customers and initialize them for the first use.

First, I have my development database named 'trunk'. I use the visual studio 2008 "Publish to Provider" functionnality (or SSMS script generation tool) for generating a schema creation script. I have now a quite huge script with T-SQL statements, creating tables, stored proc, etcThis is perfect, I will use it in a simple programmatic way...

Next I have a .NET web application which is reponsible for the customer registration.When the user is correctly registered, I have to create a new Database and fill the newly created database by using my creation script.Two options :1) By using ADO, I can create a new database and execute a script file2) By using ADO, I can call a 'management database' stored procedure with parameters which will create the database and fill it

These two options work correctly except the following problems :

1)- VS doesn't manage scripts in a Web application natively - I have to specify the script file location using Physical path in the code.- I have to deal with the GO statement- script file has nothing to do with the .NET application, scripts should be stored on the SQL server side.- SQL server projects or Database Projects cannot be published on the Web application server.

2)- Stored procedure cannot change the database context by using USE 'database'

So with these constraints, I cannot include or copy/paste or use my sql creation script....It would be easier to manage the script on the SQL side but if it's not possible to execute the script on a particular database I cannot solve my problem.Moreover, I would like to save my scripts on SVN/SourceSafe

How to deal with this problem ?Does anyone have an experience on multiple dynamic database creation ?

rp-1074589 (1/25/2010)...2)- Stored procedure cannot change the database context by using USE 'database'...

I have faced this problem many times and have developed a very reliable and consistent way deal with it. It all has to do with the fact that many SQL DDL commands have requirements about batch begins and ends, and that you do not have GO available to you in stored procedures ("GO" is not a T-SQL command, it's actually a Management Studio command).

the general solution is to realize that Dynamic SQL executions each constitute their own separate batch. Here is a quick & easy demonstration of that:

Now I'll anticipate some of the problems that you'll run into down the road:

problem: Many DDL statements need to be in their own batch, and I still don't have "GO".solution: 3) First use Dynamic SQL to execute the USE, then "nest" a second level of dynamic SQL batches within that one to execute separate DDL commands.

problem: When something goes wrong, it's hard to figure out what was executing.solution: 4) When using dynamic SQL, always print out the string before executing it.

problem: The errors or the error messages seem to be disappearing.solution: 5) Always use Try/Catch in the dynamic SQL and display the error from the Catch statement.

problem: How do the apostrophes work when your doubly-nested?solution: 6) You double the apostrophes every extra level in, when using dynamic SQL (so try to keep it to two levels).

problem: I am not allowed to use Dynamic SQL because of SOX/Corp Guidelines/Other Auditors.solution: 7) Re-Educate them. Dynamic SQL is as safe as any other form of SQL as long as you do not start executing user input strings. Sometime's it is the best, or even the *only* solution (like here). They need to understand that.

problem: This all seems like a lot and/or confusing. Do you have an example?solution: Sure! Here's an example command procedure that can execute any single command in another database that follows all of these rules:

CREATE proc spDo_NoErr(@Command nvarchar(max), @DB nvarchar(80) = '') as /* Procedure to execute a dynamic string, suppressing any errors.Errors will be printed, along with the command, but will not be raised back to the caller.