BEGIN TRANSACTION
GO
... some statements(1) ... 
GO
... a statement with an error ... 
GO
... some statements(2) ... 
GO
... COMMIT / OR ROLLBACK logic here ...

CREATE TABLE #tmpErrors (Error int)
GO
BEGIN TRANSACTION
GO
... attempt a statement ...
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
... attempt another statement ...
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 2 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0
BEGIN
	PRINT 'The database update succeeded'
	COMMIT TRANSACTION
END
ELSE
BEGIN
	PRINT 'The database update failed at these locations'
	SELECT * FROM #tmpErrors
END
GO
DROP TABLE #tmpErrors
GO

BEGIN TRANSACTION
GO
... some statements(1) ... 
GO
... a statement with an error ... 
GO
... some statements(2) ... 
GO
... COMMIT / OR ROLLBACK logic here ...

BEGIN TRANSACTION
GO
CREATE TABLE TestingTransactions (
	ID INT PRIMARY KEY,
	SomeChar CHAR(1),
	SomeDate DATETIME
);
GO

INSERT INTO TestingTransactions (ID, SomeChar, SomeDate)
VALUES (1,'A',GETDATE()); -- this succeeds
GO

INSERT INTO TestingTransactions (ID, SomeChar, SomeDate)
VALUES (1,'A',GETDATE()); -- this fails, primary key violation. We're still in the transaction though....
GO

INSERT INTO TestingTransactions (ID, SomeChar, SomeDate)
VALUES (2,'B',GETDATE()); -- this succeeds. We're still in the transaction....
GO

SELECT * FROM TestingTransactions -- returns 2 rows

ROLLBACK TRANSACTION -- all gone, right back to the begin tran

SELECT * FROM TestingTransactions -- Invalid object name 'TestingTransactions'.

-- Or, alternately

BEGIN TRANSACTION
GO
CREATE TABLE TestingTransactions (
	ID INT PRIMARY KEY,
	SomeChar CHAR(1),
	SomeDate DATETIME
);
GO

INSERT INTO TestingTransactions (ID, SomeChar, SomeDate)
VALUES (1,'A',GETDATE()); -- this succeeds
GO

INSERT INTO TestingTransactions (ID, SomeChar, SomeDate)
VALUES (1,'A',GETDATE()); -- this fails, primary key violation. We're still in the transaction though....
GO

INSERT INTO TestingTransactions (ID, SomeChar, SomeDate)
VALUES (2,'B',GETDATE()); -- this succeeds. We're still in the transaction....
GO

SELECT * FROM TestingTransactions -- returns 2 rows

COMMIT TRANSACTION -- all committed. 

SELECT * FROM TestingTransactions -- returns 2 rows.

CREATE TABLE ScriptTest1
(
	ID INT PRIMARY KEY,
	SomeChar CHAR(1),
	SomeDate DATETIME
)
GO

CREATE TABLE ScriptTest2
(
	ID INT PRIMARY KEY,
	SomeChar CHAR(1),
	SomeDate DATETIME
)
GO

ALTER TABLE dbo.ScriptTest2 ADD CONSTRAINT
	FK_ScriptTest2_ScriptTest1 FOREIGN KEY
	(
	ID
	) REFERENCES dbo.ScriptTest2
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

ALTER TABLE dbo.ScriptTest2
	DROP CONSTRAINT FK_ScriptTest2_ScriptTest1
GO

BEGIN TRANSACTION
GO
PRINT '(1)Create Tmp_ScriptTest2 ' + CONVERT(varchar(20), @@TRANCOUNT)
GO
CREATE TABLE dbo.Tmp_ScriptTest2
	(
	ID bigint NOT NULL,
	SomeChar char(1) NULL,
	SomeDate datetime NULL
	)  ON [PRIMARY]
GO

PRINT '(2)Transfer data ' + CONVERT(varchar(20), @@TRANCOUNT)
GO
IF EXISTS(SELECT * FROM dbo.ScriptTest2)
	 EXEC('INSERT INTO dbo.Tmp_ScriptTest2 (ID, SomeChar, SomeDate)
		SELECT CONVERT(bigint, ID), SomeChar, SomeDate FROM dbo.ScriptTest2 WITH (HOLDLOCK TABLOCKX)')
GO

PRINT '(3)Drop Fkey ' + CONVERT(varchar(20), @@TRANCOUNT)
GO
ALTER TABLE dbo.ScriptTest2
	DROP CONSTRAINT FK_ScriptTest2_ScriptTest1
GO

PRINT '(4)Drop ScriptTest2 ' + CONVERT(varchar(20), @@TRANCOUNT)
GO
DROP TABLE dbo.ScriptTest2
GO

PRINT '(5)Rename Tmp_ScriptTest2 ' + CONVERT(varchar(20), @@TRANCOUNT)
GO
EXECUTE sp_rename N'dbo.Tmp_ScriptTest2', N'ScriptTest2', 'OBJECT' 
GO

PRINT '(6)Create PK ' + CONVERT(varchar(20), @@TRANCOUNT)
GO
ALTER TABLE dbo.ScriptTest2 ADD CONSTRAINT
	PK_ScriptTest2 PRIMARY KEY CLUSTERED 
	(
	ID
	)
GO

PRINT '(7)Create FKey ' + CONVERT(varchar(20), @@TRANCOUNT)
GO
ALTER TABLE dbo.ScriptTest2 ADD CONSTRAINT
	FK_ScriptTest2_ScriptTest1 FOREIGN KEY
	(
	ID
	) REFERENCES dbo.ScriptTest2
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

PRINT '(8)Commit ' + CONVERT(varchar(20), @@TRANCOUNT)
GO
COMMIT
GO

PRINT '(9)Display data in ScriptTest2'
GO
SELECT * FROM dbo.ScriptTest2
GO

quote:

---

(1)Create Tmp_ScriptTest2 @@TranCount = 1
(2)Transfer data @@TranCount = 1
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.ScriptTest2'.
(3)Drop Fkey @@TranCount = 1
Msg 4902, Level 16, State 1, Line 1
Cannot find the object "dbo.ScriptTest2" because it does not exist or you do not have permissions.
(4)Drop ScriptTest2 @@TranCount = 1
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'dbo.ScriptTest2', because it does not exist or you do not have permission.
(5)Rename Tmp_ScriptTest2 @@TranCount = 1
Caution: Changing any part of an object name could break scripts and stored procedures.
(6)Create PK @@TranCount = 1
(7)Create FKey @@TranCount = 1
(8)Commit. @@TranCount = 1
(9)Display data in ScriptTest2

(0 row(s) affected)

---

quote:

---

Originally posted by tech_1

thanks for all your inputs. I appreciate it and welcome this.
So, I guess I need to instruct the folks who create the scripts to make sure to add GO statements after every "transaction" they have?

---

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) 
	Jul  9 2008 14:17:44 
	Copyright (c) 1988-2008 Microsoft Corporation
	Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

PRINT 'Drop tables, ignore errors'
GO
DROP TABLE ScriptTest1
GO
DROP TABLE ScriptTest2
GO
PRINT 'Drop tables DONE. Start the script:'
GO
SET XACT_ABORT OFF
GO
PRINT 'Create tables'
GO
CREATE TABLE ScriptTest1
(
	ID INT PRIMARY KEY,
	SomeChar CHAR(1),
	SomeDate DATETIME
)
GO

CREATE TABLE ScriptTest2
(
	ID INT PRIMARY KEY,
	SomeChar CHAR(1),
	SomeDate DATETIME
)
GO

PRINT 'Create FKey'
GO
ALTER TABLE dbo.ScriptTest2 ADD CONSTRAINT
	FK_ScriptTest2_ScriptTest1 FOREIGN KEY
	(
	ID
	) REFERENCES dbo.ScriptTest2
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

PRINT 'Drop FKey'
GO
ALTER TABLE dbo.ScriptTest2
	DROP CONSTRAINT FK_ScriptTest2_ScriptTest1
GO


PRINT 'Begin transaction'
GO
BEGIN TRANSACTION
GO
PRINT '(1)Create Tmp_ScriptTest2 ' + CONVERT(varchar(20), @@TRANCOUNT) + '/' + CONVERT(varchar(20), XACT_STATE())

GO
CREATE TABLE dbo.Tmp_ScriptTest2
	(
	ID bigint NOT NULL,
	SomeChar char(1) NULL,
	SomeDate datetime NULL
	)  ON [PRIMARY]
GO

PRINT '(2)Transfer data ' + CONVERT(varchar(20), @@TRANCOUNT) + '/' + CONVERT(varchar(20), XACT_STATE())
GO
IF EXISTS(SELECT * FROM dbo.ScriptTest2)
	 EXEC('INSERT INTO dbo.Tmp_ScriptTest2 (ID, SomeChar, SomeDate)
		SELECT CONVERT(bigint, ID), SomeChar, SomeDate FROM dbo.ScriptTest2 WITH (HOLDLOCK TABLOCKX)')
GO

PRINT '(3)Drop Fkey ' + CONVERT(varchar(20), @@TRANCOUNT) + '/' + CONVERT(varchar(20), XACT_STATE())
GO
ALTER TABLE dbo.ScriptTest2
	DROP CONSTRAINT FK_ScriptTest2_ScriptTest1
GO

PRINT '(4)Drop ScriptTest2 ' + CONVERT(varchar(20), @@TRANCOUNT) + '/' + CONVERT(varchar(20), XACT_STATE())
GO
DROP TABLE dbo.ScriptTest2
GO

PRINT '(5)Rename Tmp_ScriptTest2 ' + CONVERT(varchar(20), @@TRANCOUNT) + '/' + CONVERT(varchar(20), XACT_STATE())
GO
EXECUTE sp_rename N'dbo.Tmp_ScriptTest2', N'ScriptTest2', 'OBJECT' 
GO

PRINT '(6)Create PK ' + CONVERT(varchar(20), @@TRANCOUNT) + '/' + CONVERT(varchar(20), XACT_STATE())
GO
ALTER TABLE dbo.ScriptTest2 ADD CONSTRAINT
	PK_ScriptTest2 PRIMARY KEY CLUSTERED 
	(
	ID
	)
GO

PRINT '(7)Create FKey ' + CONVERT(varchar(20), @@TRANCOUNT) + '/' + CONVERT(varchar(20), XACT_STATE())
GO
ALTER TABLE dbo.ScriptTest2 ADD CONSTRAINT
	FK_ScriptTest2_ScriptTest1 FOREIGN KEY
	(
	ID
	) REFERENCES dbo.ScriptTest2
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

PRINT '(8)Rollback ' + CONVERT(varchar(20), @@TRANCOUNT) + '/' + CONVERT(varchar(20), XACT_STATE())
GO
PRINT 'ROLLBACK transaction'
GO
ROLLBACK
GO

PRINT '(9)Display data in ScriptTest2'
GO
SELECT * FROM dbo.ScriptTest2
GO

Drop tables, ignore errors
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'ScriptTest2', because it does not exist or you do not have permission.
Drop tables DONE. Start the script:
Create tables
Create FKey
Drop FKey
Begin transaction
(1)Create Tmp_ScriptTest2 1/1
(2)Transfer data 1/1
(3)Drop Fkey 1/1
Server: Msg 3728, Level 16, State 1, Line 1
'FK_ScriptTest2_ScriptTest1' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 1
Could not drop constraint. See previous errors.
(4)Drop ScriptTest2 0/0
(5)Rename Tmp_ScriptTest2 0/0
Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.
(6)Create PK 0/0
Server: Msg 4902, Level 16, State 1, Line 1
Cannot find the object "dbo.ScriptTest2" because it does not exist or you do not have permissions.
(7)Create FKey 0/0
Server: Msg 4902, Level 16, State 1, Line 1
Cannot find the object "dbo.ScriptTest2" because it does not exist or you do not have permissions.
(8)Rollback 0/0
ROLLBACK transaction
Server: Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
(9)Display data in ScriptTest2
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.ScriptTest2'.