Returning a long error message from ODBC engine

[ScottBass]

I'm creating a Stored Procedure in SQL Server.  Here is some skeleton code:

-- =============================================
-- Author:      Scott Bass
-- Create date: 30AUG2019
-- Description:	Create New Project in Operational Table
-- =============================================
ALTER PROCEDURE spNewProject 
     @ProjectNum INT = 0
    ,@ProjectName VARCHAR(64) = ''
    ,@ProjectDesc VARCHAR(256) = ''
    ,@ProjectSpon VARCHAR(50) = ''
    ,@FileTrimNumber VARCHAR(20) = ''
    ,@Department CHAR(3) = ''
    ,@Help BIT = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Print help if requested
    DECLARE @helptext VARCHAR(4000) = '
EXEC spNewProject @ProjectNum =         [<existing ProjectNum> | -1 ]
                 ,@Department =         [''EBU'' | ''DIU'']
                 ,@ProjectName =        [<Project Name>]
                 ,@ProjectDescription = [<Project Description>]
                 ,@ProjectSponsor =     [<Project Sponsor>]
                 ,@FileTrimNumber =     [<FileTrimNumber>]
                 ,@Help =               [0 | 1]

ProjectNum: 
Specify an existing ProjectNum to create a new Project row for an existing project.
For example, if the ProjectSponsor changes.

Department:
Enter ''EBU'' if the Project is owned by EBU (for example APEDDR)
Enter ''DIU'' if the Project is owned by DIU (for example Data To Investigators)

ProjectName:
If possible, enter a short mnemonic that uniquely identifies the Project.
For example, MLK, APEDDR, MCHR, INJDEM, THROMBO, CDMP, CRS, C.H.E.S.T, etc.
A short mnemonic allows you to easily search for the ProjectName later.
There must be a one-to-one match between the ProjectNum and the ProjectName.

ProjectSponsor:
The ProjectSponsor is the surname of the "owner" of the project.
For example, FUBAR for MLK and APEDDR, or the investigator''s surname.
The ProjectSponsor will be uppercased when saved to the Project table.
Enter ''XXX'' if the ProjectSponsor is unknown, 
   but update the ProjectSponsor once it is known.

FileTrimNumber:
A Project should be supported by a TRIM File Number.
You should create a TRIM File Number, even if it is a dummy (barebones) TRIM entry,
   before creating a new Project in the Project table.
Enter ''XX/XXXX'' if you are unable to create a dummy TRIM File Number,
   but update the FileTrimNumber once the TRIM File Number has been created.
'

    IF @Help = 1
    BEGIN
        RAISERROR(
            '%s'
            ,-1
            ,-1
            ,@helptext
        )
        RETURN
    END

    -- Check for required parameters
    IF @ProjectNum = 0
    BEGIN
        RAISERROR(
            '%s is a required parameter.  Enter either an existing ProjectNum or -1 to automatically create a new ProjectNum.'
            ,16
            ,-1
            ,'ProjectNum'
        )
        RETURN
    END

    IF @ProjectName = ''
    BEGIN
        RAISERROR(
            '%s is a required parameter.'
            ,16
            ,-1
            ,'ProjectName'
        )
        PRINT @helptext
        RETURN
    END

    IF @ProjectDesc = ''
    BEGIN
        RAISERROR(
            '%s is a required parameter.'
            ,16
            ,-1
            ,'ProjectDesc'
        )
        PRINT @helptext
        RETURN
    END

    IF @ProjectSpon = ''
    BEGIN
        RAISERROR(
            '%s is a required parameter.'
            ,16
            ,-1
            ,'ProjectSpon'
        )
        PRINT @helptext
        RETURN
    END

    IF @FileTrimNumber = ''
    BEGIN
        RAISERROR(
            '%s is a required parameter.'
            ,16
            ,-1
            ,'FileTrimNumber'
        )
        PRINT @helptext
        RETURN
    END

    IF @Department = ''
    BEGIN
        RAISERROR(
            '%s is a required parameter.  Valid values are ''EBU'' or ''DIU''.'
            ,16
            ,-1
            ,'Department'
        )
        PRINT @helptext
        RETURN
    END

    -- Minimal checking for invalid parameters
    IF @Department NOT IN ('EBU','DIU')
    BEGIN
        RAISERROR(
            'Invalid value for %s.  Valid values are ''EBU'' or ''DIU''.'
            ,16
            ,-1
            ,'Department'
        )
        PRINT @helptext
        RETURN
    END

END
GO

If I execute this in SQL Server Management Studio, things work as expected:

EXEC spNewProject @Help =1
EXEC spNewProject @ProjectNum = 0

The first invocation prints the Help text to the Messages window and does not raise an error since Error Level = -1.

The second invocation prints the Error message to the Messages window and raises an error since Error Level = 16.

My end users will be calling this stored procedure from Enterprise Guide:

proc sql;
   connect using rlcs;
   execute by    rlcs (
EXEC spNewProject @Help=1
   );
quit;

proc sql;
   connect using rlcs;
   execute by    rlcs (
EXEC spNewProject
   );
quit;

Log from the first invocation:

27         proc sql;
28            connect using rlcs;
ODBC: AUTOCOMMIT is YES for connection 2
29            execute by    rlcs (
30         EXEC spNewProject @Help=1
31            );
 
ODBC_16: Executed: on connection 2
EXEC spNewProject @Help=1
 
WARNING: During execute: Unable to retrieve error message.
32         quit;

Log from the second invocation:

27         proc sql;
28            connect using rlcs;
ODBC: AUTOCOMMIT is YES for connection 2
29            execute by    rlcs (
30         EXEC spNewProject
31            );
 
ODBC_17: Executed: on connection 2
EXEC spNewProject
 
ERROR: CLI execute error: [Microsoft][SQL Server Native Client 10.0][SQL Server]ProjectNum is a required parameter.  
       Enter either an existing ProjectNum or -1 to automatically create a new ProjectNum.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
32         quit;

I suspect either:

1) The ODBC standard dictates the length of text that can be returned in an error message (just a guess), or

2) SAS R&D didn't conceive of a very long error message and the buffer for the returned error message is relatively short (another guess).

It also looks like all message formatting, such as newlines, spacing, etc. are stripped from the returned error message anyway (unlike the Messages window in SSMS).

Can anyone comment on my guesses?  Is the maximum length of an error message returned by the ODBC engine documented somewhere (I couldn't find it from a brief skim of the SAS/Access to ODBC documentation, but I didn't search too hard before posting.)

Finally, any brilliant ideas on how to return a help message to the end user's log? 

I'm thinking I either bail on the @Help parameter, or keep it for use in SSMS, but either way create a Note entry in EG documenting the usage for the stored procedure (i.e. the Help text). 

[ScottBass]

Well if I change the code to:

    IF @Help = 1
    BEGIN
        RAISERROR(
            '%s'
            ,-1
            ,-1
            ,@helptext
        )
        BEGIN
            SELECT @helptext AS HelpText
        END
        RETURN
    END

And change the invocation from EG as:

proc sql;
   connect using rlcs;
   select * 
   from connection to rlcs (
EXEC spNewProject @Help=1
   );
quit;

it's a bit better.  (Thinking to myself) Perhaps a Note entry in EG is the way to go.  Still would like answers to my OP.

[Shmuel]

I have never worked in such environment, so it is just a guess:

When you connect to ODBC, do you have to disconnect at end of process? 

Pay attention to the bold-underlined part of the message:

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 10.0][SQL Server]
ProjectNum is a required parameter.
Enter either an existing ProjectNum or -1 to automatically create a new ProjectNum.

As you can have more than one connected process, you need to define which of them to select.

[BradleyShaw]

Hey Scott,

Hope you are well.

Any updates on the below?

I am now having the precise issue in SAS wanting to pass feedback from SQL SP's to SAS via ODBC (ODBC Driver 17 for SQL Server) and receiving the dreaded warning.

Cheers,

Brad

[ScottBass]

Hi Brad,

I was hoping someone from SAS might chime in on this issue but alas...

I never got a fix, workaround, or explanation for this issue.

One kludge is to send a message to the output window via select.

The SAS invocation would look something like this:

proc sql;
   connect using foo;  * where foo is an ODBC libref ;
   select *
   from connection to foo (
EXEC dbo.spMyStoredProcedure
   );
quit;

And in your stored procedure:

IF @SOME_ERROR THEN
BEGIN
SELECT 'line 1' AS message
UNION 
SELECT 'line 2'
UNION 
SELECT 'line 3'
END

You can't return multiple selects, so your code needs to create one select result per logic branch.

I use this technique in some of my stored procedures.  My error processing looks like:

    BEGIN CATCH
        IF (@@trancount > 0)
        BEGIN
            ROLLBACK TRANSACTION;
            PRINT 'Transaction failed, all changes reversed.';  /* This only prints in SSMS, not SAS */
        END
        SELECT ERROR_NUMBER()    AS ErrorNumber  /* But this prints to the SAS output window in EG */
              ,ERROR_SEVERITY()  AS ErrorSeverity
              ,ERROR_STATE()     AS ErrorState
              ,ERROR_PROCEDURE() AS ErrorProcedure
              ,ERROR_LINE()      AS ErrorLine
              ,ERROR_MESSAGE()   AS ErrorMessage
        ;

        SET @RC = -1;
        RETURN @RC;
    END CATCH

HTH...