I am not a fan of triggers and dynamic sql, however, what I am working with requires both.
CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DatabaseName NVARCHAR(100)
DECLARE @FormItemID INT
DECLARE db_cursor CURSOR FOR
SELECT SourceID,FormItemID from Inserted
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC spAddEditFormItemView @FormItemID, @DatabaseName WITH RESULT SETS NONE
END TRY
BEGIN CATCH
INSERT INTO AdminErrorLog(SourceID, ErrorNumber, ErrorState, ErrorSeverity, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDateTime)
SELECT @DatabaseName, ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE()
END CATCH
FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
Here is what is happening.
- An AWS DMS Replication Task is replicating data in batches of 10,000. The implementation of the replication is a black box but there appears to be nested transactions on the connections.
- EXEC spAddEditFormItemView calls a stored procedure that exec's dynamic sql and errors out on one record.
- The CATCH block is never executed
- The error never makes it to AdminErrorLog.
- The AWS Batch errors our and is never committed and the whole task fails.
- In extended events the following sql error is caught. This is the error that failed the task. I read about conditions where commands can neither be in a commit or rollback state but truly did not fully understand that concept.
message: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
severity: 16
sql_text: (@P1 int,@P2 varchar(250),@P3 int,@P4 int,@P5 int,@P6 int,@P7 varchar(500),@P8 varchar(1000),@P9 bit,@P10 varchar(50),@P11 varchar(50),@P12 varchar(50),@P13 varchar(50),@P14 int,@P15 varchar(5000),@P16 bit,@P17 int,@P18 datetime2,@P19 int,@P20 datetime2,@P21 varchar(200),@P22 int,@P23 varchar(50),@P24 varchar(50),@P25 varchar(500),@P26 int,@P27 int,@P28 varchar(200),@P29 varchar(2000),@P30 int,@P31 int,@P32 int,@P33 varchar(4000),@P34 varchar(200),@P35 bit,@P36 varchar(300),@P37 bit,@P38 varchar(50),@P39 varchar(50),@P40 varchar(100))INSERT INTO [dbo].tblFormItems values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40)
Can anyone explain why the catch block is not being caught and why the client application is getting, what my guess is, a sql exception and rolling everything back. My guess is that dynamic sql exceptions are being handled differently and the implicit trigger transaction is rolling back instead of catching the error. Also, does anyone know of a way to avoid the exception from being propagated?
I bet the safest solution would be modify the trigger to cram the sql commands into a table and then have a sql agent job look for commands to run every minute or so.
EDIT - Adding Dynamic SQL:
I am just porting this to a cloud database. Please understand that this was not written by me and has been around for a long time now.
CREATE PROCEDURE [dbo].[spAddEditFormItemView] (
@FormItemID int,
@Schema nvarchar(100)
)
AS
DECLARE @Existing bit
, @FormItemFriendlyName varchar(200)
, @FormItemTypeID int
, @ObjectType varchar(100)
, @ObjectTypeID int
, @DataTypeID int
, @FormItemSystemName varchar(200)
, @SystemName varchar(200)
, @SourceType varchar(50)
, @TableName varchar(100)
, @DataColumn varchar(50)
, @DropdownSQL varchar(2000)
, @ObjectTableName varchar(100)
, @ObjectKeyField varchar(100)
, @FieldValue varchar(50)
SELECT @Existing = isnull(Existing, 0)
, @FormItemFriendlyName = FormItemFriendlyName
, @FormItemSystemName = FormItemSystemName
, @FormItemTypeID = FormItemTypeID
, @DataTypeID = DataTypeID
, @ObjectTypeID = ObjectTypeID
, @FormItemTypeID = FormItemTypeID
, @SourceType = SourceType
, @DropdownSQL = FormItemDropdownSource
FROM tblFormItems
WHERE FormItemID = @FormItemID
IF @ObjectTypeID = 4
BEGIN
SET @ObjectTableName = 'tblLeaseAbstract'
SET @ObjectKeyField = 'LeaseAbstractID'
END
ELSE
BEGIN
SELECT @ObjectTableName = TableName
, @ObjectKeyField = KeyField
FROM tblObjectType
WHERE ObjectTypeID = @ObjectTypeID
END
SELECT @DataColumn = Case when @DataTypeID IN ( 6, 5, 206 )
then 'FormDataFloat'
when @DataTypeID = 7 then 'FormDataDate'
when @DataTypeID = 200 then 'FormDataText'
when @DataTypeID = 201
then 'FormDataVarChar400'
when @DataTypeID = 202 then 'FormDataVarChar50'
when @DataTypeID IN ( 2, 3 ) then 'FormDataInt'
else 'FormDataText'
end
SELECT @TableName = DynamicFormDataTable
FROM tblObjectType
WHERE ObjectTypeID = @ObjectTypeID
--create view if it is not there...must know objectType and formItemType first
if (@Existing = 0
and @FormItemFriendlyName <> ''
and @FormItemTypeID <> 8 --and @FormItemTypeID <> 9 --Calculated=9
)
/*Modified 2/17/2011 to create form item views for existing listbox form items*/
OR (@FormItemTypeID =1 AND @SourceType = 'sql')
BEGIN
select @ObjectType = ObjectType
from tblObjectType
where objectTypeID = @ObjectTypeID
select @SystemName = REPLACE(@FormItemSystemName, ' ', '')
select @FormItemSystemName = 'vwFormItem_'
+ cast(@ObjectTypeID as varchar) + '_'
+ REPLACE(@FormItemSystemName, ' ', '')
if not ( exists ( select *
from dbo.sysobjects
where id = object_id(@FormItemSystemName)
and OBJECTPROPERTY(id, 'IsView') = 1 ) )
BEGIN
IF ( ( @FormItemTypeID = 1
OR @FormItemTypeID = 5
)
and @SourceType = 'tbldropdown'
)
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.' + @FormItemSystemName + '
AS
SELECT ObjectID
, tblDropdownValue.Dropdownvalue as ['
+ @FormItemFriendlyName + ']
, tblDropdownValue.DropdownDisplay as ['
+ @FormItemFriendlyName + 'Display]
FROM tblDropdownValue
inner join ' + @TableName
+ ' fd on fd.DropdownValueID = tblDropdownValue.DropdownValueID
WHERE FormItemID = ' + @FormItemID
)
END
ELSE
IF ( @FormItemTypeID = 1
AND @SourceType = 'sql'
AND ( @DropdownSQL LIKE 'SELECT DropdownValueID%'
OR @DropdownSQL LIKE 'SELECT tblDropdownValue.DropdownValueID%'
)
)
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.' + @FormItemSystemName
+ '
AS
SELECT ObjectID
, tblDropdownValue.DropdownDisplay AS ['
+ @FormItemFriendlyName + ']
, tblDropdownValue.DropdownValue AS ['
+ @FormItemFriendlyName + 'Value]
FROM ' + @TableName
+ ' LEFT JOIN tblDropdownValue ON tblDropdownValue.DropdownValueID = '
+ @TableName + '.' + @DataColumn
+ ' WHERE FormItemID = ' + @FormItemID
)
END
ELSE
IF ( @FormItemTypeID = 18 )
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.'
+ @FormItemSystemName + '
AS
SELECT
ObjectID
, dbo.udfGetMultiSelectAsTextValues(' + @DataColumn
+ ')as [' + @SystemName + ']
, ' + @DataColumn + ' as [' + @SystemName + 'Values]
FROM ' + @TableName + '
WHERE FormItemID = ' + @FormItemID
)
END
ELSE
IF ( @FormItemTypeID = 10 )
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.'
+ @FormItemSystemName + '
as
SELECT ObjectID,
CONVERT(varchar(8000),Clause) as [' + @SystemName
+ '],
SourceDocument as [' + @FormItemFriendlyName + ' Doc],
SourceSection as [' + @FormItemFriendlyName + ' Sec],
SourcePage as [' + @FormItemFriendlyName + ' Page]
FROM tblClauses
WHERE FormItemID = ' + @FormItemID
)
END
ELSE
IF ( @FormItemTypeID = 9 )
BEGIN
IF @DataColumn IN ( 'FormDataText', 'FormDataVarChar50', 'FormDataVarChar400' )
BEGIN
SET @FieldValue = '''Calculation - Update Form Item View'''
END
ELSE
IF @DataColumn IN (
'FormDataFloat',
'FormDataInt' )
BEGIN
SET @FieldValue = '99999999'
END
ELSE
BEGIN
SET @FieldValue = '''1/1/1900'''
END
EXEC
( 'CREATE VIEW '+@Schema+'.'
+ @FormItemSystemName + '
AS
SELECT ' + @ObjectKeyField + ' AS ObjectID ' + ', '
+ @FieldValue + ' AS ['
+ @SystemName + ']
FROM ' + @ObjectTableName
)
END
ELSE
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.'
+ @FormItemSystemName + '
AS
SELECT
ObjectID
, ' + @DataColumn + ' as [' + @SystemName + ']
FROM ' + @TableName + '
WHERE FormItemID = ' + @FormItemID
)
END
END
END
EDIT: I can now duplicate in SSMS.
