A Simple way of Automating Scripts

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sadequl Hussain » A Simple way of Automating Scripts

72 posts, Page 2 of 8

««1 2 3 4 5 »»»

A Simple way of Automating Scripts

Rate Topic

Display Mode

Topic Options

Author

Message

michael.patton

Posted Thursday, July 30, 2009 11:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 29, 2010 12:13 PM
Points: 1, Visits: 6

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.

Post #762622

JStiney

Posted Thursday, July 30, 2009 11:51 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:05 AM
Points: 75, Visits: 446

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.

Post #762641

yuge

Posted Thursday, July 30, 2009 11:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 08, 2010 12:10 PM
Points: 33, Visits: 7

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.

Post #762643

Martin Bell-290511

Posted Thursday, July 30, 2009 12:30 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, Visits: 135

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

Post #762675

Chad Crawford

Posted Thursday, July 30, 2009 12:38 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,550, Visits: 17,369

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

Post #762680

the sqlist

Posted Thursday, July 30, 2009 3:10 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:18 AM
Points: 124, Visits: 471

I am doing this for a long time now and personally I don't see the need of that complicated T-SQL code. The bacth file is the best and by far the easiest and less complicated way to execute scripts. You can also use the windows authentication with OSQL and SQLCMD using the -E switch if you want to enforce security.

In regards with the order my approach is to prefix the file names with sequence of not consecutive numbers (with gaps) like:

0010_script_1.sql
0020_xxx.sql
0030_aaa_harry_potter.sql

if you want to insert a script somewhere just add the correct prefix, if you want to change the order just change the prefix. This also gives you a visual advantage and of the obvious significance of the numbers and that the rest of the name doesn't really matter.

Another way of ensuring the execution order is maintaining an file having the script names in the wanted order. This file would be read by the batch file when executed to get the scripts in the right order. This method eliminates the need for naming or prefixing but I prefer still the prefixing method, which I find much simpler than maintaining an "order" file. I used both of them by the way.

Another thing I do I move all the executed scripts into a next deeper level folder and the output of the results an a second folder usually called _Archive and _Results. This is a way to make sure I don't execute same scripts twice. The output file is only one regardless of the number of scripts by using >> output_YYYYMMDDTHHMMSS.log syntax.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #762797

Sadequl Hussain-911759

Posted Thursday, July 30, 2009 5:33 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 9:50 PM
Points: 75, Visits: 126

Great discussion... !!! Folks, I really appreciate all the feedback and the ideas... this is how you learn when you write and share Smile

One thing I am not sure - and correct me if I am wrong - is if the batch file shown here creates one seprate output text file for each of the scripts executed. Ideally, each of the source script output should be in a seperate text output file.

As for how to insert multiple scripts in between - I believe the approach in the last post is worthwhile - for example starting file names with say 10 then 20, 30 etc....that way, files beiginning with 11 or 12 or 25 can be inserted in between. Even if we do have files starting with 1,2,3 etc... I am sure another script or batch file can rename the whole group using some sort of loop to accomodate the new file.

Personally, I would not want to run say, 25 script files concatenated in one huge script file. To me, if there is an error in one batch (because if you incorporate everything in one script, the individual "scripts" would have to be batches within the large file), the whole process may still go ahead but with inconsistencies / errors in it. I would rather use the /b option.

Once again, many thanks.

Post #762844

Martin Bell-290511

Posted Friday, July 31, 2009 1:23 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, Visits: 135

Sadequl Hussain (7/30/2009)

Great discussion... !!! Folks, I really appreciate all the feedback and the ideas... this is how you learn when you write and share Smile

That's easy! If your batch file is called runit.bat and contains

for %%f in (%2\*.sql) do SQLCMD -E -S "(local)" -i %%f -o %2\output\%%~nf.out -b -d %1 || exit /b

You would run the batch file as

C:\temp\sqlscripts\runit.bat adventureworks C:\temp\sqlscripts

All files in C:\temp\sqlscripts with the .sql extention will be executed with output files in the C:\temp\sqlscripts\output sub-directory using the body of the file name and a .out extension e.g C:\temp\sqlscripts\1_Firstfile.sql will have C:\temp\sqlscripts\output\1_Firstfile.out. If you have a script that fails e.g 2_Secondfile.sql containing the command RAISERROR ( 'Error', 16 , 1 ), then all that will be run is 1_Firstfile.sql and 2_Secondfile.sql and C:\temp\sqlscripts\output\2_Secondfile.out would contain:

Msg 50000, Level 16, State 1, Server MyPC, Line 1
Error

So the last file created is where the error occured.

Martin

Post #762939

Adam Gojdas

Posted Friday, July 31, 2009 8:25 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 22, 2013 3:37 PM
Points: 79, Visits: 1,317

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....

You can use the -E option for sqlcmd instead of having user and pass in there.

From BOL:
-E
Uses a trusted connection instead of using a user name and password to log in to SQL Server. sqlcmd uses the trusted connection option by default.

The -E option ignores possible user name and password environment variable settings such as SQLCMDPASSWORD. If the -E option is used together with the -U option or the -P option, an error message is generated.

Post #763132

the sqlist

Posted Friday, July 31, 2009 10:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:18 AM
Points: 124, Visits: 471

I agree with the ideea that the sql script files should be kept in separate files but for the output file is better to have only one as is far easier to open one single output file instead of 50 and check for the errors. You will see the results and if one(or more) of the script(s) generated errors you deal with only that (those ) file(s), debug it(them) and run it(them) again if needed.

The only problem you need to take care of with this is that you will need to add the script file names in the output file because output from OSQL or SQLCMD doesn't do that.

Here is a bat code that executes all the sql scripts in a folder creates an output with the format like Output_YYYY-MM-DD_PMHHMM.LOG

and them moves the scripts down one folder in Archive and the output file in Results:

REM Parameters:
REM %1 - Server:, %2 - Database, %3 - Scripts folder
REM @Echo off

REM get a timestamp variable
REM set the cleaned time into tm var
for /F "tokens=1-6 delims=: " %%A in ('time/T') do set tm=%%C%%A%%B

REM set the cleaned date into dt var and set tm with date time stamp
for /F "tokens=1-11 delims=/ " %%A in ('date/T') do set dt=%%A%%B%%C%%D
set tm=%dt:~7,4%-%dt:~3,2%-%dt:~5,2%_%tm%

REM create the outoput file
echo Server: %1 Database: %2 Folder: %3 > %3\Results\Output_%tm%.LOG

REM run the scripts
for /f "tokens=1" %%i in ('dir /b %3\*.sql') do (
REM the echo lines only add delimiting lines between the outputs
echo . >> %3\Results\Output_%tm%.LOG
echo . >> %3\Results\Output_%tm%.LOG
echo . >> %3\Results\Output_%tm%.LOG
SQLCMD -E -S%1 -d%2 -Q"SET NOCOUNT ON; SELECT '##### Script: %%i'" >>%3\Results\Output_%tm%.LOG
SQLCMD -E -S%1 -d%2 -i%3\%%i >>%3\Results\Output_%tm%.LOG
)

REM Move *.sql files to Archive folder
move %3\*.sql %3\Archive\

If the file is called exec_sql.bat and you have it in C;\Temp folder and the scripts are in the same folder you execute it like:

C:\Temp>exec_sql.bat server dbname C:\Temp

for local server you do

C:\Temp>exec_sql.bat . dbname C:\Temp

use . (dot) for local server

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #763263

« Prev Topic | Next Topic »

72 posts, Page 2 of 8

««1 2 3 4 5 »»»

Permissions