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


A Simple way of Automating Scripts


A Simple way of Automating Scripts

Author
Message
Sadequl Hussain-911759
Sadequl Hussain-911759
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 131
Comments posted to this topic are about the item A Simple way of Automating Scripts
pp.pragnesh
pp.pragnesh
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 53
Great example... Sadequal...

Very useful.. Thanks
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 1654
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
Jamie Ingram-729524
Jamie Ingram-729524
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 421
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.

JStiney
JStiney
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 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.



JJ B
JJ B
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 2858
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.
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4175 Visits: 1619
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.
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 1654
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
Martin Bell-290511
Martin Bell-290511
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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
JJ B
JJ B
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 2858
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.
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