BookmarkSubscribeRSS Feed
sasprofile
Quartz | Level 8

Hi  All,

I have an Issue with SAS AMO 5.1.

all other users are able to update the records without any issues except me.

When am trying to run a Stored process to update some records via a Interface. it is throwing below errors

it throws below two error prompt windows as shown  and the data is never getting updated.

Error window 1

SAS Add-In

stored process CD_QM_UPDATE encountered errors while opening the results

we can continue to open the results or postpone processing by placing them in the status window.

Continue   Postpone    Abort (These are buttons)

When I click continue above below it prompts with a different below window

When I checked the log window of first error  it shown below errors in that

ERROR: ORACLE execute error: ORA-00001: unique constraint (PDN_ST.SWA_PROD_CDQ_UNQ) violated.

ERROR: ROLLBACK issued due to errors for data set FQTAB. TB_SWA_PROD_CDQ.DATA.

Error window 2


SAS Add-In

stored process "Name" CD_QM_UPDATE has completed with no visual content. You can refresh this analysis through the Manage content dialog box

When I close this window the records never updates

any one has faced similar kind of experience

I would really appreciate if any one can help me with this issue

Thanks in Advance

6 REPLIES 6
LinusH
Tourmaline | Level 20

RTL!

Clearly you are violating constraints on the Oracle table.

Examine stored process logic with the constraints and your interaction.

Data never sleeps
Kurt_Bremser
Super User

Your stored process tries to do something that violates the integrity constraints imposed on the table(s) in question.

Inspect the constraints/integrity rules defined for the table(s). Since only one user is affected, it may be that you lack permissions for one of the tables which must be updated to make the transaction valid.

sasprofile
Quartz | Level 8

It works for all other users when they are updating but only its not working for one user, and even the user for whom its working he tried to connecte to the sas server with his credential and tried to update stored process from non working sas amo and its throwing same errors.so its not a permissions issue.

LinusH
Tourmaline | Level 20

Or local installation / configuration issue?

Sounds like you need to dig into bits and bytes.

I sugest that you open a track to SAS tech support.

Data never sleeps
sasprofile
Quartz | Level 8

I have already open sas track,they are not very helpful they are suggesting to contact Oracle DBA to find out the constraints on the Oracle table we are getting error.

boemskats
Lapis Lazuli | Level 10

I'd suggest making an identical copy of your stored process for testing with this particular user, and looking at adding some of the SASTRACE options for debugging (probably ',,,d'), and looking in the STP logs to see what values are being sent to Oracle and what logic is executing.

SAS are correct - this problem is an Oracle problem, but using the SASTRACE option above you should be able to figure it out with your DBA. I know they're very friendly people Smiley Happy

If you struggle to get anywhere when talking to them, try creating that user a test stored process with this inside:

proc sql;

  CONNECT TO ORACLE (USER=XXX PASS=XXX PATH=XXX);

    create table userconstraints as
      select * from connection to oracle (

        SELECT * FROM user_constraints

      );

DISCONNECT from ORACLE;

quit;

proc print data=userconstraints; run;

This will give you something to present to your DBA. It may be worth comparing the output for users where it is working and others where it is not.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 1318 views
  • 0 likes
  • 4 in conversation