08-21-2015 12:12 AM
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
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
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
08-21-2015 12:46 AM
Clearly you are violating constraints on the Oracle table.
Examine stored process logic with the constraints and your interaction.
08-21-2015 02:59 AM
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.
08-21-2015 02:17 PM
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.
08-21-2015 03:31 PM
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.
08-21-2015 04:03 PM
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.
08-28-2015 04:57 AM
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
If you struggle to get anywhere when talking to them, try creating that user a test stored process with this inside:
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;
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.