Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    10

    Question Unanswered: Affect of deleting error messages

    I'm using a unique index on a table with ignore_dup_key to get around using distinct in a stored procedure. In Query analyser, when I issue the stored procedure, you get a message and also the data in the Grid. In a stored procedure call, initiated from MS Access, the message means I have to put additional client side programming to work around it. I know that duplicate keys are ignored; that is why I've created the table with this feature (unique index in combination with ignore_dup_key). So what's with the message. If I delete the Error message 3604 will I still get an error message?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm still trying to figure out when I would ever want to do this.

    What are you trying to do?

    From BOL

    Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.

    If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.

    The table shows when IGNORE_DUP_KEY can be used.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Posts
    10
    I'm trying to use a server side solution to improve performance of a stored procedure. I've got a one-to many table with Street names (being the many) where the recno links back to the main table. It is possible for say King St to have a cross street of Kingsway. Hence Recno 13 for both. When I then match back to the main table I'd get multiple rows where I desire 1. To get around this I originally used the distinct clause. It works but is much slower than if I create a unique index with the ignore_dup_key option on a temp table. Problem being is the message. I've explicitly requested this action 'ignore_dup_key' so why tell me 'Duplicate key was ignored.' as if it is an error? We were force to a slower server and now my endusers are complaining about speed, that is why I wish to solve this server side instead of client side. I could write a routine to deal with the error client side but why go this route if I don't have too.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Select Distinct

    But you need to resolve this from a sql perspective...

    did you read the quote...

    You really should address handling MUCH more closely...you'r going to need to as well as have a general expectance of what the stored procedures are doing and what the expect...
    Last edited by Brett Kaiser; 06-10-04 at 17:37.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And that's not an error it's a warning...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Posts
    10

    Angry

    Similar to using a "set nocount" to eliminate an unwanted response to an end user. I'd expected and hoped an equivalent for eliminating an unwanted message from the use of a unique index. Yes I did read "SQL Server issues a warning and ignores the duplicate row" of your message. I wish to eliminate the message as it is unwanted. This is the contents of the message window in Query Analyzer "Server: Msg 3604, Level 16, State 1, Procedure ZZZStreet, Line 15
    Duplicate key was ignored." The only way I can think of eliminating this is to delete the error message 3604 from the SQL Box. I've never done it and I am looking for guidance as to whether this will or will not fix the unwanted message from being given. I don't see any downside to this error being gone as the performance improvements should outweigh the loss of a strictly informational message. Maybe there is a downside that I'm unware of, an that's what I'm trying to figure out here.

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    funny, I'm using EM here but when I create a table say Table1 with a varchar column (indexed, unique, ignore duplicate key) I am not able to insert duplicate values. From BOL it says: "Ignore duplicate key If you create a unique index, you can set this option to ensure each value in an indexed column is unique.".
    Why am I confused?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •