A Simple way of Automating Scripts

  • Sadequl Hussain-911759

    SSC-Addicted

    Points: 465

    Comments posted to this topic are about the item A Simple way of Automating Scripts

  • pp.pragnesh

    Old Hand

    Points: 330

    Great example... Sadequal...

    Very useful.. Thanks

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    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]

  • Jamie Ingram-729524

    SSCommitted

    Points: 1565

    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

    Say Hey Kid

    Points: 671

    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

    SSCertifiable

    Points: 5042

    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

    SSChampion

    Points: 13157

    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

    SSCertifiable

    Points: 7676

    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]

  • Martin Bell-290511

    SSC Enthusiast

    Points: 161

    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

    SSCertifiable

    Points: 5042

    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.

  • michael.patton

    Newbie

    Points: 9

    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.

  • JStiney

    Say Hey Kid

    Points: 671

    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.

  • yuge

    SSC Rookie

    Points: 39

    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.

  • Martin Bell-290511

    SSC Enthusiast

    Points: 161

    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

  • Chad Crawford

    SSChampion

    Points: 11610

    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 73 total)

You must be logged in to reply to this topic. Login to reply