I am wrting a script that inserts data into a table based data I retrieve
using a cursor.  I want to setup the entire query in a transaction.  How do
I setup a transaction?  How do I trap errors to rollback?

Hi there,

Just try that:

BEGIN TRANSACTION

UPDATE Employees SET FirstName = 'Jens' and LastName = 'Suessmeyer'

IF @@ERROR = 0
COMMIT
ELSE ROLLBACK

HTH, Jens SUessmeyer.

---
http://www.sqlserver2005.de
---

Show quoteHide quote

Here is a snippent of SQL

--Script is used to convert R2 Run Defs to R3 RunDef
print 'Declaring Variables'
DECLARE @EntityID int
DECLARE @SubSectionID int
DECLARE @EntityName varchar(200)
DECLARE @VariableEntityID int
Declare @SQLString varchar(5000)
Declare @Debugging int
Declare @CurrentDate DateTime


set @Debugging = 1
print 'Building Cursors'
DECLARE curEntity CURSOR FOR
SELECT EntityID, EntityName , SubSectionID FROM  Entity Where SubSectionID
in(200004,700000 ,800000 ,860001 ,861100 ,1000302 ,1000306 , 1000356)
ORDER BY SubSectionID, EntityID

OPEN curEntity
print 'Fetching Entities'
FETCH NEXT FROM curEntity
INTO @EntityID, @EntityName, @SubSectionID

WHILE @@FETCH_STATUS = 0
BEGIN

print 'Processing Entity: ' + Cast(@EntityName as varchar(255))
--Loop through entities to see if Value already exists
--if value does not then add value
IF NOT EXISTS (select * from variableentity where variableid = 800002 and
entityid = @EntityID)
BEGIN
  print 'Insert Data'
END
FETCH NEXT FROM curEntity
INTO @EntityID, @EntityName, @SubSectionID
END
CLOSE curEntity
DEALLOCATE curEntity

Show quoteHide quote

Do something like this. Then you don't need a cursor or an explicit
transaction:

INSERT INTO variableentity (entityid, entityname, subsectionid)
SELECT entityid, entityname, subsectionid
  FROM Entity
  WHERE subsectionid
   IN (200004, 700000 ,800000 ,860001,
       861100, 1000302, 1000306, 1000356)
   AND NOT EXISTS
    (SELECT *
     FROM variableentity
     WHERE variableid = 800002
      AND entityid = Entity.entityid)

Certainly best to avoid using a cursor in a transaction. Cursors are rarely
a good idea anyway. Post DDL, sample data and expected results if you want
help to develop a set-based solution instead.

To trap errors, either SET XACT_ABORT ON or IF @@ERROR > 0.

ntext and update/insert triggers
Error converting data type nvarchar to int.
move records from table1 to table2 if does not exist in table3
Query Help
FIFO report