How To Get Error Message In Sql Server 2000
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 about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping 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.
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 about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Retrieve Error text from SQL Server 2000 error up http://stackoverflow.com/questions/10858472/proper-use-of-error-in-sql-server-2000 vote 0 down vote favorite 2 I need help logging errors from T-SQL in SQL Server 2000. We need to log errors that we trap, but are having trouble getting the same information we would have had sitting in front of SQL Server Management Studio. I can get a message without any argument substitution like this: SELECT MSG.description from master.dbo.sysmessages MSG INNER JOIN sys.syslanguages LANG ON MSG.msglangID=LANG.msglangid WHERE http://stackoverflow.com/questions/128190/retrieve-error-text-from-sql-server-2000-error MSG.error=@err AND LANG.langid=@@LANGID But I have not found any way of finding out the error arguments. I want to see: Constraint violation MYCONSTRAINT2 on table MYTABLE7 not Constraint violation %s on table %s Googling has only turned up exotic schemes using DBCC OUTPUTBUFFER that require admin access and aren't appropriate for production code. How do I get an error message with argument replacement? asp.net sql-server error-handling share|improve this question edited Sep 24 '08 at 17:45 Joel Coehoorn 249k92440661 asked Sep 24 '08 at 16:22 Clyde 4,73254376 add a comment| 4 Answers 4 active oldest votes up vote 2 down vote In .Net, retrieving error messages (and anything output from print or raiserror) from sql server is as simple as setting one property on your SqlConnection ( .FireInfoMessageEventOnUserErrors = True) and handling the connection's InfoMessage event. The data received by .Net matches what you get in the Messages window in the SQL Server Management Studio results grid. All the code goes in the function that handles the event, and you can abstract that so that all your connections point to the same method, so there's nothing else to change in the rest of the app aside from the two lines of cod
Java Microsoft & .NET Cloud Open Source PHP Database next Developer.com Techniques Read More in Techniques » Microsoft SQL Server 2000 Error http://www.developer.com/tech/article.php/724711/Microsoft-SQL-Server-2000-Error-Messages.htm Messages March 27, 2001 By Mark Spenik, Orryn Sledge Bio » Send Email » More Articles » Tweet If you run a query and accidentally make a mistake https://www.aspfree.com/c/a/ms-sql-server/exception-handling-in-sql-server-2000-and-2005/ by entering a table that does not exist in the database, what happens? SQL Server returns an error message. Actually, SQL Server reacts to all errors in how to the same manner, whether those errors are generated by users, databases, objects, or the system. SQL Server returns a formatted error message and/or writes the error message to the error log and/or event log. Here is a quick example that executes a SQL statement to update a nonexistence table in the pubs database. The SQL statement how to get for the example is as follows: UPDATE new_authors Set author1 = "Spenik", author2 = "Sledge", title="Microsoft SQL Server DBA Survival Guide" When the statement is executed, the following error message is returned: Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'new_authors'. Post a comment Email Article Print Article Share Articles Digg del.icio.us Slashdot DZone Reddit StumbleUpon Facebook FriendFeed Furl Newsvine Google LinkedIn MySpace Technorati Twitter YahooBuzz The preceding error message demonstrates the standard message format for error messages returned by SQL Server. TIP The first thing presented in the error message is the message number, severity level, state, and line number. To most users, these numbers are just garbage to be ignored, so they skip down to the message and try to resolve the problem. In reality, the error message number is very useful for obtaining more error information. You can use the severity levels to help find errors that need to be handled. When tracking a problem, always write
facebook google twitter rss Exception Handling in SQL Server 2000 and 2005 Posted on May 24, 2006 by JagadishChaterjee This article mainly discusses and compares the features of exception handling in Microsoft SQL Server 2000 with the same features in SQL Server 2005.Basically, in Microsoft SQL Server 2000, there exists no structured exception handling. We need to dependon @@ERROR for any errors that occur.Microsoft SQL Server 2005 has beenenhanced in such a way that developers program more powerful and error resistant SQL codewithstructured exception handling. In this article, I shall provide some samples in both SQL Server 2000 and SQL Server 2005. I shall also give explanations on both of the approaches by comparing each of them. I've tried to keep thisarticle looks as simple as possible to get beginners off to a good start. I am assuming that the readers of this article will have some knowledge of RDBMS along with some exposure to SQL Server 2000. Introduction to error handling Before going to exception handling, let us first determineall the possible ways to get errors. Errors may occur in T-SQL (of course not only in T-SQL) in several possible ways, including hardware failures, network failures, bugs in programs, out of memory and for several other reasons. We may not know which error has been raised at what moment. But we need to handle all such errors and provide some meaningful messages to the user (instead of making the user horrified with error messages that are impossible to understand). An exception is generally a runtime error which gets raised by SQL Server runtime when a T-SQL block is in the process of execution. Handling the exception is something like trapping the error (or exception) and inserting that error into the error_log table including date, error message, and other details. Storing error messages in the error_log table makes it easy to trace for future maintenance. It doesn’t mean that errors are in the table only for maintenance; we can take certain actions (programmatically) when an error occurs. Error handling is a very monotonous task and we should make it as simple as possible. Iferror handling is too complex, bugs might creep into the error handling and should be tested after each statement. Another special case is the use of transactions. We need to issue a “ROLLBACK TRANSACTION”