Return Sql database warning to SAS Log

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Return Sql database warning to SAS Log

Hello everyone. I have a process that takes a sas table, and inserts records from it into a sql server database. I am using Windows 7 if that matters! Anyways, the code is very simply a proc sql insert into, shown below.

proc sql;

insert into Databs.Testtable (loanid)

select loanid  from sastestdataset;

quit;

run;

The issue I have is that the sql database table "testtable" on the Databs database (set up through a libname) has a constraint of a Primary Key on the "loanid" column.  If I try to insert the same record into the table multiple times, it is not allowed (as it shouldn't be). However the issue I have is my log will still SAY that the record was inserted into the datatable.

Example:

Say I run the query and insert the value of 1,2,3, into the table.  Then I try to run it again and insert the same three values. It should error out (and on the sql side it does) however my log says

"NOTE: 3 records were inserted into Databs.Testtable

NOTE: Procedure sql step took : ...."

So is there a way to return the errror you would get in sql of

Violation of PRIMARY KEY constraint 'PK_FR_test'. Cannot insert duplicate key in object 'dbo.testtable'. The duplicate key value is (1).

To the sas log so I can know when errors have occured within the insert? 

Thank you!

Brandon


Accepted Solutions
Solution
‎12-30-2013 09:36 PM
Super User
Super User
Posts: 6,502

Re: Return Sql database warning to SAS Log

Sounds like you will need to make your process more complex to deal with these constraints that have been put on your tables.  You might try inserting only one record at a time. Or using explicit pass-through.

Did you try using explicit pass-through SQL code?  Does that return a reasonable error message?

proc sql ;

connect to ...... as sql (...) ;

execute by sql ( insert into testtable ...) ;

quit;

Then if want to try to inserting multiple records you could first move them into the database into a temporary table that does NOT have any constraint and try using explicit code to inset into your real target table from there.

View solution in original post


All Replies
Super Contributor
Posts: 418

Re: Return Sql database warning to SAS Log

Does anyone have an answer to this perhaps?

Is there any way to check the primary key combination before entering it into a sql table?

Super User
Posts: 5,260

Re: Return Sql database warning to SAS Log

My experience with SQL Server (via OLEDB) is that when constraint is not met during insert, there is an error in log, but the message is usually not very clear about what kind of error you have encountered.

By looking at your log, it sounds like the inserts are successful.

What does the macro variables SQLRC and SQLMSG say?

Data never sleeps
Super Contributor
Posts: 297

Re: Return Sql database warning to SAS Log

Hi Another,

I don't have access to a SQL Server at the moment to test, but the SASTRACE system option may be helpful.

For more information check out. SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

Regards,

Scott

Super Contributor
Posts: 418

Re: Return Sql database warning to SAS Log

Sorry for the very long delay in the response.

I actually tried all of these, and a few other tricks and cannot find an answer. To answer your question LinusH, the SQLRC variable is a 0 when the insert works, and when it doesn't work. Is sqlmsg a different variable depending on the DBMS, because it says "macro Variable not defined", so I think the name might be an issue.

I am not seeing any errors using SASTRACE, but I have never run it so might be looking at it incorrectly.

I am not sure how to fix this problem, besides adding a timestamp column and then manually checking that the max timestamp is equal to the most recent run, which requires a selection from the table thus slowing code. Hopefully this isn't the answer I have to use going forward.

It appears to me that SAS fully thinks it is inserting into sql server, even though it isn't. Does anyone know if this is a bug with Sql server specifically?

Solution
‎12-30-2013 09:36 PM
Super User
Super User
Posts: 6,502

Re: Return Sql database warning to SAS Log

Sounds like you will need to make your process more complex to deal with these constraints that have been put on your tables.  You might try inserting only one record at a time. Or using explicit pass-through.

Did you try using explicit pass-through SQL code?  Does that return a reasonable error message?

proc sql ;

connect to ...... as sql (...) ;

execute by sql ( insert into testtable ...) ;

quit;

Then if want to try to inserting multiple records you could first move them into the database into a temporary table that does NOT have any constraint and try using explicit code to inset into your real target table from there.

Super Contributor
Posts: 418

Re: Return Sql database warning to SAS Log

Interesting. It appears that using an explicit pass-through sql to insert the records DOES indeed work!

THanks Tom, I will just use this method going forward to determine if a primary key error has been found within the given process!

On a related note, do you know if there are any automatic variables that are created that show an error was found using explicit sql pass through code? The sqlrc macro variable listed above does not seem to work (it stays as a 0).

If not, I will have to pass the log out to a file somewhere and then parse through the strings to look for an error Code.

Thanks!

Brandon

Super User
Super User
Posts: 6,502

Re: Return Sql database warning to SAS Log

Did you try the variables mentioned in this help page?

SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 530 views
  • 0 likes
  • 4 in conversation