Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


multiple database creation script


multiple database creation script

Author
Message
rp-1074589
rp-1074589
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 11
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
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-2008   RBarryYoung:   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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search