July 30, 2009 at 12:04 am
Comments posted to this topic are about the item A Simple way of Automating Scripts
July 30, 2009 at 12:06 am
Great example... Sadequal...
Very useful.. Thanks
July 30, 2009 at 3:54 am
An easy way of executing scripts.....
Create a batch file and place it in the folder containing the scripts...
@for /r %%s in (.) do (echo ..............%%s /b >> "C:\Build Deployment\output\Log.txt"
sqlcmd -d Dbname -Usa -Psql2005 -i "%%s" >> "C:\Build Deployment\output\Log.txt")
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 30, 2009 at 8:06 am
Nice work!!!
I have been using this tool, xSQL Script Executor. It's freeware - so the price is right.
It allows you to batch your scripts together in projects and target multiple servers and/or databases.
Here is the URL
http://www.xsqlsoftware.com/Product/Sql_Server_Script_Executor.aspx
Cheers
Jamie
July 30, 2009 at 9:13 am
Let me suggest another simple method to implementing production database changes.
Use a DTS or SSIS Package.
Several years ago we agreed with our development staff that all database changes would be implemented using DTS Packages. The developer creates the package with all the scripts put into steps so that they will run in the correct order. The developer runs the package in the development and test environments. When the changes are ready for production, the dba runs the package in production. This has worked great for us.
It is simple, secure, and the chance of error is very small.
July 30, 2009 at 9:41 am
Nice article.
The part of the process I don't like is having to name the scripts a certain way to get them to run in the correct order. Wouldn't changes to the order (needing to insert a script somewhere) end up being a pain? Of course, the article was clear that the script naming idea was just the simplest way to go.
I string together scripts using an MS Access database and VBA code. (You could store the data in SQL Server tables instead if you preferred.) The script names are stored in tables with sort orders under categories and sub categories that also have sort orders. The benefit is that I can name my scripts anything. The categories help with organizing the scripts and provide some additional meta-data. I have to deal with collecting 200+ scripts for my databases. That may explain why this approach appeals to me.
July 30, 2009 at 10:17 am
Sqlfrenzy (7/30/2009)
An easy way of executing scripts.....Create a batch file and place it in the folder containing the scripts...
@for /r %%s in (.) do (echo ..............%%s /b >> "C:\Build Deployment\output\Log.txt"
sqlcmd -d Dbname -Usa -Psql2005 -i "%%s" >> "C:\Build Deployment\output\Log.txt")
Problem with this is that you have to give either SA or Login ( mostly administrator) password in P parameter and that could be read by other people working on project.
It would be OK to run/call commands in batch when you are doing it on fly.
SQL DBA.
July 30, 2009 at 10:48 am
SanjayAttray (7/30/2009)
Sqlfrenzy (7/30/2009)
An easy way of executing scripts.....Create a batch file and place it in the folder containing the scripts...
@for /r %%s in (.) do (echo ..............%%s /b >> "C:\Build Deployment\output\Log.txt"
sqlcmd -d Dbname -Usa -Psql2005 -i "%%s" >> "C:\Build Deployment\output\Log.txt")
Problem with this is that you have to give either SA or Login ( mostly administrator) password in P parameter and that could be read by other people working on project.
It would be OK to run/call commands in batch when you are doing it on fly.
well.... u need a login to connect to sql server any how......and the person implementing the changes on production server are mostly administrators....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 30, 2009 at 11:18 am
Sqlfrenzy (7/30/2009)
SanjayAttray (7/30/2009)
Sqlfrenzy (7/30/2009)
An easy way of executing scripts.....Create a batch file and place it in the folder containing the scripts...
@for /r %%s in (.) do (echo ..............%%s /b >> "C:\Build Deployment\output\Log.txt"
sqlcmd -d Dbname -Usa -Psql2005 -i "%%s" >> "C:\Build Deployment\output\Log.txt")
Problem with this is that you have to give either SA or Login ( mostly administrator) password in P parameter and that could be read by other people working on project.
It would be OK to run/call commands in batch when you are doing it on fly.
well.... u need a login to connect to sql server any how......and the person implementing the changes on production server are mostly administrators....
There is no difference between the posted article and this command line script in terms of authentication as they both call sqlcmd. There's also no reason why you can't call the command line script or batch file from a xp_cmdshell session and if you want sql authentication pass as parameters the username and password along with server and database to make it more flexible. That's potentially one line of T-SQL compared to around 86.
Doing it at the command line also has the advantage that you are not having to create a command shell for each file, so it will save a significant amount of time. Concatenating the files will mean you only call sqlcmd once which will improve the performance, but you need the /b option when doing the concatenation; although if you do this and someone has messed with a SET option it may have unwanted knock on effects.
I posted several examples at http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/02/How-to-execute-multiple-sql-scripts.aspx
Martin
July 30, 2009 at 11:26 am
For completeness, I'll add another method: If you have ERwin, you can link to files/scripts within ERwin. ERwin will include those files when you "forward engineer"/create the script for the database. You can control script order because ERwin adds the scripts based on table level. Thus, scripts attached to a parent table will run before scripts attached to it's child.
As much as I appreciate having ERwin as a tool and use it for all my schema generating needs, I dislike this method for script concatenation and stopped using it years ago. Still, my co-worker likes it and so others may find this option desirable too. It is worth mentioning. Perhaps other data modeling tools have similar options.
July 30, 2009 at 11:28 am
Interesting alternative to the Fogg approach but if you are receiving scripts from developers then the individual using this method would have to interrogate those scripts in order to determine the sequence as a set and as a whole. Unless the developers used a unique group name and serialized their set this could be cumbersome activity and prone to the same reasons the script was developed in the first place.
July 30, 2009 at 11:51 am
Michael,
If you are referring to my package alternative, I don’t get scripts from developers.
I get the package after it has been tested by successfully running it in the Test/QA environment. The dba doesn’t do anything with scripts, except maybe help debug them with the developer, if there is a problem.
July 30, 2009 at 11:52 am
Sadequal,
I am a DBA that promots scripts for developer all the time also.
I look at the script, and it is very well done but, from my experience, I will have errors with the script one a while, if the script can catch the error and stop. it should be very good new feature.
July 30, 2009 at 12:30 pm
If you use the -b option for SQLCMD you can stop after a given file has failed providing you are executing the files individually in a batch file. I give an example in the link I posted.
Martin
July 30, 2009 at 12:38 pm
JJ B (7/30/2009)
Nice article.The part of the process I don't like is having to name the scripts a certain way to get them to run in the correct order. Wouldn't changes to the order (needing to insert a script somewhere) end up being a pain? Of course, the article was clear that the script naming idea was just the simplest way to go.
We do something similar to what Sadequl has done, and we do periodically have to insert a file in the middle of the run order. An easy way to accomplish that is to add characters to the end of the filename - ScriptB_1.sql. This would place the new file between ScriptB and ScriptC in the run order.
Thanks,
Chad
Viewing 15 posts - 1 through 15 (of 72 total)
You must be logged in to reply to this topic. Login to reply