SQLCMD.EXE and transactions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQLCMD.EXE and transactions

Author

Topic

SQLMark
Starting Member

18 Posts

Posted - 2008-12-23 : 09:40:32

Hi all!

Well...I have a little setup project (builded with InstallShield 2008) that at certain point launch some scripts stored in

SQL data files using the SQLCMD.exe utility. It launch for example the 'Script1.sql' file, than the 'Script2.sql' and so on.

When something goes wrong (for example when SQL crashes or when the customer database has an unexpected structure version so

single TSQL file crash...), SQLCMD.EXE automatically rollback the command that raised the exception but unfortunately not all

the scripts that I've launched.
I was unable to find the way to manage the TRANSACTIONS with SQLCMD.EXE!

What I want to do is to rollback ALL the scripts launched when something goes wrong, so the effect is to preserve the

integral database.

Therefore consider that:
1. it is impossible to modify all the script adding the TRANSACTIONS management inside all files!
2. IS 2008 doesn't manage any way to transact the DBs
3. please don't tell me to backup database during setup (is too hard to restore them in setup mode!)

I thought a possible solution but I don't know if it is so correct: launch a single command 'BEGIN TRANSACTION' before launching the list of scripts file, if all works right launch a static command like 'COMMIT', launch the 'ROLLBACK' string if else.

It's a good solution (note that inside SQL files there are other TRANS commands)? It is better to manage a named transaction

like:
BEGIN TRANS ALLSQLSCRIPTS

...

COMMIT/ROLLBACK ALLSQLSCRIPTS

Thanks in advance!

Mark

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-12-23 : 11:07:34

hi,
you maybe able to do your last idea but turning off autocommit at the start of your scripts.

conn.setAutoCommit(false);

roberto12cr
Starting Member

1 Post

Posted - 2009-05-27 : 15:53:18

How do you think to do this?

I have tryed something like this:

BEGIN TRAN T1
:r C:\TEST01.SQL
:r C:\TEST02.SQL
IF @@ERROR = 0 COMMIT TRAN ELSE ROLLBACK TRAN
GO

but the result is:
INCORRECT SYNTAX NEAR THE KEYWORD '@@ERROR'

Could you help me with this?

iamtig
Starting Member

3 Posts

Posted - 2009-06-16 : 11:15:06

Do not remember where I got this sample code from, but basically you can specify an error file location and then load the error file to check to see if errors occurred
:SETVAR workpath "c:\errorpath\"
:SETVAR Errorfile "Errors.txt"
--specify the name of the error file
:Error $(workpath)$(Errorfile)

Then add this after the scripts are executed
CREATE TABLE #errors ( line VARCHAR(8000) )

DECLARE @errors VARCHAR(MAX)

INSERT INTO #Errors ( line )--any Errors
EXECUTE xp_cmdshell 'TYPE $(workpath)$(Errorfile)'

delete from #Errors where line IS NULL

SELECT @errors=COALESCE(@Errors,'')+line FROM #errors
SELECT @errors

IF LEN(@errors)>0

BEGIN --report the error and quit
SELECT 'tHERE WERE ERRORs - attempting rollback'

ROLLBACK TRANSACTION

RAISERROR ('there were errors %s', 16,1,@errors)
end

mghome
Starting Member

1 Post

Posted - 2010-06-22 : 11:18:20

Has any body found a solution to begin a transaction. Call many sql scripts with sqlcmd. And later rollback?

nettle
Starting Member

1 Post

Posted - 2010-07-21 : 04:00:35

I think I have a solution:

We have a batch file containing:

@sqlcmd -E -S%DBServer% -d%Database% -b -iMain.sql

Where Main.sql is a script file for sqlcmd containing:
:r Start.sql
:r Script01.sql
:r Script02.sql
:r End.sql
Start.sql is just BEGIN TRANSACTION
End.sql is COMMIT TRANSACTION
and each script (scriopt01.sql etc) contains:

IF (@@ERROR<>0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END

after any command that modifies the database.

Because all the scripts are run in one sqlcmd session the transaction stays open for all of them and the rollback on error rolls back all the changes from all the scripts.

Hope this helps

surf4sql
Starting Member

1 Post

Posted - 2011-01-17 : 10:03:25

Testing out your solution as looking to do the same.

Is that the full batchfile? Have followed your post and and running from command prompt unsuccessfully.

Error is -S missing argument, have never used sqlcmd in the context before.

Am executing as follows;

C:\>dbinstall -S JoeBloggs -d master

quote:
Originally posted by nettle

I think I have a solution:

We have a batch file containing:

@sqlcmd -E -S%DBServer% -d%Database% -b -iMain.sql

Where Main.sql is a script file for sqlcmd containing:
:r Start.sql
:r Script01.sql
:r Script02.sql
:r End.sql
Start.sql is just BEGIN TRANSACTION
End.sql is COMMIT TRANSACTION
and each script (scriopt01.sql etc) contains:

IF (@@ERROR<>0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END

after any command that modifies the database.

Because all the scripts are run in one sqlcmd session the transaction stays open for all of them and the rollback on error rolls back all the changes from all the scripts.

Hope this helps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources