topic Re: Integration med MS SQL Stored procedure in SAS Programming

Integration med MS SQL Stored procedure

MartinJeppesen — Mon, 10 Jul 2017 09:28:02 GMT

Hej SAS folk

Det er lykkedes mig at kalde en stored procedure i en Microsoft SQL database med en parameter, som får proceduren til at eksekvere sin kode.

Fejler proceduren, får jeg en ret intetsigende besked i loggen i SAS. Er der nogen måde man kan programmere i SAS, så man får den egentlige fejlbesked fra SQL vist i SAS?

Jeg benytter denne kode:

PROC SQL;

CONNECT TO odbc as sql (NOPROMPT='DRIVER=SQL Server; SERVER=testsqlsrv1; DATABASE=xxxx');

execute (FjernDatasaetLaas &datasetnavn)

by sql;

DISCONNECT FROM sql;

QUIT;

Mvh

Martin

Re: Integration med MS SQL Stored procedure

LinusH — Mon, 10 Jul 2017 11:46:43 GMT

Hi Martin,

even not stated explicitly, this forum is mainly English written. If you are more comfortable with Scandinavian, try the Regional Groups/SAS Community Nordic.

Have you tried SQLXMSG? Don't think there are any other options as long you wish to call from SAS.

The SQL pass-through facility generates return codes and error messages that are available to you through these SAS macro variables:

SQLXMSG contains DBMS-specific error messages.

SQLXRC contains DBMS-specific error codes.

You can use SQLXMSG and SQLXRC only through explicit pass-through with the SQL pass-through facility. See Return Codes.

You can print the contents of SQLXMSG and SQLXRC in the SAS log by using the %PUT macro. SQLXMSG is reset to a blank string, and SQLXRC is reset to 0 when any SQL pass-through facility statement is executed.

Re: Integration med MS SQL Stored procedure

MartinJeppesen — Wed, 12 Jul 2017 13:33:13 GMT

Hi Linus

Thanks for your fast reply and aplogies for not writing in english in the first place. But impressive still that you actually understood the question!

I am not sure how to use the macro variables, because they do not give me any messages in the log. I have implemented them in this way:

PROC SQL;

CONNECT TO odbc as sql (NOPROMPT='DRIVER=SQL Server; SERVER=testsqlsrv1; DATABASE=xxxx');

execute (FjernDatasaetLaas &datasetnavn)

by sql;

DISCONNECT FROM sql;

QUIT;

%put &SQLXMSG;

%put &SQLXRC;

And the log shows:

1 %_eg_hidenotesandsource;

5 %_eg_hidenotesandsource;

32 PROC SQL;

33 CONNECT TO odbc as sql (NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX);

34 execute (FjernDatasaetLaas &datasetnavn)

35 by sql;

NOTE: No data found/modified.

36 DISCONNECT FROM sql;

37 QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.02 seconds

cpu time 0.00 seconds

39 %put &SQLXMSG;

40 %put &SQLXRC;

43 %_eg_hidenotesandsource;

60 %_eg_hidenotesandsource;

Do you know if my implementation is wrong or if something else is failing?

Best wishes

Martin

Re: Integration med MS SQL Stored procedure

LinusH — Wed, 12 Jul 2017 15:31:27 GMT

Well, I'm Swedish so perhaps it's not that impressive..😎.
Code looks fine by the eye.
Since the messaging communication is specific for the SAS/ACCESS engine and the target database, I suspect that not much can be done.
Perhaps you need to check with SAS tech support for verification.