May 7, 2009 at 11:03 am
Hi,
I have a stored procedure that is using a number of cursors and building a number of dynamic insert and update statements as strings and executing them using sp_executesql within each cursor. Finally, the procedure commits or rolls back the transaction and returns status code.
When I execute the code in query analyzer and hard code in the two parameters the code executes with no errors. However, when I run the stored procedure as...
exec sp_do_stuff 'REG', '1000001'
It generates the following error on the final insert statement even though it is inserting zero rows to this table....
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.s_table' with unique index 'iux_s_table_dky_columnkey'.
The statement has been terminated.
Does anyone have suggestions on next steps to debug or what the problem might be?
Thanks,
Chris
May 7, 2009 at 11:23 am
Following your topic i wud say there seems some problem with table constraints. Cross check the relationship of the table and constraint as well.
May 7, 2009 at 11:26 am
There are many problems:
- Cursors;
- Dynamic insert and updates;
- sp_ in the name of the user’s store procedure.
Also, every temporary space, table, execution plan, etc. cleans out every time you run your code in query analyzer. That might be different when you run/re-run the code in the store procedure. Any case, there is no way to say what is wrong without seeing actual code. Believe me, SQL Server works great if it is used properly.
Alex Prusakov
May 7, 2009 at 11:31 am
I was not disputing that SQL Server does not work well, I was simply asking for suggested debugging tips on an issue that seemed to not make sense in a complex stored procedure that I am supporting. But I appreciate the constructive advice in your post...
May 7, 2009 at 11:35 am
I believe it may be related to the constraint....I tried removing the constraint, running the stored procedure, and re-creating the constraint which could not be added due to uniqueness issues with a record.
It looks like the constraint was not set up for the correct columns and the insert is actually trying to insert a single record, but this is not obvious when running in query analyzer.
Thank you for the suggestions but I think this is all set now.
May 8, 2009 at 8:36 am
The error is very straight forward. You've got duplicate data. The key is to identify what constraints you have and then break down the procedure and capture the data output at any point where it would be affected by the constraints by simply selecting the data that you would insert/update that affects the constraint prior to that update/insert.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply