Looking for SQL error help

[Debau2005]

I am a Domino database admin. I use LEI to connect Domino to SQL. On one of my LEI activities I am getting this error:
04/11/2008 03:45:42 PM Error: Cannot insert duplicate key row in object 'dbo.test' with unique index 'test'., Connector 'test', Method -Insert- (2601)
04/11/2008 03:45:42 PM Error: The statement has been terminated., Connector 'test', Method -Insert- (3621)

This activity runs 5 times a day. It only fails around 3:45pm, and if I restart it, it then runs successfully. My SQL admin is not helpful at the moment, he is more concerned with another activity that runs before this one, that is now taking about 4 minutes longer to run then usual. They may be related, but the first one doesn't fail every day at the same time!

Thanks

[KatyMan]

1. I don't know about Domino or LEI

but could it be that the job is running twice at that time, and is trying to redo activity it's already done? Is it the same script that runs 5 times a day, or a does it vary?

[patriotvoice]

2. Are you write-locking the table prior to the insert?

It looks like you have two competing processes or threads (either duplicates or cooperatives not cooperating) trying to simultaneously write. Try locking your tables before the write:

LOCK TABLES dbo.test WRITE
INSERT INTO test (...)
UNLOCK TABLES

[many a good man]

3. What is the primary key on the table?

If it is a numeric surrogate key, how is it generated?

Databases that don't have special identity key data types or sequence generators often use a separate table to maintain the last number used in each of the tables. This is the table that should be locked while inserting.

If the insert statement is looking up the max value of the PK before insertion, change the code so it runs as one statement instead of two. Maybe create a function that locks the table until the entire transaction is completed.

[Debau2005]

4. Plan Guide

My SQL dba states he put a plan guide in place. This seemed to work for a month or so, but as of July 4th weekend, we are back with the slow connections from Domino to SQL, and now the usual 3:45pm failures!

He states he is sending me the plan guide, and the SQL logs, is there anything in particular I need to look for?

[Debau2005]

5. The error is:

07/14/2008 03:51:10 PM Error: 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/14/2008 03:51:10 PM Error: The statement has been terminated., Connector 'PS_RI_JOB_REVIEWER', Method -Insert- (3621)

[many a good man]

6. You need a friendly dba

Ask him/her for the list of columns in the table ps_ri_job_reviewer and then ask which column or columns are included in the unique index named ps_ri_job_reviewer. Those columns have to be unique so the one job must somehow be inserting the same value(s) twice. It may be inserting multple rows at a time and one or more were probably added in the previous run. What are the criteria in the sql statement used? You may need to add DISTINCT to the select statement that generates the list of value for insert.

Best bet is to ask your dba to run SQL Profiler against the database starting a minute or two before the failing job kicks off. It will show exactly which statement is getting the error and possibly the value that causes the violation.

[Debau2005]

7. Thanks!

I'll take a look and see what I can find. Unfortunately, my DBA is not high on my list of competent IT professionals! He tells me there is nothing wrong and if there is something wrong, there no way to figure out what is. Let's just say my confidence in his abilities to even turn on the lights is "low."

[Debau2005]

8. Looks like I need a "Friendly" DBA

actually I'd take an unfriendly COMPETENT dba. He told me that running SQL Profiler would not do anything to help our understanding of the issue. He has a way of saying no, without using the word, so it can't come back and bite him in the ass later.

He implied that he didn't really know what SQL Profiler would show. I stated it would not hurt to run it, and if it showed nothing then no harm done.

He states that he is going to start the testing process for a patch/hot fix for SQL Server 2005. He again stated this in a meeting this morning, so now I have it on record. I will hound him every day for the next week, and then take it to the next up the office ladder.

I have fought this since Feb, and I am tired of KNOWING that my server job will fail at the same time every day.

Thanks for the help. I am going to keep fighting. I am going to re-write the LEI/SQL sire to use DISTINCT in my tes environment and see what results I get.