Looking for ideas - 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 2000 Forums

Transact-SQL (2000)

Looking for ideas

Author

Topic

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-03-30 : 14:22:44

I have a proc that inserts data from another database (source) and updates the source db with the identity.

I've noticed that occasionally the update hasn't updated the source table even though the inserts before and after the update have run. What kinds of things am I missing?

The proc looks like this:

Create Proc usp_Proc1
@TableID int
As

Set NoCount On

Begin Tran
Insert Into DB1.dbo.Table1 (fields)
Select (fields)
From DB2.dbo.TableA
Where TableID = @TableID

Set @Chk = @@Error, @Value = Scope_Identity()

Insert Into DB2.dbo.History (id, fields)
Values (@Value, values)

Set @Chk = @Chk + @@Error

Update DB2.dbo.TableA
Set Field1 = @Value
, Field2 = GetDate()
Where TableID = @TableID

Set @Chk = @Chk + @@Error

...More Inserts to DB1.dbo.(Various Tables)
...Each Insert has a "Set @Chk = @Chk + @@Error following it

If @Chk = 0
Begin
Commit Tran
..
End
Else
Begin
Rollback Tran
..notify
End

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-30 : 16:39:18

>> Set @Chk = @@Error, @Value = Scope_Identity()

is that valid syntax?

- Jeff
http://weblogs.sqlteam.com/JeffS