PROC SQL Execute Stored Procedure Warnings

[ThierryHerrie]

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?

[SASKiwi]

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.

[ThierryHerrie]

Still getting warnings

[SASKiwi]

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

[ThierryHerrie]

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

[SASKiwi]

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.

[ThierryHerrie · Accepted solution]

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!

[SASKiwi]

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

[ashna]

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

[ThierryHerrie]

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

[ashna]

Thanks ll try and check if that helps me out 

[k75240]

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.

[SASKiwi]

@k75240 - The code is in the first post.