SQL Server Stored Procedure

Thanks. I suspect this will be the issue. I am using SQLSVR 2016 and I may have misunderstood the doco. I will check abd let you know.

Thanks again

Hi, Just a recap and clarification for anyone seeing this.

The RAISERROR T-SQL clause mentioned by Maria is dependent on a MSG-ID. in this case 15600 which can be found in the
sys.messages view and when translated results in the Message Text of "An invalid parameter or option was specified for
procedure". The "-1" indicates that RAISERROR should use the severity level from sys.messages which in this case is 15.
Which still results in AETL indicating failed status.I found that a Severity <= 10 will result in a SUCCESS response to
AETL and any >10 will result in a FAILED response to AETL.There seems to be some debate on what severity level
should be used and if they should terminate.

In our case, the error is probably significant enough for the process to terminate. Hence, unless proven otherwise I will stick
to a basic format of

Following on from this thread I have found an odd behaviour. Most of the tests in this thread were done using the AETL Run SQL tab and simply
calling a stored procedure (and getting the expected results).

However, I have found a scenario where the results always return positive and the errors thrown seem to be absorbed somewhere.

I have a basic stored procedure with a TRY/CATCH block that I have reduced down to the necessary elements for this thread.



If I execute this from SSMS it runs as expected and an error is thrown back to the caller. NOTE that there is an INSERT statement just before
the THROW. This is so that the error can be logged. In reality the true script would be updating a table row indicating that an error occur whilst
actioning ETL for the row.

All Good...



However, I found that AETL was not showing the error even though it is in the USER Range (50000+) and more importantly has a severity between 11-17
This one is 16. Therefore, SQL Server considers this as a fatality and AETL should indicate the call has failed. But as you can see it considers it to be Ok.



IF I REMOVE THE INSERT Statement before the THROW. It fails as expected AND AETL senses that it has failed. Any Ideas?
I have included the sample SQL Script.

can you try putting set nocount on at the beginning of the stored procedure?

Also please post the source code as text here

Thanks. I happened to find the same thing during my research. I will attempt this over the weekend.
Thanks

The stored proc souce was in a .txt attachment
Cheers