Mssql Stored Procedure Error Log
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the sql server log stored procedure execution workings and policies of this site About Us Learn more about sql server stored procedure logging Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions oracle stored procedure logging 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 Transaction Log
Join them; it only takes a minute: Sign up SQL Server error logging from a Stored Procedure up vote 3 down vote favorite Our application is Windows Service (native .EXE written in C++) that calls stored procedures in SQL Server. In most cases errors in stored procedures (in 90% of the cases these errors mean something was wrong in sql server stored procedure execution history our business logic) are re-thrown as exception and caught by our service. They are then logged in Application Event Log on the computer where our service is running. However, I now have a need to log some of the errors on the SQL Server itself within a stored procedure. Following the paradigm we use for our service I think I can use xp_logevent to save error information in the event log. Is this a recommended approach to log SQL Server errors? FWIW I use SQL Server 2008 sql-server share|improve this question edited Apr 21 '11 at 19:26 John Saunders 138k20178323 asked Apr 21 '11 at 19:22 Joe Schmoe 3352422 add a comment| 4 Answers 4 active oldest votes up vote 6 down vote accepted The How To You can always use RAISEERROR() WITH LOG. Logs to both Windows Application log and the SQL error log.Please note that severity level is key here. There are some limitations and security considerations, but you get some other features also. More details in BOL: http://msdn.microsoft.com/en-us/library/ms178592.aspx The Should you M
| Related Tips: More > Stored Procedures Problem In many environments there is a lack of consistent xp_readerrorlog logging from stored procedures - if there is any logging at
Sql Server Logging Options
all. In general, we tend to leave it up to the application to record any errors,
Sql Server Audit Stored Procedure Execution
but in systems with many different applications, it can be tedious to collect exception information and figure out which stored procedures are having issues. And more generically, it http://stackoverflow.com/questions/5748779/sql-server-error-logging-from-a-stored-procedure is difficult to determine which stored procedures are being used at all, without running a server-side trace for a significant amount of time. Solution By creating a central logging system, you can add both error and general usage logging to stored procedures with minimal intrusion. On my systems I create a separate database called Utility https://www.mssqltips.com/sqlservertip/2003/simple-process-to-track-and-log-sql-server-stored-procedure-use/ which contains general purpose and system-wide items like this. So if you don't have a generic database for this type of purpose, you can create one: CREATE DATABASE Utility; GO Now, what kind of things would you be interested in logging from your stored procedures? For a starting list: Date/time DatabaseID ObjectID Fully-qualified procedure name Line number Error message Any additional info you want to pass Why both DatabaseID + ObjectID and fully qualified procedure name? Because procedures can be dropped and re-created, dropped entirely, or re-named. I usually add the extra column for additional info in case there is other context I want to pass and log (for example, if I am using RAISERROR manually), even if in most cases the value will be NULL. So here is the DDL for the table you can create to capture these things: USE Utility; GO CREATE TABLE dbo.ProcedureLog ( LogDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, DatabaseID INT, ObjectID INT, ProcedureName NVARCHAR(400), ErrorLine INT, ErrorMess
resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards https://msdn.microsoft.com/en-us/library/ms186244.aspx Events Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers Samples Retired content We’re sorry. The content you requested has been removed. http://www.codeproject.com/Articles/363681/Easy-way-to-track-Stored-Procedure-errors-in-MS-SQ You’ll be auto redirected in 1 second. Transact-SQL Reference (Database Engine) System Stored Procedures (Transact-SQL) General Extended Stored Procedures (Transact-SQL) General Extended Stored Procedures (Transact-SQL) xp_logevent stored procedure (Transact-SQL) xp_logevent (Transact-SQL) xp_logevent (Transact-SQL) xp_cmdshell (Transact-SQL) xp_enumgroups (Transact-SQL) xp_grantlogin (Transact-SQL) xp_logevent (Transact-SQL) xp_loginconfig (Transact-SQL) xp_logininfo (Transact-SQL) xp_msver (Transact-SQL) xp_revokelogin (Transact-SQL) xp_sprintf (Transact-SQL) xp_sqlmaint (Transact-SQL) xp_sscanf (Transact-SQL) TOC Collapse the table of content Expand the table of content This documentation is archived and is not being maintained. This documentation is archived stored procedure execution and is not being maintained. xp_logevent (Transact-SQL) Other Versions SQL Server 2012 Logs a user-defined message in the SQL Server log file and in the Windows Event Viewer. xp_logevent can be used to send an alert without sending a message to the client.Applies to: SQL Server (SQL Server 2008 through current version). Transact-SQL Syntax ConventionsSyntax Copy xp_logevent { error_number , 'message' } [ , 'severity' ] Argumentserror_number Is a user-defined error number larger than 50,000. The maximum value is 2147483647 (2^31 - 1).' message ' Is a character string with a maximum of 2048 characters.' severity ' Is one of three character strings: INFORMATIONAL, WARNING, or ERROR. severity is optional, with a default of INFORMATIONAL.Return Code Values0 (success) or 1 (failure)Result Setsxp_logevent returns the following error message for the included code example:The command(s) completed successfully.RemarksWhen you send messages from Transact-SQL procedures, triggers, batches, and so on, use the RAISERROR statement instead of xp_logevent. xp_logevent does not
Articles Technical Blogs Posting/Update Guidelines Article Help Forum Article Competition Submit an article or tip Post your Blog quick answersQ&A Ask a Question about this article Ask a Question View Unanswered Questions View All Questions... Linux questions C# questions ASP.NET questions SQL questions fabric questions discussionsforums All Message Boards... Application Lifecycle> Running a Business Sales / Marketing Collaboration / Beta Testing Work Issues Design and Architecture ASP.NET JavaScript C / C++ / MFC> ATL / WTL / STL Managed C++/CLI C# Free Tools Objective-C and Swift Database Hardware & Devices> System Admin Hosting and Servers Java .NET Framework Android iOS Mobile SharePoint Silverlight / WPF Visual Basic Web Development Site Bugs / Suggestions Spam and Abuse Watch features Competitions News The Insider Newsletter The Daily Build Newsletter Newsletter archive Surveys Product Showcase Research Library CodeProject Stuff communitylounge Who's Who Most Valuable Professionals The Lounge The Insider News The Weird & The Wonderful The Soapbox Press Releases Non-English Language > General Indian Topics General Chinese Topics help What is 'CodeProject'? General FAQ Ask a Question Bugs and Suggestions Article Help Forum Site Map Advertise with us About our Advertising Employment Opportunities About Us Articles » Database » Database » SQL Server Technical BlogView BlogBrowse CodeStatsRevisions (4)Alternatives Comments (1) Add your ownalternative version Tagged as SQLWindowsSQL-Server-2008Dev Stats 17K views8 bookmarked Posted 10 Apr 2012 Easy way to track Stored Procedure errors in MS SQL Server 2008 Sadeque Sharif, 12 Apr 2012 CPOL 1.00 (1 vote) 1 2 3 4 5 1.00/5 - 1 voteμ 1.00, σa 5.00 [?] Rate this: Please Sign up or sign in to vote. Easily trace all errors of Stored Procedures in MS SQL Server. You can easily trace all errors of Stored Procedures in MS SQL Server. To do this, first create a table called Error. CREATE TABLE [dbo].[Error]( [iAutoID] [int] IDENTITY(1,1) NOT NULL, [dErrorDate] [datetime] NOT NULL, [vErrorNumber] [nvarchar](max) NULL, [vErrorSeverity] [nvarchar](max) NULL, [vErrorState] [nvarchar](max) NULL, [vErrorProcedure] [nvarchar](max) NULL, [vErrorLine] [nvarchar](max) NULL, [vErrorMessage] [nvarchar](max) NULL ) ON [SECONDARY] GO ALTER TABLE [dbo].[Error] ADD CONSTRAINT [DF_Error_dErrorDate] DEFAU