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