Integration med MS SQL Stored procedure

Reply
Occasional Contributor
Posts: 12

Integration med MS SQL Stored procedure

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

Esteemed Advisor
Posts: 5,198

Re: Integration med MS SQL Stored procedure

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.
Data never sleeps
Occasional Contributor
Posts: 12

Re: Integration med MS SQL Stored procedure

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;

31

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

 

38

39 %put &SQLXMSG;

 

40 %put &SQLXRC;

0

41

42

43 %_eg_hidenotesandsource;

58

59

60 %_eg_hidenotesandsource;

 

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

Best wishes

Martin

Esteemed Advisor
Posts: 5,198

Re: Integration med MS SQL Stored procedure

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.
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 80 views
  • 0 likes
  • 2 in conversation