On Error Resume Next Sql Server 2008
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the sql server on error continue workings and policies of this site About Us Learn more about sql server try catch resume Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions sql server ignore error and continue Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other.
Sql Server Ignore Errors Stored Procedure
Join them; it only takes a minute: Sign up SQL Server 'Resume Next' Equivalent up vote 1 down vote favorite I'm working on a project in VB.net which takes large text files containing T-SQL and executes them against a local SQL database, but I've hit a problem in regards to error handling. I'm using the following technologies : VB.net sql try catch ignore error Framework 3.5 SQL Express 2005 The SQL I'm trying to execute is mostly straight-forwards but my app is completely unaware of the schema or the data contained within. For example : UPDATE mytable SET mycol2='data' WHERE mycol1=1 INSERT INTO mytable (mycol1, mycol2) VALUES (1,'data') UPDATE mytable SET mycol2='data' WHERE mycol1=2 INSERT INTO mytable (mycol1, mycol2) VALUES (1,'data') UPDATE mytable SET mycol2='data' WHERE mycol1=3 The above is a sample of the sort of thing I'm executing, but these files will contain around 10,000 to 20,000 statements each. My problem is that when using sqlCommand.ExecuteNonQuery(), I get an exception raised because the second INSERT statement will hit the Primary Key constraint on the table. I need to know that this error happened and log it, but also process any subsequent statements. I've tried wrapping these statements in TRY/CATCH blocks but I can't work out a way to handle the error then continue to process the other statements. The Query Analyser seems to behave in this way, but not when using sqlCommand.ExecuteNonQuery(). So is there a T-SQ
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On begin try sql Home » SQL Server 2008 » T-SQL (SS2K8) » Ignore Error in T-SQL Ignore Error in T-SQL Rate Topic Display Mode Topic Options Author Message C.K.ShaijuC.K.Shaiju Posted Tuesday, September 24, 2013 8:12 AM SSC Veteran Group: General Forum Members Last Login: Thursday, May 5, 2016 12:58 AM Points: 214, Visits: 707 Hi,How do we ignore an error in http://stackoverflow.com/questions/1411057/sql-server-resume-next-equivalent T-SQL? For e.g.:- The following code throw an error once the @lCounter reach at 15 and will come out. Requirement is it should go till 1000. Even if there are errors in between.DECLARE @lCounter SMALLINTDECLARE @lError SMALLINTSET @lCounter = 0WHILE (@lCounter <= 1000 )BEGIN SELECT CONVERT(VARCHAR, GETDATE(), @lCounter) SET @lCounter = @lCounter + 1ENDThanks in advance _____________________________________________One http://www.sqlservercentral.com/Forums/Topic1497887-392-1.aspx ounce of practice is more important than tonnes of dreams Post #1497887 Sean LangeSean Lange Posted Tuesday, September 24, 2013 8:20 AM SSCoach Group: General Forum Members Last Login: 2 days ago @ 3:14 PM Points: 16,067, Visits: 16,681 C.K.Shaiju (9/24/2013)Hi,How do we ignore an error in T-SQL? For e.g.:- The following code throw an error once the @lCounter reach at 15 and will come out. Requirement is it should go till 1000. Even if there are errors in between.DECLARE @lCounter SMALLINTDECLARE @lError SMALLINTSET @lCounter = 0WHILE (@lCounter <= 1000 )BEGIN SELECT CONVERT(VARCHAR, GETDATE(), @lCounter) SET @lCounter = @lCounter + 1ENDThanks in advanceYou can't ignore an error. You can however handle it using Try/Catch.I hope your code is just for an example of how to force an error because a loop is not very efficient in sql. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to
SQL Server experts to answer whatever question you can come up with. Our new SQL http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31176 Server Forums are live! Come on over! We've restricted the ability http://www.tek-tips.com/viewthread.cfm?qid=461823 to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2000 Forums Transact-SQL (2000) On Error Resume Next sql server in SQL Reply to Topic Printer Friendly Author Topic ratcho Starting Member Canada 18 Posts Posted-12/09/2003: 11:26:39 Hello to all!I need something like ON ERROR Resume Next while using nested stored procedures.I have 2 stored procedures: spA calls spB. CREATE PROCEDURE spAAS....(some code)exec spB...(some code)GOI want to be sure that spA will continue even sql server ignore if spB produces errors.How can I do this!Please help me! nr SQLTeam MVY United Kingdom 12543 Posts Posted-12/09/2003: 11:34:55 >> I want to be sure that spA will continue even if spB produces errors.You can never guarantee that this will be possible as a lot of errors abort the batch.Error handling is implemented by checking @@error after each statement and acting accordingly.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. jsmith8858 Dr. Cross Join USA 7423 Posts Posted-12/09/2003: 12:03:09 You would need to ensure spA has proper error handling and returns a code indicating success or failure. Then, from that code, you would determine if spA succeded. As Nigel says, if you don't trap errors in spA, the entire thing may terminate.- Jeff ratcho Starting Member Canada 18 Posts Posted-12/09/2003: 12:43:09 Thanks a lot guys!As I understood I have to write something like this:---1st solution---------CREATE PROCEDURE spAAS
FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft SQL Server: Programming Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesMicrosoft SQL Server: Programming Forum On error resume next thread183-461823 Forum Search FAQs Links MVPs On error resume next On error resume next shiggyshag (Programmer) (OP) 30 Jan 03 08:29 HIIN a stired procedure I am doing an append but i will have duplicate indexes so it crashes out.Can I do an On error resume next so it skips the duplicate but continues?Cheers RE: On error resume next tlbroadbent (MIS) 30 Jan 03 10:40 No. There is no On Error Resume Next in T-SQL. Duplicate inserts cause a FATAL error and the SP will terminate. You can avoid inserting duplicates with a simple WHERE clause in the INSERT statement.Insert YourTable(