August 11, 2011 at 1:03 pm
Hello -
I have a cobol stored proc / assembly that runs the following code:
SELECT DISTINCT LO.CLM_ID_C
,LO.POL_ID_N
,LO.LSS_OCR_D
,LO.LSS_OCR_SEQ_N
,PC.POL_COVG_ID_N
,CC.CLMNT_SEQ_N
,PC.MJR_PRL_CODE_N
,CC.RSV_CTG_C
,PC.ANNL_STMNT_LINE_C
,PC.BUR_SUBLN_C
,PC.BUR_TYP_C
,PC.BUR_CLAS_C
,LO.ENVIR_HAZ_C
,CC.UDS_COVG_C
,CC.UDS_COVG_TYP_C
,CC.CLM_COVG_STAT_C
FROM CLM_LOSS_OCCUR LO
JOIN CLM_CLMNT_COVG CC
ON CC.POL_ID_N = LO.POL_ID_N
AND CC.LSS_OCR_D = LO.LSS_OCR_D
AND CC.LSS_OCR_SEQ_N = LO.LSS_OCR_SEQ_N
AND CC.CLM_COVG_STAT_C <> 'Z'
JOIN CLM_POLICY_COVG PC
ON CC.POL_COVG_ID_N = PC.POL_COVG_ID_N
WHERE LO.POL_ID_N = 1255760
AND LO.CLM_ID_C = '000100250010'
The SQL runs fine in both versions of SQL. It runs fine as an SP (does not fail on the open) in SQL2K5. But when I tried to execute the SP in SQL2K8 R2, I get the following error:
An exception occurred when the SQL statement was executed. 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.'
Yes these tables have RI defined on them - but I would not expect it to come into play on a simple select / open cursor.
Can anyone give me some help as to why this is happening and if there is something I will need to add / change in all my stored proc cursors.
Thanks !!
Dave
August 11, 2011 at 1:20 pm
davemiller3 (8/11/2011)
Hello -I have a cobol stored proc / assembly that runs the following code:
....
The SQL runs fine in both versions of SQL. It runs fine as an SP (does not fail on the open) in SQL2K5. But when I tried to execute the SP in SQL2K8 R2, I get the following error:
An exception occurred when the SQL statement was executed. 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.'
Assembly makes me wonder if you've ensured that all your server & CLR perms in SQL 2k8 are set correctly. I think (but am not sure) that some of the default settings may have changed between it and SQL 2k5.
"Failed to enable constraints". That's an error I haven't seen before. Have you googled it yet?
August 11, 2011 at 2:56 pm
Thanks for replying -
Here's an update: if I remove the distinct from the statement, it runs (the cursor opens). Whatever that means !!!!
The other COBOL SP's assemblies in this app execute just fine using SQLK8 R2. I tried both VS2005 and VS2008 - same error. So I do not think it is CLR related. I just want to add also that none of these fields are nullable and every row returned has a value (when the select stmt is executed in SSMS)
Next plan of attack is to go thru some of the other SP's and see if I can find similar code that fails. I also want to try removing the RI from the tables involved and see what that does.
The only thing I have found in google related to this constraints error message talks about XML and datasources / dataset field attributes but this process is not using any those.
Thoughts ???
Thanks....
Dave
August 12, 2011 at 4:42 am
davemiller3 (8/11/2011)
Thanks for replying -Here's an update: if I remove the distinct from the statement, it runs (the cursor opens). Whatever that means !!!!
The DISTINCT keyword in SQL Server actually forces any query to take more time as the Engine has to group the records and sort them to pull out duplicates before posting the results. If removing this keyword causes the cursor to run, that tells me the query / code itself needs to be re-optimized for SQL Server 2008.
I take it you're running this in SSMS? If so, have you checked the Execution Plan while running the code to see what the engine has done to the code?
Also, try this. Pull all records (without the DISTINCT) into a temp table or a staging table, then do a SELECT DISTINCT on that.
But I would definitely think you have an optimization issue at this point. Do the troubleshooting first before going for the "pull into a table and select distinct on that" option.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply