Hi

Errors thrown from T-SQL procedures used to result in easy to understand messages, but since the last few versions the messages have been pretty ugly. Here is an example of what I mean:

1) Create a simple procedure

CREATE PROCEDURE p_test
AS
DECLARE @err AS VARCHAR(100);
BEGIN
SET @err = 'test';

THROW 51000
,@err
,1;
END;

2) Create a job that calls this procedure. The Output(error) produces this:

Exception in Task: System.Data.SqlClient.SqlException (0x80131904): test
at OBOKIIHBDBPMCFALPGECCIFHCDJPEDKODDGK.NFMCAMDOGBCBCKKAEOJICMPGMOLBJPKAAHOI.BPNKNPGIMAAAJCHDLKLMKPOLKDLBPDDAPBLF(TaskProcessClass , Boolean& , ConnectionClass , Boolean , String , Int32 , CommandType , Boolean , String , List`1 lstParameters, StringBuilder& , StringBuilder& , VariableReplacerClass , JobClass , TaskClass , TriggerClass , SqlCommand& , TaskSQLOutputSettingsClass ) in C:\sourcefiles\code\VisualCronService\apiSQL.vb:line 1142
at OBOKIIHBDBPMCFALPGECCIFHCDJPEDKODDGK.GKHJKBDAJKKLOCDAGMDEFBPILHJMKGOIPBBJ.Run() in C:\sourcefiles\code\VisualCronService\Jobs\TaskProcesses\Database\clsProcessTaskSQL.vb:line 74
ClientConnectionId:7eb83484-e63d-43b4-a86a-1ecbd464d5ad

Question: Is there any way of getting only the first bit (exception type and message)? ie SqlException and 'test'. I know I can get it by string parsing, but there must be a more elegant solution. As far as I can tell, we don't have any debugging switched on.

Thomas

We cannot seem to compile that procedure - we get errors. Could you please give us the correct one for SQL server

---

Hi

The procedure I posted is correct, but will compile only in Sql Server 2012 or above. You are probably testing on an older version. Try with RAISERROR (instead of THROW) :

CREATE PROCEDURE p_test
AS
DECLARE @err AS VARCHAR(100);
BEGIN
SET @err = 'test';

RAISERROR (@err , -- Message text.
16, -- Severity.
1 -- State.
);

END;
GO


Thanks for looking into this. I get the following output error:

Exception in Task: System.Data.SqlClient.SqlException (0x80131904): test
at OBOKIIHBDBPMCFALPGECCIFHCDJPEDKODDGK.NFMCAMDOGBCBCKKAEOJICMPGMOLBJPKAAHOI.BPNKNPGIMAAAJCHDLKLMKPOLKDLBPDDAPBLF(TaskProcessClass , Boolean& , ConnectionClass , Boolean , String , Int32 , CommandType , Boolean , String , List`1 lstParameters, StringBuilder& , StringBuilder& , VariableReplacerClass , JobClass , TaskClass , TriggerClass , SqlCommand& , TaskSQLOutputSettingsClass ) in C:\sourcefiles\code\VisualCronService\apiSQL.vb:line 1142
at OBOKIIHBDBPMCFALPGECCIFHCDJPEDKODDGK.GKHJKBDAJKKLOCDAGMDEFBPILHJMKGOIPBBJ.Run() in C:\sourcefiles\code\VisualCronService\Jobs\TaskProcesses\Database\clsProcessTaskSQL.vb:line 74
ClientConnectionId:82d1a460-7d8c-4259-b372-24c61dbd24a7

Thanks, we have now added this data to the error output:

http://www.visualcron.co....aspx?g=posts&t=4656 

However, we still include some other error data. To remove that we need do some other changes which might come in upcoming versions.

---