Error In Sqlserver2000
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more sql server 2000 if error about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users
Ms Sql 2000 Error
Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping sql server 2000 error handling each other. Join them; it only takes a minute: Sign up Proper use of @@Error in SQL Server 2000 up vote 2 down vote favorite Taking any example for SQL Server 2000 documentation, or something like Use Northwind Go Alter Procedure spu_UpdateOrderDetails( @ProductID Int, @OrderId Int, @ErrorCode Int OutPut, @ErrorMessage Varchar(100) Output ) As UPDATE [Order Details] SET ProductID = @ProductID WHERE OrderID = @OrderId Set @ErrorCode = @@ERROR IF @ErrorCode <> 0 Set @ErrorMessage = 'Some error ocurred' GO Declare @MyErrorCode Int Declare @MyErrorMsg Varchar(100) Exec spu_UpdateOrderDetails 999, 10248, @MyErrorCode OutPut, @MyErrorMsg OutPut Print @MyErrorCode Print @MyErrorMsg This will cause a Foreign Key error, and I wll print the @MyErrorMsg properly. But I still get the ugly Foreign Key error. Now, if I run from within my application, it could be Delphi or Visual Studio, I still get the ugly error message, too. How am I supposed to "trap" an error, so that I show my custom error to the user? (I know newer SQL Server versions provides Try/Catch, but this is SQL Server 2000) sql-server sql-server-2000 share|improve this question asked Jun 1 '12 at 23:17 Craig Stevensson 60111133 2 Read the Bible on SQL Server 2000 error handling and see if it helps: sommarskog.se/error-handling-I.html#whathappens –mellamokb Jun 1 '12 at 23:21 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote Put it in a transaction, and roll it back it if errors, commit if success: SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE spu_UpdateOrderDetails AS DECLARE @ErrorCode int --,@myRowCount int ,@ProductID ,@OrderId --,@ErrorMessage Varchar(100) Output SET NOCOUNT ON BEGIN TRAN UPDATE [Order Details] SET ProductID = @ProductID WHERE OrderID = @OrderId SELECT @ErrorCode = @@ERROR--, @myRowCount = @@ROWCOUNT IF @ErrorCode != 0 GOTO HANDLE_ERROR COMMIT TRAN -- No Errors, so go ahead RETURN 0 HANDLE_ERROR: ROLLBACK TRAN RETURN @ErrorCode GO share|improve this answer answered Jun 2 '12 at 16:52 user1166147 1,2942917 I'm sorry, but that still returns the ugly Foreign Key error to the client. I think the answer is : there's no clean solution
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mudassar Ahmed Khan... » Get Error Description in SQL Server 2000 15 posts,Page 1 of 212»» Get Error Description in SQL Server 2000 Rate Topic Display Mode Topic Options Author Message Mudassar Ahmed KhanMudassar Ahmed Khan Posted Monday, January 12, 2009 9:45 PM Forum Newbie Group: General Forum Members Last Login: Monday, December 9, 2013 2:25 AM Points: 8, Visits: 36 Comments posted to this topic are about the item Get Error Description http://stackoverflow.com/questions/10858472/proper-use-of-error-in-sql-server-2000 in SQL Server 2000 Post #635145 philcartphilcart Posted Monday, January 12, 2009 9:52 PM SSCrazy Group: General Forum Members Last Login: Yesterday @ 8:07 PM Points: 2,709, Visits: 1,416 Looks pretty familiar.http://www.sqlservercentral.com/articles/Stored+Procedures/capturingtheerrordescriptioninastoredprocedure/1342/ Hope this helpsPhill Carter--------------------Colt 45 - the original point and click interface Australian SQL Server User Groups-My profilePhills PhilosophiesMurrumbeena Cricket Club Post #635146 Mudassar Ahmed KhanMudassar Ahmed Khan Posted Monday, January 12, 2009 10:19 PM Forum Newbie Group: General Forum http://www.sqlservercentral.com/Forums/Topic635145-1456-1.aspx Members Last Login: Monday, December 9, 2013 2:25 AM Points: 8, Visits: 36 Yes I had a look. It is similar to mine. But I have not extracted any thing from it. If I had done so why would I post the article on same site.:) Post #635151 Mark D PowellMark D Powell Posted Tuesday, January 13, 2009 10:42 AM SSCommitted Group: General Forum Members Last Login: Wednesday, September 7, 2016 11:25 AM Points: 1,609, Visits: 449 I was unable to find sp_GetErrorDesc in the resouce section. Where should (url) I be looking? A search on the procedure name returned no hits.-- Mark -- Post #635644 Mudassar Ahmed KhanMudassar Ahmed Khan Posted Tuesday, January 13, 2009 10:59 AM Forum Newbie Group: General Forum Members Last Login: Monday, December 9, 2013 2:25 AM Points: 8, Visits: 36 Its there in the Resources Section There's a link to download the file The File Name itself is the link Text sp_GetErrorDesc.sql Search for this sp_GetErrorDesc.sql Post #635663 Mudassar Ahmed KhanMudassar Ahmed Khan Posted Tuesday, January 13, 2009 11:01 AM Forum Newbie Group: General Forum Members Last Login: Monday, December 9, 2013 2:25 AM Points: 8, Visits: 36 The URL of the file ishttp://www.sqlservercentral.com/Files/sp_GetErrorDesc.sql/2268.sql Post #635666 SQLBOTSQLBOT Posted Tuesday, January 13, 2009 3:32 PM SSChasing Mays Group: General F
United States Australia United Kingdom Japan Newsletters Forums Resource Library Tech Pro Free Trial Membership Membership My Profile People Subscriptions My stuff Preferences Send a message Log Out TechRepublic Search GO Topics: CXO Cloud Big Data http://www.techrepublic.com/article/understanding-error-handling-in-sql-server-2000/ Security Innovation Software Data Centers Networking Startups Tech & Work All Topics Sections: Photos http://www.sommarskog.se/error-handling-I.html Videos All Writers Newsletters Forums Resource Library Tech Pro Free Trial Editions: US United States Australia United Kingdom Japan Membership Membership My Profile People Subscriptions My stuff Preferences Send a message Log Out Data Management Understanding error handling in SQL Server 2000 Transaction design and error handling in SQL Server 2000 is no easy task. sql server Tim Chapman provides insight into designing transactions and offers a few tips to help you develop custom error handling routines for your applications. By Tim Chapman | June 5, 2006, 12:00 AM PST RSS Comments Facebook Linkedin Twitter More Email Print Reddit Delicious Digg Pinterest Stumbleupon Google Plus Most iterative language compilers have built-in error handling routines (e.g., TRY…CATCH statements) that developers can leverage when designing their code. Although SQL sql server 2000 Server 2000 developers don't enjoy the luxury that iterative language developers do when it comes to built-in tools, they can use the @@ERROR system variable to design their own effective error-handling tools. Introducing transactions In order to grasp how error handling works in SQL Server 2000, you must first understand the concept of a database transaction. In database terms, a transaction is a series of statements that occur as a single unit of work. To illustrate, suppose you have three statements that you need to execute. The transaction can be designed in such a way so that all three statements occur successfully, or none of them occur at all. When data manipulation operations are performed in SQL Server, the operation takes place in buffer memory and not immediately to the physical table. Later, when the CHECKPOINT process is run by SQL Server, the committed changes are written to disk. This means that when transactions are occurring, the changes are not made to disk during the transaction, and are never written to disk until committed. Long-running transactions require more processing memory and require that the database hold locks for a longer period of time. Thus, you must be careful when designing long running transactions in a production environment. Here'
some extent ADO - behave when an error occurs. The other article, Implementing Error Handling with Stored Procedures, gives advice for how you should check for errors when you write stored procedures. Logically, this article is part one, and Implementing... is part two. However, you can read the articles in any order, and if you are relatively new to SQL Server, I recommend that you start with Implementing.... The article here gives a deeper background and may answer more advanced users' questions about error handling in SQL Server. Note: this article was written for SQL2000 and earlier versions. All I have for SQL 2005 is unfinished article with a section Jumpstart Error Handling. The content in this article is to some extent applicable to SQL 2005 as well, but you will have to use your imagination to map what I say to SQL 2005. The article includes a short section on TRY-CATCH. I hope to produce a complete article for error handling in SQL 2005 later on. Table of Contents: Introduction The Basics The Anatomy of an Error Message How to Detect an Error in T-SQL - @@error Return Values from Stored Procedures @@rowcount @@trancount More on Severity Levels What Happens when an Error Occurs? The Possible Actions When Does SQL Server Take which Action? Connection-termination Scope-abortion Statement-termination and Batch-abortion Trigger Context Errors in User-Defined Functions Control Over Error Handling SET XACT_ABORT ARITHABORT, ARITHIGNORE and ANSI_WARNINGS RAISERROR WITH NOWAIT Duplicates Using Linked Servers Retrieving the Text of an Error Message TRY-CATCH in SQL2005 Client-side Error Handling DB-Library ODBC ADO ADO .Net Acknowledgements and Feedback Revision History Introduction In many aspects SQL Server is a very good DBMS that permits you implement powerful solutions with good performance. However, when it comes to error handling... To be blunt: error handling in SQL Server is poor. It is a patchwork of not-always-so-consistent behaviour. It's also weak in that you have fairly little control over error handling, and for advanced error handling like suppressing errors or logging errors, you must take help from the client-side. Unfortunately, depending on