IGNORE_DUP_KEY return message

[SteveMac32]

I am running a stored procedure which populates a table and has a IGNORE_DUP_KEY and a clustered index on it

I am getting the message that "Duplicate key was ignored" which creates an error in the calling VB program

Can this be suppressed or changed somehow? I have tried a number of things like "SET ANSI_WARNINGS OFF" but to no avail.

Anymore ideas


Thanks

Steve

 

[Emtucifor]

SET NOCOUNT ON *could* help? I think what happens it that VB can in fact ignore an error, but with SET NOCOUNT OFF, it ignores the rest of the SP after the first message it gets, and treats that as an error.

I think you will find (please tell me if I'm wrong) that if you SET NOCOUNT ON in your SP, the DB library may continue farther in processing the results of the SP and allow the behavior you expect.

It is in fact pretty good practice to SET NOCOUNT ON by habit at the top of your stored procedures because not only does it help avoid client problems similar to the one you're having, it actually increases performance in many cases (especially if there is a loop generating many row count messages).

 

[SteveMac32]

Thanks Emtucifor but I am afraid that SET NOCOUNT ON does not work in this case, I thought that NOCOUNT returned or not the number of rows affected by statement and did not suppress other messages.

I have had to come up with a solution using the calling VB application.

 

[Emtucifor]

The effects of SET NOCOUNT ON are a bit more subtle than simply whether the number of rows are returned. As I said, extra rowcounts in a stored procedure can cause the DB library (such as ADODB or DAO) to think it has received a recordset and then stop.

Also, have you explored using .NextRecordset even after the error? Have you looked at the ADO errors collection in addition to the VB error that is thrown (the VB error always shows only the first ADO error, but ADO errors come in as a collection and you can step through all the errors to learn more)? Would you show us some code?

I'd like to know the answer to this problem, too.

 

[gmmastros]

Take a look at this:

http://bytes.com/topic/sql-server/answers/443567-preventing-message-duplicate-key-ignored

Apparently you cannot suppress the message with sql2000, but you can with sql2005 using a try-catch block.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JoeAtWork]

Can you not just trap for this particular error in your VB code and do a Resume Next in your error handler?

 

[SteveMac32]

Hi Joe
Thats exactly what I did as it looks like you cannot suppress messages well with sql2000.

Thanks also gmmastros bit it is easier and quicker to do in vb, like your solution though.

Steve