topic PROC SQL Execute Stored Procedure Warnings in SAS Procedures

PROC SQL Execute Stored Procedure Warnings

ThierryHerrie — Fri, 25 May 2018 06:07:21 GMT

Hi,

I'm executing a MS SQL Stored Procedure from SAS DI Studio like this:

PROC SQL ;
  CONNECT TO odbc (&_OUTPUT1_connect.) ;
  EXECUTE (TheStoredProcedure) BY odbc ;
  DISCONNECT FROM odbc ;
QUIT ;

However the Stored Procedure contains print messages like:

PRINT 'Rows inserted: ' + CAST(@rowsInsertedHD AS VARCHAR(100))

And those messages are come back as warnings in SAS.

Is there any way to suppress those warnings, without altering the Stored Procedure?

Re: PROC SQL Execute Stored Procedure Warnings

SASKiwi — Fri, 25 May 2018 07:25:34 GMT

What happens if you use: proc sql nowarn;

Note to stored procedure developer: Any printed output should be optional and turned off by default - program design 101.

Re: PROC SQL Execute Stored Procedure Warnings

ThierryHerrie — Fri, 25 May 2018 07:29:56 GMT

Still getting warnings

Re: PROC SQL Execute Stored Procedure Warnings

SASKiwi — Fri, 25 May 2018 22:48:52 GMT

What's the issue for you? The warnings triggering a non-zero return code on the job or something else?

Re: PROC SQL Execute Stored Procedure Warnings

ThierryHerrie — Thu, 31 May 2018 05:53:34 GMT

Yes, that's basically it.
Also because I am surprised that it looks like SAS treats the print statement from SQL as warnings

Re: PROC SQL Execute Stored Procedure Warnings

SASKiwi — Thu, 31 May 2018 07:26:46 GMT

What scheduler do you use? You might be able to tweak the batch script file to ignore warnings in this case. You could also open a track with SAS Tech Support to see if they have any other ideas.

I'm assuming here there is no possibility of getting the stored procedure changed as that would be the best option.

Re: PROC SQL Execute Stored Procedure Warnings

ThierryHerrie — Thu, 31 May 2018 08:37:16 GMT

I've made a workarround by creating a SQL job that executes the SP. From SAS I execute the job. No warnings then and good enough for now.

We can't modify the Stored Procedure indeed.

Thanks for your help!

Re: PROC SQL Execute Stored Procedure Warnings

SASKiwi — Thu, 31 May 2018 22:47:59 GMT

@ThierryHerrie - No problem! Please mark your solution as answered in that case.

Re: PROC SQL Execute Stored Procedure Warnings

ashna — Mon, 24 Sep 2018 16:22:15 GMT

Hi,

I facing same issue while executing, Can you provide the answer you figured out for solution.

Hope that can help in resolving my issue.

Thanks,
Ashna

Re: PROC SQL Execute Stored Procedure Warnings

ThierryHerrie — Tue, 25 Sep 2018 11:29:19 GMT

Hi Ashna,

I created a job in SQL Server, using SQL Server Management Studio. The job executes the SQL Stored Procedure.

From SAS I execute the SQL Server job. That way I don't get a warning in the SAS log.

Kind regards,

Thierry

Re: PROC SQL Execute Stored Procedure Warnings

ashna — Tue, 25 Sep 2018 13:39:26 GMT

Thanks ll try and check if that helps me out

Re: PROC SQL Execute Stored Procedure Warnings

k75240 — Wed, 09 Oct 2019 19:19:08 GMT

Hi Theirry,

Could you please share sas code, where you are calling sql stored procedure from SAS EG. I'm facing same issue you faced earlier. Thanks.

Re: PROC SQL Execute Stored Procedure Warnings

SASKiwi — Wed, 09 Oct 2019 19:23:10 GMT

@k75240 - The code is in the first post.