SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sadequl Hussain / A Simple way of Automating Scripts / Latest Posts

RE: A Simple way of Automating Scripts

pamalatha — Wed, 07 Mar 2012 16:56:02 GMT

I get an error on executing the script and I am ot able to find it. I was looking for this kind of script and now I am not able to use it because of the error. Msg 245, Level 16, State 1, Line 59Conversion failed when converting the nvarchar value 'usp_GetListOwner_AZ00' to data type int.Please help me fix this.Thank you

RE: A Simple way of Automating Scripts

siddhartha.datta — Wed, 15 Feb 2012 07:34:15 GMT

Sadiq,could you let me know where to put the database name on which I am running the scripts, also will it work on SQL 2008?

RE: A Simple way of Automating Scripts

Rod at work — Tue, 17 Jan 2012 11:24:32 GMT

Would this also work for SQL Express databases?

RE: A Simple way of Automating Scripts

bstephens — Tue, 03 Jan 2012 10:17:38 GMT

We use something very similar where I work. The key difference for us is that we have a table in the database to record which scripts have run. We execute a batch file in our script directory and it proceeds to run the SQL files in the same directory in list order (we prefix filenames with descending date format; multiples on one day would be 20120103a, b, and so on). Each SQL file consists of 2 parts: the wrapper SQL and the payload SQL. The wrapper sql executes a stored procedure to see if this script has already been run in the db. If it has not, the payload is executed and the script name is inserted into the database table. We also write the output of the cmd window to a text file. The main benefit of the table in the database is that it essentially 'versions' the database. We have dozens of environments in development and QA, and developers will restore newer backups at their own pace. This means all these databases can be from various points in time with scripts run up to the point the DB was backed up/sanitized from production. Since we draw from a common SQL folder in each branch, this allows us to automate running the scripts after any db restore, regardless of the date of the db. After restoring a db, just get latest code and execute the batch script. We archive SQL scripts quarterly in the script folder so a developer would need to update their db at that rate, which is quite reasonable.It should be noted that we do not use this process for production deployment, as we have a release DBA execute the scripts in order manually, although there is no reason this process could not be done in production. Although we run the batch repeatedly on restored test dbs prior to a release, things can happen in a moving target like production (moving wrt data) and we prefer to slow things down with single script execution. We have a GUI in our application that we can monitor the executed scripts table to verify that the dba is running them in order.The one hiccup we had recently was relating to updating some strings that contained some javascript syntax (with @ signs, I believe) and the SQLCMD was treating it like a variable. Since our scripts in production are run via SSMS, we could not use the same escape mechanism for SQLCMD since they are not compatible. We ended up breaking up the updates into a couple parts using concatenation of existing column values.There's probably a slicker way to do parts of what I have described. I'm a developer, not a DBA, though with many SQL server installations, the developer gets to wear that hat a little, and not always by choice.

RE: A Simple way of Automating Scripts

Revenant — Tue, 03 Jan 2012 09:51:36 GMT

[quote][b]Martin Bell-290511 (1/3/2012)[/b][hr]Adding TFS to the equation iwill increase the cost![/quote]Not if you cost it over say a year, and factor in that TFS is a management tool, a key part of test environment, and at the same time a deployment tool.

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Tue, 03 Jan 2012 01:37:05 GMT

Adding TFS to the equation iwill increase the cost!

RE: A Simple way of Automating Scripts

Revenant — Mon, 02 Jan 2012 19:50:53 GMT

[quote][b]Martin Bell-290511 (12/30/2011)[/b][hr]Cost is the usual reason given for not using VS database projects.[/quote]With 2008, usually not a problem if you do it right, meaning that you keep your projects in TFS ad deploy from TFS.The big f***up is that 2010 does not support BI and that you cannot do deployments of cubes and reports from the same version of VS.

RE: A Simple way of Automating Scripts

Revenant — Mon, 02 Jan 2012 19:48:26 GMT

[quote][b]gchornenkyy (12/30/2011)[/b][hr]I mentioned Visual Studio 2010 Ultimate or Premium Editions.It has everything you need now - don't wait for 2012:-)The only problem - no multi-sever deployment tool[/quote]Db deployment was made available in VS 2008 Database Edition.Multiserver deployment and Azure deployment are coming in 2012.

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Fri, 30 Dec 2011 13:41:23 GMT

Cost is the usual reason given for not using VS database projects.

RE: A Simple way of Automating Scripts

gchornenkyy — Fri, 30 Dec 2011 08:53:13 GMT

I mentioned Visual Studio 2010 Ultimate or Premium Editions.It has everything you need now - don't wait for 2012:-)The only problem - no multi-sever deployment tool

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Fri, 30 Dec 2011 08:44:54 GMT

For me SSDT is one of the great things in 2012.. looking forward to the release next year :-)

RE: A Simple way of Automating Scripts

gchornenkyy — Fri, 30 Dec 2011 08:12:31 GMT

Very long discussion... Visual Studio was mentioned just once I'd say...Why do not use Visual Studio Database project functionality (with any code version control system)?Use normal build functionality, get *.DBSchema file, deploy it with VSDBCMD utility .... ?

RE: A Simple way of Automating Scripts

vijay.singh 97987 — Tue, 30 Nov 2010 02:35:15 GMT

Hi Sadequl Hussain,Thanks.Very use full script. I have 1 question. how can we capture the output of the result file to and variable?Regards,Vijay Singh.C

RE: A Simple way of Automating Scripts

Steve Jones - SSC Editor — Mon, 29 Nov 2010 12:53:20 GMT

I still think my old method of using scripts is a good one. It handles dependencies, and it integrates VCS with a release process.Building a stored procedure for a release can work, but I'm not sure it's that efficient, especially as the release grows.[url]http://www.sqlservercentral.com/articles/System+Development+Life+Cycle/vcspart3/525/[/url]

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Mon, 29 Nov 2010 12:22:47 GMT

This may work for you, but it is not going to work or be a option for everyone.

RE: A Simple way of Automating Scripts

Doug Bishop — Mon, 29 Nov 2010 12:10:02 GMT

[quote][b]Martin Bell-290511 (11/29/2010)[/b][hr][quote][b]Doug Bishop (11/29/2010)[/b][hr]I am still confused as to how using scripts is better than using stored procedures and using a job with multiple steps to control the flow. Furthermore, in Sadequl's article, he specifically mentions that you cannot check for errors that occur between scripts, something that can be done with a job and you are controlling the flow of execution of the steps. You could add that, but then your code to execute code starts becoming complex, using pipes and FIND and branching (mind you extremely limited branching). Using a job, each step can branch to a completely different step if the executing step fails, thus customizing error handling.[/quote]For software releases, especially if they are to multiple servers or third party systems you need a rubust and reliable method to deploy your scripts. A job is just not going to do this, and there is always the question of creating the job in the first place. There are means to check a script has worked so I am not sure where in Sadequl's article it says otherwise or if it's been taken in the correct context.[quote][b]Doug Bishop[/b][hr]I am also somewhat opposed to saying, "execute anything in the folder and always assume the last character of the filename (prior to the period) is the sort order". The code presented would attempt to execute a temp file, a text file, a backup copy of a current script, etc. At the very least, if I was going to implement this methodology, I would have a control table in SQL Server that specified the script name and order of execution. I could then pull the script name from that table in execution order. Then I could give my scripts meaningful names without worrying about them having the "correct" prefix or suffix.[/quote]If you control what is in the given folder and the names of the files, then it is not a problem. How do you get the data into the control tables that you mention?[quote][b]Doug Bishop[/b][hr]I did not state, or even imply that backups are a method of version control. I stated that you can either integrate your version control software into SQL Server, or script the procedure and manually add updates to your version control (check out existing, check in changes). I was implying that backing up the database backs up the procedures.[/quote]You can integrate version control with SSMS, that is a tool not SQL Server itself and Visual Studio or some third party tools can also interface with version control systems. Version control is only the starting point for a release process and a small part of implementing change control. Third party tools can package up releases for you, but not everyone wants to use them or has the budgets to buy them.Martin[/quote]Dilemna: you have some form of version control software that you want to integrate for change management, but it is not integrated into SQL Server.Solution: script the stored procedure and save the script under version control. Check that in and out of your version control. A scripted stored procedure is a script.Chances are, if you are maintaining the scripts, you are checking them out, modifying and executing them for testing in SSMS and saving the completed script for checkin. If it is a procedure, right-click on it in the SSMS object explorer, select Script Stored Procedure As > Create to > New Query Editor Window (or File). As far as packaging up releases, the job does that for you and control of execution is maintained in the order of the job steps.

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Mon, 29 Nov 2010 11:54:49 GMT

[quote][b]Doug Bishop (11/29/2010)[/b][hr]I am still confused as to how using scripts is better than using stored procedures and using a job with multiple steps to control the flow. Furthermore, in Sadequl's article, he specifically mentions that you cannot check for errors that occur between scripts, something that can be done with a job and you are controlling the flow of execution of the steps. You could add that, but then your code to execute code starts becoming complex, using pipes and FIND and branching (mind you extremely limited branching). Using a job, each step can branch to a completely different step if the executing step fails, thus customizing error handling.[/quote]For software releases, especially if they are to multiple servers or third party systems you need a rubust and reliable method to deploy your scripts. A job is just not going to do this, and there is always the question of creating the job in the first place. There are means to check a script has worked so I am not sure where in Sadequl's article it says otherwise or if it's been taken in the correct context.[quote][b]Doug Bishop[/b][hr]I am also somewhat opposed to saying, "execute anything in the folder and always assume the last character of the filename (prior to the period) is the sort order". The code presented would attempt to execute a temp file, a text file, a backup copy of a current script, etc. At the very least, if I was going to implement this methodology, I would have a control table in SQL Server that specified the script name and order of execution. I could then pull the script name from that table in execution order. Then I could give my scripts meaningful names without worrying about them having the "correct" prefix or suffix.[/quote]If you control what is in the given folder and the names of the files, then it is not a problem. How do you get the data into the control tables that you mention?[quote][b]Doug Bishop[/b][hr]I did not state, or even imply that backups are a method of version control. I stated that you can either integrate your version control software into SQL Server, or script the procedure and manually add updates to your version control (check out existing, check in changes). I was implying that backing up the database backs up the procedures.[/quote]You can integrate version control with SSMS, that is a tool not SQL Server itself and Visual Studio or some third party tools can also interface with version control systems. Version control is only the starting point for a release process and a small part of implementing change control. Third party tools can package up releases for you, but not everyone wants to use them or has the budgets to buy them.Martin

RE: A Simple way of Automating Scripts

Doug Bishop — Mon, 29 Nov 2010 10:54:37 GMT

[quote][b]Martin Bell-290511 (11/29/2010)[/b][hr]If you are using version control then you would use the script held in version control as the definitive copy. Relying on backups to keep a history of version of a procedure is not workable when there are more than one developer working on a project or if you need to keep an accurate history of changes. Making changes on the fly is also not acceptable on systems that require the code changes to be accountable, and when you start to implement releases rather than ad-hoc changes you will usually need to run one or more script (procedure or otherwise) in a given order and in a reliable way.. hence the reason for this thread.Martin[/quote]I am still confused as to how using scripts is better than using stored procedures and using a job with multiple steps to control the flow. Furthermore, in Sadequl's article, he specifically mentions that you cannot check for errors that occur between scripts, something that can be done with a job and you are controlling the flow of execution of the steps. You could add that, but then your code to execute code starts becoming complex, using pipes and FIND and branching (mind you extremely limited branching). Using a job, each step can branch to a completely different step if the executing step fails, thus customizing error handling.I am also somewhat opposed to saying, "execute anything in the folder and always assume the last character of the filename (prior to the period) is the sort order". The code presented would attempt to execute a temp file, a text file, a backup copy of a current script, etc. At the very least, if I was going to implement this methodology, I would have a control table in SQL Server that specified the script name and order of execution. I could then pull the script name from that table in execution order. Then I could give my scripts meaningful names without worrying about them having the "correct" prefix or suffix.I did not state, or even imply that backups are a method of version control. I stated that you can either integrate your version control software into SQL Server, or script the procedure and manually add updates to your version control (check out existing, check in changes). I was implying that backing up the database backs up the procedures.

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Mon, 29 Nov 2010 10:35:21 GMT

[quote][b]Doug Bishop (11/29/2010)[/b]Okay, I am confused; license cost for what? A stored procedure is a script that has been saved to the database using CREATE PROCEDURE procname. There are no licensing costs, the procedure gets backed up everytime the database is backed up. If you have version control software, it can be used with SQL Server. If you don't want to integrate it into SQL Server, then script the procedure and save that manually under version control.The only reason they have in our shop for using scripts is, "that is the way it used to be done so that is how they are still doing it." Whenever I have to go in and maintain code that is in a script that is run through OSQL, I change it to a stored procedure and delete the old script code form the server.[/quote]If you are using version control then you would use the script held in version control as the definitive copy. Relying on backups to keep a history of version of a procedure is not workable when there are more than one developer working on a project or if you need to keep an accurate history of changes. Making changes on the fly is also not acceptable on systems that require the code changes to be accountable, and when you start to implement releases rather than ad-hoc changes you will usually need to run one or more script (procedure or otherwise) in a given order and in a reliable way.. hence the reason for this thread.Martin

RE: A Simple way of Automating Scripts

Doug Bishop — Mon, 29 Nov 2010 10:12:28 GMT

[quote][b]Martin Bell-290511 (11/29/2010)[/b][hr]Some people use a thing called version control for those stored procedure scripts, and although there are some flashy features in Visual Studio or third party tools to help maintain code and deploy them; some companies are either not in the position to do that; or just don't want to use them, especially when it can be done on the command line for no extra licence cost.Martin[/quote]Okay, I am confused; license cost for what? A stored procedure is a script that has been saved to the database using CREATE PROCEDURE procname. There are no licensing costs, the procedure gets backed up everytime the database is backed up. If you have version control software, it can be used with SQL Server. If you don't want to integrate it into SQL Server, then script the procedure and save that manually under version control.The only reason they have in our shop for using scripts is, "that is the way it used to be done so that is how they are still doing it." Whenever I have to go in and maintain code that is in a script that is run through OSQL, I change it to a stored procedure and delete the old script code form the server.

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Mon, 29 Nov 2010 09:56:52 GMT

[quote][b]Doug Bishop (11/29/2010)[/b][hr]Why, oh why, oh why, oh why do people still insist on using scripts? They do it here too and it is frustrating. Anyone ever hear about a new object in SQL Server called stored procedures? Not only that, but SQL Server also came out with something called the SQL Server AGent a while back (maybe not everyone is read up on it yet), and Jobs. The jobs have steps and you can control the order in which you execute stored procedures. If one fails, you can take specific action, including sending out email, paging someone, etc. It is really cool stuff. Besides, you do not need to learn hieroglyphics (@for /r %%s in (.) do (echo ..............%%s /b >>).Not only is it more efficient, using stored procedures allows SQL Server to compile the code and results in overall faster execution. And, if you need to insert something between *_A and *_B, just insert a job step, and all references in both upstream and downstream steps are automatically adjusted.[/quote]Some people use a thing called version control for those stored procedure scripts, and although there are some flashy features in Visual Studio or third party tools to help maintain code and deploy them; some companies are either not in the position to do that; or just don't want to use them, especially when it can be done on the command line for no extra licence cost.Martin

RE: A Simple way of Automating Scripts

Doug Bishop — Mon, 29 Nov 2010 09:20:48 GMT

Why, oh why, oh why, oh why do people still insist on using scripts? They do it here too and it is frustrating. Anyone ever hear about a new object in SQL Server called stored procedures? Not only that, but SQL Server also came out with something called the SQL Server AGent a while back (maybe not everyone is read up on it yet), and Jobs. The jobs have steps and you can control the order in which you execute stored procedures. If one fails, you can take specific action, including sending out email, paging someone, etc. It is really cool stuff. Besides, you do not need to learn hieroglyphics (@for /r %%s in (.) do (echo ..............%%s /b >>).Not only is it more efficient, using stored procedures allows SQL Server to compile the code and results in overall faster execution. And, if you need to insert something between *_A and *_B, just insert a job step, and all references in both upstream and downstream steps are automatically adjusted.

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Fri, 26 Nov 2010 12:53:04 GMT

For Oracle you can use SQL*Plus on a command line and the START option i.e. @filename will allow you to run a file. Alternatively you can use the START command from within SQL*Plus. Documentation for SQL*Plus can be found at http://www.oracle.com/technology/docs/tech/sql_plus/index.html. The rest is basically the same.Martin

RE: A Simple way of Automating Scripts

glenn_91 — Fri, 26 Nov 2010 09:42:36 GMT

An excellent presentation of how to automate these scripts! I know I will use it in my work.I wonder if you or anyone else in this forum could take this SQL Server model and modify it to do the same things using PL/SQL and an Oracle database? Most of my work is with Oracle databases and having the ability to automate my PL/SQL scripts in this fashion would be tremendously valuable.Thanks for your consideration!

RE: A Simple way of Automating Scripts

GabyYYZ — Thu, 25 Nov 2010 10:07:54 GMT

Another quirk I found for SQLCMD, and it affected a rollout of about 90 stored procs, was to use the -I option (set quoted identifiers on), as not all sql scripts have that inside. E.G.:[code="plain"]@echo offfor /f "delims=] tokens=1*" %%a in ('find /v /n "" ^<%1') do ( sqlcmd -S DBSERVER\InstanceName -E -d AdventureWorks -i %%b -I)[/code]Trust me on this one, saves you having the developer scratching his head and you going crazy trying to figure this one out.EDIT: stole this script from somewhere, so not original, except maybe the -I :-D

RE: A Simple way of Automating Scripts

WILLIAM MITCHELL — Thu, 25 Nov 2010 09:58:44 GMT

In my world it is the job of the developer to provide the DBA with a single script file. The developer prefixes their script files with 01_, 02_ etc. to ensure the correct sequence, and uses a simple VB program with the FileSystemObject to assemble the scripts, with comments for filename & last modified date, into a single file. (Each individual script ends with a GO to prevent any conflicts between the scripts.) The prior-night production backup & tlogs are restored to the dev database, and the dev applies the change script to the dev copy to verify that everything works as expected. Then, that script to sent to the DBA to apply to production.

RE: A Simple way of Automating Scripts

yordan.georgiev — Thu, 25 Nov 2010 09:39:07 GMT

Duh .. I am able to port any over 100 GB db on MSSQL from server to server , so this DOES work, since I have used it also in production ; ) The reason why it did not work for somebody are the different regional settings which make the log files paths impossible and it fails ... this is the reason why you should have the : Get_Nice_Time script . ( yee I know there are batches to get the date in nice format but they ALways have bugs , blame Microsoft for not implmenting the simple `date +%Y.%m.%d-%H:%M:%S`in cmd .. ) and the script configurator , which is probably going to be different for all your environments ... mkdir : D:\cas\sfw\sql\sql.0.5.9.20101023_232611\Install\scriptsDownload : http://dl.dropbox.com/u/9726039/cas/sfw/sql.0.5.9.20101023_232611_20101125_182852.zipUnpack zip to D:\cas\sfw\sqlgo to : D:\cas\sfw\sql\sql.0.5.9.20101023_232611\Install\scriptsI have commented EACH CALL in the batch scripts , so ... If all this does not help ... I cannot help you ... You could downvote me ; ) ... I have used to it ... our outsourced dba's use to do it all the time ; ) ...During TechEd 2010 in Berlin several weeks ago .. Microsoft demoed their PACK-DAC or whatever solution ... It might be also more convenient for somebody , so it might be worth checking .... However I have set the following requirements: - The db should be scriptable via command line : both schema and data - the db should be portable between different mssql servers via simple command line ...- the db should be easy to deploy via the command line - just a single double click should be enough ... ( This is because the so called outsourced dba's actually do not understand a shit and burn money by asking stupid questions and changing the steps provided to them in the instructions ...

RE: A Simple way of Automating Scripts

SQLRNNR — Thu, 25 Nov 2010 09:27:25 GMT

Thanks for the article.

RE: A Simple way of Automating Scripts

the sqlist — Thu, 25 Nov 2010 09:23:28 GMT

[quote][b]yordan.georgiev (11/25/2010)[/b][hr]Hi , *** Don't just give the hungry man a fish, teach him how to catch it as well.In my opinion this is exactly what I did ; ) [/quote]Sorry, man, you just gave me a fish in the form of a batch file which is very difficult to "decrypt" in terms of functionality. I also tried it and didn't work. I have a script like that which I built by myself but I am always willing to learn new tricks if possible. I only wanted to understand what you did.Thanks.

RE: A Simple way of Automating Scripts

yordan.georgiev — Thu, 25 Nov 2010 09:19:20 GMT

Hi , *** Don't just give the hungry man a fish, teach him how to catch it as well.In my opinion this is exactly what I did ; ) Ok .. Download this project : http://ysgitdiary.blogspot.com/2010/10/cassqldev-out-now.htmland check the Install/scripts section - it has the whole solution + Bonus for logging both selects and errors ; )

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Thu, 25 Nov 2010 08:31:20 GMT

Maybe my blog posts explains all of this better, it pre-dates all of these articles. http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/02/How-to-execute-multiple-sql-scripts.aspxor if you use Powershellhttp://sqlblogcasts.com/blogs/martinbell/archive/2009/07/30/Executing-all-.SQL-files-in-a-directory-with-Powershell.aspxMartin

RE: A Simple way of Automating Scripts

the sqlist — Thu, 25 Nov 2010 08:16:08 GMT

@yordan.georgievI checked your batch file on your blog but can you describe a little bit what and how is it doing?.Thanks.

RE: A Simple way of Automating Scripts

yordan.georgiev — Thu, 25 Nov 2010 03:47:11 GMT

There is a solution to be able to deploy sql scripts from folder structure ... so that the dba would do a simple double-click .... regardless of the where the script is executed , to which server to which instance , database , etc. based on the simple principle : for /f %i in ('dir *.SQL /s /b /o') DO ECHO %DATE% --- %TIME% RUNNING %i 1>>"..\install.log"&SQLCMD -U sqlloginUserName -P SecretPass -H hostname -d cas_dev -t 30 -w 80 -u -p 1 -b -i %i -r1 1>> "..\install.log" 2>> "..\error.log" http://ysgitdiary.blogspot.com/2010/04/blog-post_26.html

RE: A Simple way of Automating Scripts

asimsubedi — Mon, 18 Oct 2010 02:01:41 GMT

good

RE: A Simple way of Automating Scripts

Varghese Pathisseril — Wed, 01 Sep 2010 12:46:22 GMT

There was a smilar article published on 8/23/2010 http://www.sqlservercentral.com/articles/scripting/70783/but neither cases no discussions about how to handle error or script failure and notifications.Good article.

RE: A Simple way of Automating Scripts

Martin Bell-290511 — Tue, 11 Aug 2009 12:39:26 GMT

[quote][b]Sqlfrenzy (8/11/2009)[/b][hr]actually I was trying to communicate that if there is a begin tran without any corresponding rollback orcommit in the script to be executed...how this can be taken care of....[/quote]You should be able to check @@TRANCOUNTMartin

RE: A Simple way of Automating Scripts

Adam Gojdas — Tue, 11 Aug 2009 11:09:46 GMT

Or maybe use a while loop like this to check for all transactions and then commit or rollback until finished and none are found. Have never tried it this way so beware.:[code="sql"]BEGIN TRY BEGIN TRANSACTION; :r "C:\sqlscripts\01.script1.sql" :r "C:\sqlscripts\02.script2.sql" WHILE (XACT_STATE()) = 1 BEGIN PRINT 'COMMIT'; COMMIT TRANSACTION; END;END TRYBEGIN CATCH WHILE (XACT_STATE()) <> 0 BEGIN PRINT 'ROLLBACK'; ROLLBACK TRANSACTION; END; SELECT ERROR_NUMBER() [ErrorNumber] ,ERROR_SEVERITY() [ErrorSeverity] ,ERROR_STATE() [ErrorState] ,ERROR_PROCEDURE() [ErrorProcedure] ,ERROR_LINE() [ErrorLine] ,ERROR_MESSAGE() [ErrorMessage];END CATCH;[/code]

RE: A Simple way of Automating Scripts

Adam Gojdas — Tue, 11 Aug 2009 10:57:42 GMT

[quote]actually I was trying to communicate that if there is a begin tran without any corresponding rollback orcommit in the script to be executed...how this can be taken care of....Regards,Sqlfrenzy[/quote]Then maybe if you don't have the BEGIN TRANSACTION in the code I showed. Since it checks for the TRANSACTION state before executing the COMMIT or ROLLBACK I would think this could do what you need. [code]BEGIN TRY --BEGIN TRANSACTION; :r "C:\sqlscripts\01.script1.sql" :r "C:\sqlscripts\02.script2.sql" IF (XACT_STATE()) = 1 BEGIN PRINT 'COMMIT'; COMMIT TRANSACTION; END;END TRYBEGIN CATCH IF (XACT_STATE()) <> 0 BEGIN PRINT 'ROLLBACK'; ROLLBACK TRANSACTION; END; SELECT ERROR_NUMBER() [ErrorNumber] ,ERROR_SEVERITY() [ErrorSeverity] ,ERROR_STATE() [ErrorState] ,ERROR_PROCEDURE() [ErrorProcedure] ,ERROR_LINE() [ErrorLine] ,ERROR_MESSAGE() [ErrorMessage];END CATCH;[/code]But why not just put the TRANSACTION there to begin with?

RE: A Simple way of Automating Scripts

Sqlfrenzy — Tue, 11 Aug 2009 10:37:39 GMT

[quote][b]tpa (8/11/2009)[/b][hr][quote][b]Sqlfrenzy (8/11/2009)[/b][hr]Is their a way to handle begin tran statement when executing script thru sqlcmd...[/quote]Yep, you could use a transaction like this:[code="sql"]begin tran:r "C:\sqlscripts\01.script1.sql"if (@@error != 0) goto endRollback :r "C:\sqlscripts\02.script2.sql" if (@@error != 0) goto endRollbackprint 'commit'commit tran goto endFinalendRollback: print 'rollback' rollback tran endFinal: print 'endFinal' [/code][/quote]actually I was trying to communicate that if there is a begin tran without any corresponding rollback orcommit in the script to be executed...how this can be taken care of....

RE: A Simple way of Automating Scripts

Adam Gojdas — Tue, 11 Aug 2009 07:56:22 GMT

Since SQLCMD is a SQL Server 2005 tool you may want to instead use the TRY/CATCH that is available instead of the @@error. Maybe something like the following:[code]BEGIN TRY BEGIN TRANSACTION; :r "C:\sqlscripts\01.script1.sql" :r "C:\sqlscripts\02.script2.sql" IF (XACT_STATE()) = 1 BEGIN PRINT 'COMMIT'; COMMIT TRANSACTION; END;END TRYBEGIN CATCH IF (XACT_STATE()) <> 0 BEGIN PRINT 'ROLLBACK'; ROLLBACK TRANSACTION; END; SELECT ERROR_NUMBER() [ErrorNumber] ,ERROR_SEVERITY() [ErrorSeverity] ,ERROR_STATE() [ErrorState] ,ERROR_PROCEDURE() [ErrorProcedure] ,ERROR_LINE() [ErrorLine] ,ERROR_MESSAGE() [ErrorMessage];END CATCH;[/code]