Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

multiple database creation script Expand / Collapse
Posted Monday, January 25, 2010 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 19, 2010 6:10 AM
Points: 1, Visits: 11

I have to deal with a new SQL problem I can't solve alone.

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, etc
This 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 file
2) 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 :

- 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.

- 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 ?

Thank you
Post #853097
Posted Tuesday, January 26, 2010 6:20 PM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 25, 2016 7:17 AM
Points: 9,298, Visits: 9,517
rp-1074589 (1/25/2010)
- 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:
Select * from information_schema.tables
Select * from information_schema.tables
USE [tempdb]
Select * from information_schema.tables
USE [master]
Select * from information_schema.tables
Select * from information_schema.tables

Now try it in a stored procedure too, it still works, just as well. Here is an outline of my technique, with problems and solutions:

solution: 1) Use Stored Procedures.

problem: "USE" is ineffective in a Stored Proc.
solution: 2) Use Dynamic SQL in your stored procs.

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) = '')
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.

21-may-2008 RBarryYoung: Created.
Declare @SQL nvarchar(max)

--Setup the database string:
IF @DB != ''
Select @DB = 'USE ' + @DB + '; --switch to the DB before using DDL statements

--double quotes in the original @sql string:
Select @Command = Replace(@Command, '''', '''''')

--build the final string to execute:
Select @SQL = @DB + ' BEGIN TRY
EXEC(N'''+ @Command +''')
End Try
Begin Catch
Print ''Ignoring %ERROR: '' + Error_Message()
Print ''While executing: "' + @Command + '"''
End Catch
' --wrap in another EXEC because DDL statements must be first in a batch

PRINT 'Executing: ' + @SQL
--Do it.
EXEC dbo.sp_executesql @SQL

Print @SQL --Show the command, if it failed.

I have much larger examples too. Some that create whole databases with this technique. Let me know and I can provide you with a copy of one.

-- RBarryYoung, (302)375-0451 blog:, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #854060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse