Instead of logging your errors in a physical table (whose changes are being rolled back, I assume), log them in a table variable and then insert from the table...

• This reply was modified 9 months, 4 weeks ago by water490.

I swapped things around a bit. Obviously, I cannot test it.

ALTER PROCEDURE dbo.RunStoredProcedures
AS
SET NOCOUNT ON;

DECLARE @FAIL_COUNT INT = 0;

BEGIN TRANSACTION;

BEGIN TRY
    BEGIN TRY
...

I made some progress on this.

Code:

ALTER procedure [dbo].[RunStoredProcedures]
as

DECLARE @FAIL_COUNT INT = 0

BEGIN TRANSACTION

BEGIN TRY
BEGIN TRY
  truncate table dbo.temptable
END TRY
BEGIN CATCH
SET @FAIL_COUNT = @FAIL_COUNT + 1
END CATCH

BEGIN...

I have been thinking more about this.  I don't think relying on SSIS output window is the right way to capture errors.  I need a persistent record of errors (not...

• This reply was modified 9 months, 4 weeks ago by water490.
• This reply was modified 9 months, 4 weeks ago by water490.
• This reply was modified 9 months, 4 weeks ago by water490.
• This reply was modified 9 months, 4 weeks ago by water490.

deleted reply

• This reply was modified 9 months, 4 weeks ago by water490.

Thank you!

I have some questions:

1. re the output parameter option solution.  Will that message show up in the output window?

2. re the alternative solution.  I am getting an error message...

Actually...the above two outputs are not that important.  The SP is run in SSIS so I never see the above two issues.  Ok for now.

The more pressing issue...

Actually...the above two outputs are not that important.  The SP is run in SSIS so I never see the above two issues.  Ok for now.

The more pressing issue is that...

Thanks for the reply.  That is the entire code.  Not sure where/how it got -6.  Any ideas on how it got there?

I made some progress.  New code:

ALTER procedure [dbo].[RunStoredProcedures]
as

BEGIN TRANSACTION

BEGIN TRY
truncate table dbo.testtable
select 1/0
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR('Rollback of stored procedures was successful', 10, 1) WITH...

what happens if you move that SP to another container, being executed after the other ones?

and most times its some settings on your sp's or locking between SP's -...

You can check the encryption status of your databases:

SELECT 
db.name AS database_name, 
db.is_encrypted, 
dm.encryption_state, 
dm.percent_complete, 
dm.key_algorithm, 
dm.key_length 
FROM 
sys.databases db 
LEFT OUTER JOIN 
sys.dm_database_encryption_keys dm 
ON...

Without further information, there is no way one can answer your question! 😎 1. Are you using any hard drive encryption? 2. Are you using TDE, transparent data encryption...

The query is this one:

INSERT INTO DBO.RunTimeSSIS
VALUES ('Start SSIS', getdate())

I ran this in SSMS to see if there are any issues.  No issues at all.  It ran...

..