5/20/2024 10:31:42 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
SQL 2000 - TRANSACTIONS and LOCKS-- HELP !
Posted: 18 Dec 07 9:07 AM
|
I have created a sp to update aprox 50 tables on 2 different DB. I put all update statements in a transaction, to rollback tran if any error is raised.
The stored procedure works fine in QueryAnalyzer.
I am calling the same sp within .NET 2.0 WinApp (using the same credentials as in QA) and my sp fails with the following message 'String or binary data would be truncated. The statement has been terminated.'
I followed the sp execution through SQL Profiler... i see a lots of locks acquired but none released ... and then i get the error. I cannot see COMMINT OR ROLLBACK being executed. It seems it stops in the middle of the sp.
Does numbers of lock plays any role into this? -- this is just a wild guess ??
I have no idea how to debug/fix this. Can anyone help me with this?
The logic in my usp is:
CREATE PROCEDURE usp_ADM_ChangeCustID
@OldCustID varchar(10),
@NewCustID varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int
DECLARE @ErrorDesc varchar(250)
SET @ErrorCode = 0
SET @ErrorDesc = 'Success'
BEGIN TRAN
UPDATE AAAAAA SET externalaccountno = @NewCustID WHERE externalaccountno = @OldCustID
SET @err = @@ERROR
IF @err <> 0
BEGIN
RAISERROR('Error while updating table AAA field aaa',16,1)
GOTO HANDLE_ERROR
END
..... many more tables here .............
UPDATE ZZZZZ SET customer_id = @NewCustID, date_last_updt = @UpdateDate WHERE customer_id = @OldCustID
SET @err = @@ERROR
IF @err <> 0
BEGIN
RAISERROR('Error while updating table ZZZZ field zzz,16,1)
GOTO HANDLE_ERROR
END
IF @err = 0
COMMIT TRAN
HANDLE_ERROR:
IF @err <> 0
BEGIN
ROLLBACK TRAN
SET @ErrorCode = @err
SET @ErrorDesc = (SELECT Description from master..sysmessages
WHERE msgLangID = 1033
AND error = @err)
END
SELECT
@ErrorCode AS ErrorCode,
@ErrorDesc AS ErrorDesc
SET NOCOUNT OFF
END
|
|
|
| |
|
Re: SQL 2000 - TRANSACTIONS and LOCKS-- HELP !
Posted: 19 Dec 07 8:22 AM
|
Just so everyone else knows, we figured out the problem. There were triggers on the underlying table. When you run a group of updates through ADO we ran into issues. We needed to add these statements to the top and bottom of the sp to make it work properly:
ALTER TABLE DEMOESI..sofcm DISABLE TRIGGER all
ALTER TABLE DEMOESI..sofcm ENABLE TRIGGER all
Also, just so I don't get "called" on this, we are not going to use the sp to do the updates on the SLX tables. Those will be handled through the OLEDB provider to allow for sync traffic.
Ted |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|