Return a message from a SQL Server stored procedure to the SAS log

[ScottBass]

I have this stored procedure:

CREATE OR ALTER PROCEDURE dbo.spGetMaxRldxAuditKey
(
	@reference_rldx_audit_key INT
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @max_rldx_audit_key INT

    -- get max rldx_audit_key
    SET @max_rldx_audit_key = (
        SELECT MAX(rldx_audit_key) AS max_rldx_audit_key
          FROM (
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PUB_FLAT
              UNION ALL
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PVT_FLAT
              UNION ALL
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PHI_FLAT
          ) x
    )
          
    -- if the reference rldx_audit_key = max rldx_audit_key raise a message
    -- else raise an error
    IF @reference_rldx_audit_key = @max_rldx_audit_key
    BEGIN
        RAISERROR('Reference rldx_audit_key matches max rldx_audit_key',0,1)
    END
    ELSE
    BEGIN
        RAISERROR('Reference rldx_audit_key does not match max rldx_audit_key',16,1)
    END 
END
GO

This works just fine in SQL Server Management Studio:

EXEC dbo.spGetMaxRldxAuditKey 51  -- valid
EXEC dbo.spGetMaxRldxAuditKey 49  -- invalid

The first invocation displays a message in the Messages window, and the Query executed successfully.

The second invocation displays a red message in the Messages window, and the Query completed with errors.

The business logic is the @reference_rldx_audit_key is derived in SAS from reading a header file.  The source data must match the key in the header file.  There is a match if the reference_rldx_audit_key matches the maximum rldx_audit_key across the three source tables.  The rldx_audit_key has the same value within a given source table, so SELECT TOP 1 is used for performance.

However, when I invoke this in SAS:

* test the stored procedure ;

%let syscc=0;

* valid ;
%let header_rldx_audit_key=51;

proc sql noprint;
   connect using rldx;
   execute by    rldx (
EXEC dbo.spGetMaxRldxAuditKey &header_rldx_audit_key
   );
   %put &=sqlxrc;
   %put &=sqlxmsg;
quit;

%put &=syscc;

* invalid ;
%let header_rldx_audit_key=49;

proc sql noprint;
   connect using rldx;
   execute by    rldx (
EXEC dbo.spGetMaxRldxAuditKey &header_rldx_audit_key
   );
   %put &=sqlxrc;
   %put &=sqlxmsg;
quit;

%put &=syscc;

I get this in the SAS log:

27         %let syscc=0;
28         
29         * valid ;
30         %let header_rldx_audit_key=51;
31         
32         proc sql noprint;
33            connect using rldx;
ODBC: AUTOCOMMIT is YES for connection 1
34            execute by    rldx (
35         EXEC dbo.spGetMaxRldxAuditKey &header_rldx_audit_key
36            );
 
ODBC_17: Executed: on connection 1
EXEC dbo.spGetMaxRldxAuditKey 51
 
WARNING: During execute: [Microsoft][SQL Server Native Client 10.0][SQL Server]Reference rldx_audit_key matches max 
         rldx_audit_key
37            %put &=sqlxrc;
SQLXRC=01000
38            %put &=sqlxmsg;
2 The SAS System                                                                        10:53 Friday, September 13, 2019

SQLXMSG=[Microsoft][SQL Server Native Client 10.0][SQL Server]Reference rldx_audit_key matches max rldx_audit_key
39         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              102.78k
      OS Memory           12836.00k
      Timestamp           13/09/2019 11:24:50 AM
      

40         
41         %put &=syscc;
SYSCC=4
42         
43         * invalid ;
44         %let header_rldx_audit_key=49;
45         
46         proc sql noprint;
47            connect using rldx;
ODBC: AUTOCOMMIT is YES for connection 1
48            execute by    rldx (
49         EXEC dbo.spGetMaxRldxAuditKey &header_rldx_audit_key
50            );
 
ODBC_18: Executed: on connection 1
EXEC dbo.spGetMaxRldxAuditKey 49
 
ERROR: CLI execute error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Reference rldx_audit_key does not match 
       max rldx_audit_key
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
51            %put &=sqlxrc;
SQLXRC=42000
52            %put &=sqlxmsg;
SQLXMSG=[Microsoft][SQL Server Native Client 10.0][SQL Server]Reference rldx_audit_key does not match max rldx_audit_key
53         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              101.87k
      OS Memory           12836.00k
      Timestamp           13/09/2019 11:24:50 AM
      
54         
55         %put &=syscc;
SYSCC=1012

My problem is with the first WARNING.  I need it to be either a NOTE, or simply a message returned by SQL Server and displayed in the SAS log.

But, I'm raising an error you say (even if it does have severity level = 0).  Ok, so I tried changing it to a PRINT statement for the valid scenario.  Then I tried simply PRINT ''.  No joy.  It's as though if I return ANY message via the stored procedure, SAS treats it as a warning (or error if I use RAISERROR with severity level=16).

Even this stored procedure generates a warning in SAS:

CREATE OR ALTER PROCEDURE dbo.spSasIsBuggy 
AS
BEGIN
    SET NOCOUNT ON
    PRINT 'SAS is buggy'
END
GO

proc sql noprint;
   connect using rldx;
   execute by    rldx (
EXEC dbo.spSasIsBuggy
   );
   %put &=sqlxrc;
   %put &=sqlxmsg;
quit;

27         proc sql noprint;
28            connect using rldx;
ODBC: AUTOCOMMIT is YES for connection 1
29            execute by    rldx (
30         EXEC dbo.spSasIsBuggy
31            );
 
ODBC_20: Executed: on connection 1
EXEC dbo.spSasIsBuggy
 
WARNING: During execute: [Microsoft][SQL Server Native Client 10.0][SQL Server]SAS is buggy
32            %put &=sqlxrc;
SQLXRC=01000
33            %put &=sqlxmsg;
SQLXMSG=[Microsoft][SQL Server Native Client 10.0][SQL Server]SAS is buggy
34         quit;

Does anyone know if this is:

1) a shortcoming in the SQL Server ODBC driver from Microsoft?  Or

2) a shortcoming/design bug in how SAS's ODBC engine interprets a message returned from the ODBC driver?

Finally, any ideas re: workarounds?  I'm aware of a few klunky approaches, but I would like to encapsulate the logic in the SP if possible, and just have SAS generate an error when an error occurs, and not a warning when no error occurs. 

I suppose I can just comment out the valid condition, and not return any confirmation message to the end user, but IMO that's not ideal.

[SASKiwi]

@ScottBass  - A couple of ideas:

• How about trying - select * from connection using rldx (exec MyProc) - does it return your error messages?
• How about creating a temporary table in SQL Server containing the error message / return code after executing the proc? I've used this myself and it looks like this - 

execute(

if object_id('tempdb..#MyReturnCode') is not null drop table #MyReturnCode;
create table #MyReturnCode (RetCode int);

EXEC MyProc

INSERT INTO #MyReturnCode values (@RetCode).

After doing this you can do a normal select connection to get the message into SAS 

[ScottBass]

Hi @SASKiwi,

I thought I might hear from you :).  I saw some of your previous answers on similar subjects before posting my question.

How about trying - select * from connection using rldx (exec MyProc) - does it return your error messages?

No since my RAISERROR messages aren't a result set, although the 2nd one does result in a (desired) error in SAS.

How about creating a temporary table in SQL Server containing the error message / return code after executing the proc? I've used this myself and it looks like this

What I'm running with right now is:

Version 1:

SQL Server Inline table-valued function (TVF) (this could easily be a view as well):

CREATE OR ALTER FUNCTION dbo.fnGetMaxRldxAuditKey ()
RETURNS TABLE
    RETURN (
        SELECT MAX(rldx_audit_key) AS max_rldx_audit_key
          FROM (
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PUB_FLAT
              UNION ALL
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PVT_FLAT
              UNION ALL
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PHI_FLAT
          ) x
    )
GO

SAS Invocation:

%let syscc=0;

%macro CheckRldxAuditKey(
   reference_rldx_audit_key
);
proc sql noprint;
   connect using rldx;
   select max_rldx_audit_key
   into :max_rldx_audit_key trimmed
   from connection to rldx (
SELECT max_rldx_audit_key FROM dbo.fnGetMaxRldxAuditKey()
   );
quit;

%if (&max_rldx_audit_key eq &reference_rldx_audit_key) %then %do;
   %put %str(NO)TE: max_rldx_audit_key matches header rldx_audit_key.;
%end;
%else %do;
   %put %str(ERR)OR: max_rldx_audit_key does not match header rldx_audit_key.;
   %let syscc=8;
   %abort cancel;
%end;
%mend;

* valid ;
%let header_rldx_audit_key=51;
%CheckRldxAuditKey(&header_rldx_audit_key)
%put &=syscc;

proc print data=sashelp.class (obs=1);
run;

* invalid ;
%let header_rldx_audit_key=49;
%CheckRldxAuditKey(&header_rldx_audit_key)

* does not execute due to %abort cancel ;
%put &=syscc;

proc print data=sashelp.class (obs=1);
run;

Version 2:

SQL Server Scalar-valued function:

CREATE OR ALTER FUNCTION dbo.fnCheckRldxAuditKey (
    @reference_rldx_audit_key INT
)
RETURNS BIT AS
BEGIN
    DECLARE @max_rldx_audit_key INT
    SET @max_rldx_audit_key = (
        SELECT MAX(rldx_audit_key) AS max_rldx_audit_key
          FROM (
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PUB_FLAT
              UNION ALL
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PVT_FLAT
              UNION ALL
              SELECT TOP 1 rldx_audit_key
                FROM dmt.PHI_FLAT
          ) x
    )
    RETURN IIF(@max_rldx_audit_key = @reference_rldx_audit_key,1,0)
END
GO

SAS Invocation:

%macro CheckRldxAuditKey(
   reference_rldx_audit_key
);
%local rc;
%let rc=0;  %* initialize to 0 in case SQL fails for some reason ;
proc sql noprint;
   connect using rldx;
   select *
   into :rc trimmed
   from connection to rldx (
SELECT dbo.fn_CheckRldxAuditKey(&reference_rldx_audit_key)
   );
quit;

%if (&rc eq 1) %then %do;
   %put %str(NO)TE: max_rldx_audit_key matches header rldx_audit_key.;
%end;
%else %do;
   %put %str(ERR)OR: max_rldx_audit_key does not match header rldx_audit_key.;
   %let syscc=8;
   %abort cancel;
%end;
%mend;

* valid ;
%let header_rldx_audit_key=51;
%CheckRldxAuditKey(&header_rldx_audit_key)
%put &=syscc;

proc print data=sashelp.class (obs=1);
run;

* invalid ;
%let header_rldx_audit_key=49;
%CheckRldxAuditKey(&header_rldx_audit_key)

* does not execute due to %abort cancel, which is what I want ;
%put &=syscc;

proc print data=sashelp.class (obs=19);
run;

Neither approach has performance concerns, so I'll probably go with Version 2.  But I have to embed unwanted logic in a macro.  If I bailed on providing a success message to the end user then my original approach using a SP would work.

But my original questions remain, and in summary:  Why does a simple PRINT statement cause a WARNING in SAS?

I'm hoping a little birdie may chime in.  I'd open a TS ticket but I'm not getting joy from my local TS lately.

[SASKiwi]

I tried a simple proc print in EG and it behaved as expected - 0 return code:

proc print data=sashelp.class (obs=1);
run;
%put &=syscc;

Could it be not having an output destination open is causing your warning?

[ScottBass]

---

@SASKiwi wrote:

I tried a simple proc print in EG and it behaved as expected - 0 return code:

proc print data=sashelp.class (obs=1);
run;
%put &=syscc;

Could it be not having an output destination open is causing your warning?

---

But my original questions remain, and in summary:  Why does a simple PRINT statement cause a WARNING in SAS?

Hi @SASKiwi ,

The PRINT statement in SQL Server, not PROC PRINT in SAS.

If you have permission to create a stored procedure in your environment, do this:

CREATE  PROCEDURE dbo.spHelloWorld
AS
BEGIN
    SET NOCOUNT ON
    PRINT 'Hello World'
END
GO

Then in SAS:

libname foo odbc "library details pointing to your SP";

proc sql;
   connect using foo;
   execute by foo;
EXEC dbo.spHelloWorld
   );
quit;

Then ask yourself "Why is this a WARNING?"  What possibly causes SAS to think this is a warning rather than a note?

[SASKiwi]

@ScottBass - Unfortunately I can't test your stored procedure as I don't have the required permissions .