| Author |
Topic |
|
DebAU
Starting Member
6 Posts |
 Posted - 2008-07-16 : 14:38:57
|
Hey all,I am a Domino Admin/Developer by day...yes, one of "those" people... I use Domino LEI to connect to a SQL database. Up until first of April this was running beautifully, not not much. My SQL dba has decided to be less then helpful, but we won't go there! I have been researching how to find logs and other items on SQL, but don't know if I can find what I am looking for.Here is the error that I get in LEI every day around 3:45 pm that causes my LEI activity to fail:"07/15/2008 03:45:17 PM Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'dbo.PS_RI_JOB_REVIEWER' with unique index 'PS_RI_JOB_REVIEWER'., Connector 'PS_RI_JOB_REVIEWER', Method -Insert- (2601)07/15/2008 03:45:17 PM Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated., Connector 'PS_RI_JOB_REVIEWER', Method -Insert- (3621)"This activity runs 5 times a day, 4 of those times it is successful, only the 3:45pm time do I get this failure. Any help as to where to look would be super.I want to check and see if something else, such as a Peoplesoft process is also hitting this table, and giving me the issue.ThanksDeb |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-16 : 14:47:26
|
| You are attempting to insert a row into the PS_RI_JOB_REVIEWER table that already exists per the unique index. You need to either fix the application so that it properly handles the error, have it check for the duplicate before attempting the insert, or drop the unique index if the duplicates are wanted.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
DebAU
Starting Member
6 Posts |
Posted - 2008-07-16 : 15:22:09
|
| THANKS!Please pardon my ignorance in this topic, what do I need to tell my sql dba to change? Also the sql dba says he has put a plan guide in effect. Should this make the difference? So far, it hasn't. Deb |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-16 : 15:28:03
|
| What does he mean by "plan guide"? Execution plan perhaps? If yes, then that will have no effect on your error.As far as what to tell the dba, well it depends on how you want to handle the situation. Do you want the duplicate row added?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
DebAU
Starting Member
6 Posts |
Posted - 2008-07-16 : 15:30:52
|
| The Sql dba called it a "query plan guide." Here is the one he says he used for this particular issue:"plan_guide_id name create_date modify_date is_disabled query_text scope_type scope_type_desc scope_object_id scope_batch parameters hints------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------------------------------------ --------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------65539 CursorGuide_PS_RI_JOB_REVIEWER 2008-05-09 16:49:39.533 2008-05-09 16:49:39.533 0 SELECT BUSINESS_UNIT, RI_JOB_ID, REVIEWER_ID, SEQUENCE_NBR, LAST_MAINT_DTTM, RI_REVIEW_STATUS FROM dbo.PS_RI_JOB_REVIEWER WHERE (EXISTS (SELECT 'X' FROM PS_RI_JOB WHERE PS_RI_JOB.RI_JOB_ID = PS_RI_JOB_REVIEWER.RI_JOB_ID AND PS_RI_J 2 SQL NULL NULL NULL OPTION(FAST 1)(1 row(s) affected)"Sorry for the ugly formatting, it is a copy and paste from an email attachment. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-16 : 15:33:03
|
| I don't see how this is even relevant to your issue. I don't think your SQL DBA understands the issue.The key is here: "Cannot insert duplicate key row in object 'dbo.PS_RI_JOB_REVIEWER' with unique index 'PS_RI_JOB_REVIEWER'".So without the answer to my question of wanting the duplicates or not, it's hard to help.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
DebAU
Starting Member
6 Posts |
Posted - 2008-07-16 : 15:36:27
|
| Thanks for the help! I am going to figure out how to word this, so that my sql dba will understand...I'll let you know what I get back from him.The good think is as a part of all of this, I have gotten deeper into SQL and find it all fascinating. |
|
|
|
DebAU
Starting Member
6 Posts |
Posted - 2008-07-16 : 15:37:31
|
| Oh, and no, I don't want duplicates. It should just skip the dup and move on to the next. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-16 : 15:40:01
|
| Then you need to update the application code to properly handle this condition. There is nothing we can do on the database side to fix this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Domino admin needing assistance