August 11, 2009 at 12:14 am
Is their a way to handle begin tran statement when executing script thru sqlcmd...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
August 11, 2009 at 4:54 am
Sqlfrenzy (8/11/2009)
Is their a way to handle begin tran statement when executing script thru sqlcmd...
Yep, you could use a transaction like this:
begin tran
:r "C:\sqlscripts\01.script1.sql"
if (@@error != 0) goto endRollback
:r "C:\sqlscripts\02.script2.sql"
if (@@error != 0) goto endRollback
print 'commit'
commit tran
goto endFinal
endRollback:
print 'rollback'
rollback tran
endFinal:
print 'endFinal'
August 11, 2009 at 7:56 am
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:
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 TRY
BEGIN 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;
August 11, 2009 at 10:37 am
tpa (8/11/2009)
Sqlfrenzy (8/11/2009)
Is their a way to handle begin tran statement when executing script thru sqlcmd...Yep, you could use a transaction like this:
begin tran:r "C:\sqlscripts\01.script1.sql"
if (@@error != 0) goto endRollback
:r "C:\sqlscripts\02.script2.sql"
if (@@error != 0) goto endRollback
print 'commit'
commit tran
goto endFinal
endRollback:
print 'rollback'
rollback tran
endFinal:
print 'endFinal'
actually I was trying to communicate that if there is a begin tran without any corresponding rollback or
commit in the script to be executed...how this can be taken care of....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
August 11, 2009 at 10:57 am
actually I was trying to communicate that if there is a begin tran without any corresponding rollback or
commit in the script to be executed...how this can be taken care of....
Regards,
Sqlfrenzy
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.
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 TRY
BEGIN 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;
But why not just put the TRANSACTION there to begin with?
August 11, 2009 at 11:09 am
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.:
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 TRY
BEGIN 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;
August 11, 2009 at 12:39 pm
Sqlfrenzy (8/11/2009)
actually I was trying to communicate that if there is a begin tran without any corresponding rollback or
commit in the script to be executed...how this can be taken care of....
You should be able to check @@TRANCOUNT
Martin
September 1, 2010 at 12:46 pm
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.
October 18, 2010 at 2:01 am
good
November 25, 2010 at 3:47 am
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"
November 25, 2010 at 8:16 am
@yordan.georgiev
I checked your batch file on your blog but can you describe a little bit what and how is it doing?.
Thanks.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
November 25, 2010 at 8:31 am
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.aspx
or if you use Powershell
Martin
November 25, 2010 at 9:19 am
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.html
and check the Install/scripts section - it has the whole solution + Bonus for logging both selects and errors ; )
November 25, 2010 at 9:23 am
yordan.georgiev (11/25/2010)
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 ; )
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.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
November 25, 2010 at 9:27 am
Thanks for the article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 31 through 45 (of 72 total)
You must be logged in to reply to this topic. Login to reply