July 30, 2009 at 3:10 pm
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
July 30, 2009 at 5:33 pm
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.
July 31, 2009 at 1:23 am
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
July 31, 2009 at 8:25 am
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.
July 31, 2009 at 10:51 am
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
August 2, 2009 at 5:54 am
the sqlist (7/31/2009)
Mmm... the delete option doesn't seem to work!
August 2, 2009 at 5:58 am
the sqlist (7/31/2009)
...for /f "tokens=1" %%i in ('dir /b %3\*.sql') do (
...
I like this option :-), although you don't actually need to tokenise the output of the bare format of dir (dir /b) as this will only be a list of filenames so
...
for /f %%i in ('dir /b %3\*.sql') do (
...
would do. But using dir in this way does allow you to use dir's sort options to govern the order which the scripts are executed. e.g
...
for /f %%i in ('dir /b /o:N %3\*.sql') do (
...
will be in the same alphabetical order
...
for /f %%i in ('dir /b /o:- N %3\*.sql') do (
...
will be in reverse alphabetical order, the minus changes the direction, there shouldn't be a space after the minus, but I've added one to stop them being emoticons.
...
for /f %%i in ('dir /b /o:- S %3\*.sql') do (
...
will be in the filesize order largest first
...
for /f %%i in ('dir /b /o:- D %3\*.sql') do (
...
will be in the modified date order with the last changed first, which should be the same as
...
for /f %%i in ('dir /b /T:W /o:- D %3\*.sql') do (
...
but may not be the same as
...
for /f %%i in ('dir /b /T:C /o:- D %3\*.sql') do (
...
which is reverse creation date order.
This will also allow you to traverse sub-directories and even sort within them e.g.
...
for /f %%i in ('dir /b /T:W /o:G- D %3\*.sql') do (
...
Martin
August 3, 2009 at 8:48 am
sqlist: Nice batch code. Thanks for sharing.
August 3, 2009 at 10:33 am
Martin Bell (8/2/2009)
the sqlist (7/31/2009)
Mmm... the delete option doesn't seem to work!
What delete option?
I tested that code right before I posted it and everything worked fine.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
August 3, 2009 at 1:17 pm
the sqlist (8/3/2009)
Martin Bell (8/2/2009)
the sqlist (7/31/2009)
Mmm... the delete option doesn't seem to work!
What delete option?
I tested that code right before I posted it and everything worked fine.
I was talking about the functionality to a delete message in the forums. For some reason my post reverted to be just the quote and I could not delete it.
I am sure you code works fine.
Martin
August 4, 2009 at 12:34 am
Thanks for sharing the .bat code.
For daily scripts deployment on multiple databases, I usually build a SQLCMD script in 2 steps:
1. Copy the .sql file paths into a new file, e.g.,
C:\sqlscripts\01.script1.sql
C:\sqlscripts\02.script2.sql
C:\sqlscripts\03.script3.sql
2. In a text editor, add ":r" at the beginning of each line and add "go" between lines (eventually add ":on error exit" as first line), e.g.,
:on error exit
:r C:\sqlscripts\01.script1.sql
go
:r C:\sqlscripts\02.script2.sql
go
:r C:\sqlscripts\03.script3.sql
go
3. Done. SQLCMD script is ready to use.
August 4, 2009 at 8:27 am
Something important to be aware of mentioned in BOL:
SQL Server Management Studio uses .NET SqlClient for execution in regular and SQLCMD mode. When run from the command line, SQLCMD uses the OLE DB provider. Because different default options may apply, it is possible to get different behavior while executing the same query in SQL Server Management Studio SQLCMD Mode, and in the SQLCMD utility.
I am a proponent of using SQLCMD to apply scripts but have been tripped up in the past by the fact that applying them using SQLCMD can have a different behavior than from SSMS. So be sure of the connection settings SQLCMD will have and the ones you have while developing them using SSMS. You want them to be the same or you may have strange things happening.
I think we would see this issue most when stored procs, functions, etc. were applied with SQLCMD and other dependent or called or calling stored procs, functions, etc. were applied using SSMS.
August 5, 2009 at 9:06 am
tpa (8/4/2009)
Thanks for sharing the .bat code.For daily scripts deployment on multiple databases, I usually build a SQLCMD script in 2 steps:
1. Copy the .sql file paths into a new file, e.g.,
C:\sqlscripts\01.script1.sql
C:\sqlscripts\02.script2.sql
C:\sqlscripts\03.script3.sql
2. In a text editor, add ":r" at the beginning of each line and add "go" between lines (eventually add ":on error exit" as first line), e.g.,
:on error exit
:r C:\sqlscripts\01.script1.sql
go
:r C:\sqlscripts\02.script2.sql
go
:r C:\sqlscripts\03.script3.sql
go
3. Done. SQLCMD script is ready to use.
Is this something you do in SQLCMD mode? I tested that functionality but I haven't really used it so far. Is it worthed?
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
August 5, 2009 at 1:13 pm
This file can also be created from a batch file without manual intervention e.g.
del runsql.sql
echo :on error exit > runsql.lsq
for %%f in (*.sql) do ( echo :r "%%~ff" >> runsql.lsq && echo go >> runsql.lsq )
ren runsql.lsq runsql.sql
Martin
August 10, 2009 at 1:01 am
Yes, the resulting script must be run in SQLCMD mode.
The ability to include .sql files into other .sql files is really useful and SQLCMD certainly has more features I should look into...
Viewing 15 posts - 16 through 30 (of 72 total)
You must be logged in to reply to this topic. Login to reply