Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

A Simple way of Automating Scripts Expand / Collapse
Author
Message
Posted Thursday, July 30, 2009 12:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 06, 2013 3:15 PM
Points: 75, Visits: 131
Comments posted to this topic are about the item A Simple way of Automating Scripts
Post #762163
Posted Thursday, July 30, 2009 12:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 15, 2011 11:15 PM
Points: 224, Visits: 53
Great example... Sadequal...

Very useful.. Thanks
Post #762164
Posted Thursday, July 30, 2009 3:54 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
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,
Sqlfrenzy

Post #762245
Posted Thursday, July 30, 2009 8:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 03, 2013 5:05 AM
Points: 210, Visits: 304
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


We are the pilgrims, master.
We shall go always, a little further.
Post #762426
Posted Thursday, July 30, 2009 9:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:05 AM
Points: 75, Visits: 446
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.




Post #762486
Posted Thursday, July 30, 2009 9:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:19 PM
Points: 265, Visits: 2,538
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.
Post #762527
Posted Thursday, July 30, 2009 10:17 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:29 PM
Points: 3,924, Visits: 1,586
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.
Post #762563
Posted Thursday, July 30, 2009 10:48 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
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,
Sqlfrenzy

Post #762590
Posted Thursday, July 30, 2009 11:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, Visits: 135
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
Post #762613
Posted Thursday, July 30, 2009 11:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:19 PM
Points: 265, Visits: 2,538
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.
Post #762619
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse