Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How can one ignore errors in a stored procedure?

Posted on 2003-03-13
17
Medium Priority
?
533 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
  • 7
  • 3
  • 3
  • +3
16 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
Technology Partners: 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

571 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