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
michael.patton
michael.patton
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
JStiney
JStiney
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

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



yuge
yuge
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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.
Martin Bell-290511
Martin Bell-290511
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18647
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
the sqlist
the sqlist
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 724
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
Sadequl Hussain-911759
Sadequl Hussain-911759
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 131
Great discussion... !!! Folks, I really appreciate all the feedback and the ideas... this is how you learn when you write and share :-)

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.
Martin Bell-290511
Martin Bell-290511
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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 :-)

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.



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
Adam Gojdas
Adam Gojdas
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 1429
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.
the sqlist
the sqlist
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 724
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
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