BookmarkSubscribeRSS Feed
MartinJeppesen
Calcite | Level 5

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

3 REPLIES 3
LinusH
Tourmaline | Level 20

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
MartinJeppesen
Calcite | Level 5

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

LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 648 views
  • 0 likes
  • 2 in conversation