multiple database creation script

  • Hello,

    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 :

    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 ?

    Thank you

  • 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:

    Select * from information_schema.tables

    exec('

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

    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.

    21-may-2008RBarryYoung:Created.

    */

    Declare @SQL nvarchar(max)

    --Setup the database string:

    IF @DB != ''

    BEGIN

    Select @DB = 'USE ' + @DB + '; --switch to the DB before using DDL statements

    '

    END

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

    IF @@ERROR<>0

    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.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply