Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sadequl Hussain » A Simple way of Automating Scripts
Add to briefcase
72 posts, Page 1 of 8
12345»»»

A Simple way of Automating Scripts Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Sadequl Hussain-911759
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: Tuesday, August 21, 2012 9:50 PM
Points: 75, Visits: 126
Comments posted to this topic are about the item A Simple way of Automating Scripts
Post #762163
pp.pragnesh
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
Sqlfrenzy
Posted Thursday, July 30, 2009 3:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:47 AM
Points: 1,431, Visits: 1,540
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
Jamie Ingram-729524
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: Friday, May 17, 2013 3:08 AM
Points: 205, Visits: 261
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
JStiney
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
JJ B
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: 2 days ago @ 3:08 PM
Points: 255, Visits: 2,407
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
SanjayAttray
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, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
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
Sqlfrenzy
Posted Thursday, July 30, 2009 10:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:47 AM
Points: 1,431, Visits: 1,540
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
Martin Bell-290511
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
JJ B
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: 2 days ago @ 3:08 PM
Points: 255, Visits: 2,407
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
72 posts, Page 1 of 8
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.