I have embedded Dynamics SQL update commands in my application. One such task is responsible for updating 28 tables that contain a field containing the customer number field. It has come to my attention that for 3 of the tables the update is not successful even though the status code that comes back indicates success. The unique characteristic to these three tables is that the customer number field is the primary key. How can I best trace this to attempt to determine why my update is failing or perhaps if my update succeeds but then is rolled back? This is MS SQL Server 2005.
CREATE PROCEDURE [dbo].[rbsUpdateSTAXINFO]
@Return_code int output
declare @sql nvarchar(4000)
SET @Return_Code = 1
/* If old customer ID is found, update it to the new customer ID */
IF EXISTS (SELECT * FROM STAXINFO WHERE rtrim(CUSTOMER_NUMBER_STXI)=rtrim(@Old_CustomerID))
SET @sql = ' update STAXINFO ' +
' set CUSTOMER_NUMBER_STXI = ' + @New_CustomerID +
' where CUSTOMER_NUMBER_STXI= @Old_CustomerID'
exec sp_executesql @sql, N'@Old_CustomerID varchar(15)',@Old_CustomerID