We help IT Professionals succeed at work.
Get Started
Troubleshooting Question

Need to surpress meesages when  IGNORE_DUP_KEY = ON

42 Views
Last Modified: 2021-01-11
I thought I had a solution to this yesterday but it turns out that indexing with IGNORE_DUP_KEY = ON produces a warning message, which is not acceptable in my application... so I need another solution as I already closed the prior question.

Background:

I have a MasterDatabase with more than a million records... the Primary Key is the vendernumber + partnumber.

Each day we get new lists of inventory.. and we need to add records to the MasterDatabase for each new part received.

For example, if today we get a list of 1000 new parts (ventdernumber+partnumber) and only 5 of them are not already in our MasterDatabase, then I want to add those five records. The record that I add would include fields for the vendernumber and Partnumber as well as  a bunch of other fields that would be blank.

Using MS Access this process was simple process.  I would run an append query using Docmd.setwarnings false   ... in this case, I would run a query that tries to append all 1000 records.. but only the five unique records are added.. which is what I want... others are disgarded because a record with the same primary key already exists... A perfect solution for this application.  The query would take just a second or less. 

Now that I have upsized my database to SQL the routine no longer works because SQL does not have a setwarnings false command.. I tried creating the index with  IGNORE_DUP_KEY = ON.  The change works as expected except I get a message "Duplicate key was ignored” which requires acknowledgment before the code continues.

This behavior is unacceptable as the command is used as part of a maintenance operation that requires an unattended operation.

I need a way to suppress this message.. or a way to trap the message and programmatically respond to it... or a completely different process..


Comment
Watch Question
SQL Server Consultant
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 24 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE