?
Solved

How can one ignore errors in a stored procedure?

Posted on 2003-03-13
17
Medium Priority
?
523 Views
Last Modified: 2012-06-27
I need to calculate something from some tables into a new one and return some records of it. So there is a stored procedure which creates a table with index and specifies IGNORE_DUP_KEY. When it is executed in Query Analyzer it displays error messages "Duplicate key was ignored." each time it ignores a duplicate key and returns the desired rows. And it's OK. But when the same stored procedure is executed from ASP code through ODBC it returns an error and doesn't returns any records.
 
How can one ignore errors in the sp, in order to make ODBC and ASP think that there was no error?
0
Comment
Question by:Jigit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +3
17 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
ID: 8126697
Try putting SET NOCOUNT ON at the top of the procedure. It probably won't fix it, but at least it can be ruled out.
0
 
LVL 9

Assisted Solution

by:xenon_je
xenon_je earned 500 total points
ID: 8127113
Just a patch:
-create a sp that contains only the statements that generates the warning 'duplicates..'. In a second sp call the first one, and after that, in this second sp return the result.....If you try this , please let me know the results.
The set nocount on will not disable the warning....
thx,
    xenon
0
 
LVL 4

Author Comment

by:Jigit
ID: 8127238
adathelad, it doesn't help.

xenon_je, actually, that's what I've done from the beginning and it doesn't work either.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:Jigit
ID: 8127286
adathelad, it doesn't help.

xenon_je, actually, that's what I've done from the beginning and it doesn't work either.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8127300
From what I've managed to find on the net, I don't think there is a way to ignore this error. A quote I found:

IGNORE_DUP_KEY
This is an option on the create index command, which will cause SQL Server to discard rows where a duplicate of the columns in the specified index already exists. Use this option with care--you have to be certain that you do really need to do this. Also remember that the results of using this option can easily be misinterpreted as an error by end users.

The next question I guess is: Do you have to use this option?
0
 
LVL 4

Author Comment

by:Jigit
ID: 8127756
adathelad, it doesn't help.

xenon_je, actually, that's what I've done from the beginning and it doesn't work either.
0
 
LVL 4

Author Comment

by:Jigit
ID: 8127778
If I don't use this option INSERT statement fails when I insert duplicates. I have already found a workaround, but I'll let you guys think about it.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8127986
You should perform validation before you attempt the insert. If a duplicate exists, then you should handle it as appropriate. This way, you will not even be attempting to insert a duplicate, therefore no SQL error will be raised.

0
 
LVL 4

Author Comment

by:Jigit
ID: 8127998
If I don't use this option INSERT statement fails when I insert duplicates. I have already found a workaround, but I'll let you guys think about it.
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 8135377
I encountered something like this in this week (but not with asp).
A possible solution (is a patch in fact):
put a SET NOCOUNT OFF before the inserts that generates the duplicates, and after them you can set back the nocount if you want to...
If this solves the error please let me know.
   xenon
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8135779
What about this:
SET ANSI_WARNINGS OFF
0
 
LVL 4

Author Comment

by:Jigit
ID: 8137560
Doesn't help either.
0
 

Expert Comment

by:CleanupPing
ID: 9275921
Jigit:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 4

Author Comment

by:Jigit
ID: 9295411
I think this question shall be closed, since nobody answered the correct answer and I found an alternative solution by myself.

Jigit
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 9308940
can you post the solution you found, plz?!
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11093133
No comment has been added to this question in more than 252 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: adathelad http:#8126697, xenon_je http:#8127113

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer

0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question