Using xp_cmdshell in a Transaction – SQL Server 2008 R2

scriptingsql-server-2008-r2t-sqltransaction

I need to run multiple scripts in a certain folder in a transaction and rollback if there are any errors. At this point I'm just trying to get the rollback to work without a try catch.

Currently when I run the script, it gets the proper list of sql files recursively, but does not rollback the changes if there is an error. If there are 10 files and 3 complete but there is an error on the 4th, the first 3 files do not rollback.

Here is my script:

BEGIN TRANSACTION

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO

DECLARE @folderPath VARCHAR(100) = 'folder\path' 
DECLARE @cmd VARCHAR(8000) = 'dir /b /s "' + @folderPath + '\*.sql"'

INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell @cmd
GO 

DECLARE cFiles CURSOR LOCAL FOR
    SELECT DISTINCT
        [SQLFileName]
    FROM ##SQLFiles
    WHERE [SQLFileName] IS NOT NULL AND
          [SQLFileName] != 'NULL'
    ORDER BY [SQLFileName]

DECLARE @folderPath         VARCHAR(100) = 'folder\path' 
DECLARE @vFileName          VARCHAR(200)
DECLARE @vSQLStmt           VARCHAR(4000)

SELECT * FROM ##SQLFiles

OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ServerName -U UserName -P "password" -d dbname -i "' + @folderPath + @vFileName + '"'''
    PRINT @vSQLStmt
    EXECUTE (@vSQLStmt)

    FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO

DROP TABLE ##SQLFiles
GO

ROLLBACK TRANSACTION

Is there any way to get the changes made by the files to be rolled back?

Best Answer

No, absolutely impossible. Each sqlcmd invocation is a different session and transaction cannot span sessions.

Your use of a transaction on the script that invokes the sqlcmd is completely useless, it has nothing to do with the scripts being run.

Related Question