http://www.sqlmag.com/authors/author/author/5297693/rsscomment/5297693en-USFri, 25 May 2012 11:00:14 GMTFri, 25 May 2012 11:00:14 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 23 Feb 2012 15:13:05 GMT
Thanks for the SP. It works great. But I don't think this works if there are multiple step_id created within a single job_id internally and you are looking for the 3rd message in the list of 4 messages. For example: All the messages are under the same step_id=1. I am mainly interested in the 3rd message which is " *** Error: The copy destination directory 'd:\xxx\xxx' does not exist.". But when it emails me, it only gives me the last one which in this case is "Executed as user: CRTNT\crt-sql-01. The step failed." 37539598 F24AFBD0-87F8-4871-9681-08626E8B635A 1 Log shipping copy job step. 0 0 Microsoft (R) SQL Server Log Shipping Agent ----- START OF TRANSACTION LOG COPY ----- 37539599 F24AFBD0-87F8-4871-9681-08626E8B635A 1 Log shipping copy job step. 0 0 2012-02-23 15:30:03.73 Starting transaction log copy. Secondary ID: 2012-02-23 15:30:03.74 Retrieving copy settings. Secondary ID: '92833699-d6cb-47e8-ac6f-f11bbdab24f6' 2012-02-23 15:30:03.74 Retrieved copy settings. 37539600 F24AFBD0-87F8-4871-9681-08626E8B635A 1 Log shipping copy job step. 0 0 2012-02-23 15:30:03.76 *** Error: The copy destination directory 'd:\xxx\xxx' does not exist.(Microsoft.SqlServer.Management.LogShipping) *** 37539601 F24AFBD0-87F8-4871-9681-08626E8B635A 1 Log shipping copy job step. 0 0 2012-02-23 15:30:03.94 ----- END OF TRANSACTION LOG COPY ----- Exit Status: 1 (Error) 37539602 F24AFBD0-87F8-4871-9681-08626E8B635A 1 Log shipping copy job step. 0 0 Executed as user: CRTNT\crt-sql-01. The step failed. Any ideas? Thanks for your help ]]>ShettyTechThu, 23 Feb 2012 15:13:05 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorFri, 18 Nov 2011 11:54:20 GMT
The procedure uses the msdb system tables to query the detailed error message. So you need to ensure that you are logging the job step output to the log table, in order to receive the emails. ]]>jameel_ahmedFri, 18 Nov 2011 11:54:20 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/clr-stored-procedure-finds-broken-objects#commentsAnchorWed, 17 Nov 2010 20:40:28 GMT
This is very cool! The concept explanation and code example is simple and makes it easy to understand the idea you are presenting. Finding out about broken objects using this method can be used during the TFS Build process as a simple smoke test.

I have seen something like this before, but it was not through a clr object type. I like this approach better and can be used for multiple databases on the server.

Great article and example!]]>Miles CarpenterWed, 17 Nov 2010 20:40:28 GMThttp://www.sqlmag.com/article/tsql3/clr-stored-procedure-finds-broken-objects#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 03 Jun 2010 00:25:02 GMT
I do not get any mail from this job.

W2003X64
SQL2008X64 SP1

EXEC spDBA_job_notification 0xF7DA961B4BBB6845950E31CBE3C4DDB9
That is what I found in trace..

any idea?

]]>RichardThu, 03 Jun 2010 00:25:02 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 03 Jun 2010 00:14:31 GMT
I do not get any mail from this job.

W2003X64
SQL2008X64 SP1

EXEC spDBA_job_notification 0xF7DA961B4BBB6845950E31CBE3C4DDB9
That is what I found in trace..

any idea?

]]>RichardThu, 03 Jun 2010 00:14:31 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/querying-active-directory-records#commentsAnchorFri, 18 Sep 2009 12:55:42 GMT
Please change ADsDSObject to ADsDSOObject, and give it a try.]]>JameelFri, 18 Sep 2009 12:55:42 GMThttp://www.sqlmag.com/article/tsql3/querying-active-directory-records#commentsAnchorhttp://www.sqlmag.com/article/tsql3/querying-active-directory-records#commentsAnchorFri, 18 Sep 2009 08:16:00 GMT
I liked your article and I am trying using linked server. I ran this command Exec sp_addlinkedserver ’ADSI’,’Active Directory Services 2.5’, ’ADsDSObject’,’adsdatasource’ and then ran the Select command, I am getting following error message Msg 7403, Level 16, State 1, Line 4 The OLE DB provider "ADsDSObject" has not been registered. Please Help. Thanks Shafiq]]>shafiqm@gmail.comFri, 18 Sep 2009 08:16:00 GMThttp://www.sqlmag.com/article/tsql3/querying-active-directory-records#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 23 Jul 2009 16:36:45 GMT
I checked with Jameel and you’re correct. He sent along a corrected version of spDBA_job_notification.sql, which is now in the 96056.zip file. Thanks for letting us know about this! Karen Bemowski, senior editor, SQL Server Magazine, Windows IT Pro]]>KarenThu, 23 Jul 2009 16:36:45 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorWed, 22 Jul 2009 10:46:25 GMT
This is a very useful article. I also like to mention there’s syntax error when compiling stored procedure spDBA_job_notification.sql: Msg 156, Level 15, State 1, Procedure spDBA_job_notification, Line 68 Incorrect syntax near the keyword ’if’. Thanks. David.]]>DavidWed, 22 Jul 2009 10:46:25 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorMon, 28 Jul 2008 04:07:33 GMT
hi jameel i still have error "Error converting data type nvarchar to uniqueidentifier. [SQLSTATE 42000] (Error 8114). The step failed" what can i do ?? iam using: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)]]>MMon, 28 Jul 2008 04:07:33 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorWed, 30 Apr 2008 14:04:02 GMT
SQL Version: Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86) Mar 3 2007 18:40:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) I get this error message: Unable to start execution of step 2 (reason: line(1): Syntax error). The step failed. I have "Replace tokens for all job responses to alerts" checked and restarted agent. tried with it on and off. Still no luck :(]]>manitWed, 30 Apr 2008 14:04:02 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorWed, 30 Apr 2008 11:35:20 GMT
fixed syntax error: -- Build the Email Body set @Body = @Body + ’Step name= ’ + @step_name + @CrLf + ’DB Name = ’ + convert(varchar(50), ISNULL(@DBname,’)) + @CrLf + ’Run Date = ’ + convert(varchar(50),@run_datetime ) + @CrLf]]>manitWed, 30 Apr 2008 11:35:20 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorFri, 07 Mar 2008 09:32:27 GMT
hey i think this is a superb idea. unfortunately when creating the SP i get an error. Msg 156, Level 15, State 1, Procedure spDBA_job_notification, Line 68 Incorrect syntax near the keyword ’if’. any thoughts?]]>DonFri, 07 Mar 2008 09:32:27 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 11 Oct 2007 19:13:34 GMT
Never mind my last comment. I found the fix in your article. $(ESCAPE_NONE(JOBID))]]>SIDNEYThu, 11 Oct 2007 19:13:34 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 11 Oct 2007 19:10:22 GMT
I am using SQL 2005 SP2 and this is the error I am getting-- The job step contains tokens. For SQL Server 2005 Service Pack 1 or later, you must use the appropriate ESCAPE_xxx macro to update job steps containing tokens before the job can run. Thanks in advance]]>SIDNEYThu, 11 Oct 2007 19:10:22 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorFri, 13 Jul 2007 11:25:52 GMT
USE [tempdb] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[sptoken_test]’) AND type in (N’P’, N’PC’)) DROP PROCEDURE [dbo].[sptoken_test] GO create PROCEDURE [dbo].[sptoken_test] @job_id varchar(200) as /***************************************************************************************************** Note: I have am using a varchar column instead of uniqueidentifier, only for testing purpose. In RTM, tmpAgentToken.value=’JOBID’ In SQL2005 SP1, tmpAgentToken.value= varchar representation of a uniqueidentifier value, this will be in an unreadable format. you will need to change the sproc parameter, and the tables datatype to uniqueidentifier to be able to read the value. In SQL2005 SP2, tmpAgentToken.value= varchar representation of a uniqueidentifier value, this will be in an unreadable format. This is how I found that Agent Job token does not work in SQL 2005 RTM release. I haven’t tried all Agent token on RTM, I only tried the JOBID token. *****************************************************************************************************/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[tmpAgentToken]’) AND type in (N’U’)) drop table tmpAgentToken CREATE TABLE tmpAgentToken (value varchar(200) null,create_date datetime default getdate() not null) insert into tmpAgentToken (value) values ( @job_id ) GO]]>JameelFri, 13 Jul 2007 11:25:52 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorFri, 13 Jul 2007 11:25:27 GMT
Hi tarehart1 & vkomarovsky & All, Here is the Solution to: Error converting data type nvarchar to uniqueidentifier on SQL2005 RTM (9.0.1399). It seems, eventhough [JOBID] token is documented in Books Online as being supported in SQL2005 RTM, it is not. JOB Tokens seem to only work on SQL 2005 SP1, SP2. I only tested one token, namely JOBID. So RTM users will need to upgrade to SQL2005SP1, or SQL2005SP2. I created a simple stored procedure to test this case, and a simple job with a job step that calls: on SQL2005 RTM : EXEC tempdb..sptoken_test [JOBID] on SQL2005 SP1/SP2: EXEC tempdb..sptoken_test $(ESCAPE_NONE(JOBID)) after the job ran, I queried tempdb..tmpAgentToken for the values that is being passed from agent to TSQL. TSQL script to follow in next post:]]>JameelFri, 13 Jul 2007 11:25:27 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 12 Jul 2007 15:41:18 GMT
Hi tarehart1 & vkomarovsky, I was able to regenerate this on SQL 2005 RTM and i am looking into this issue.]]>JameelThu, 12 Jul 2007 15:41:18 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 12 Jul 2007 08:03:43 GMT
When running the ’EXEC spDBA_job_notification [JOBID]’ step, I am getting the error message of ’Error converting data type nvarchar to uniqueidentifier. [SQLSTATE 42000] (Error 8114). The step failed.’ on our SQL Server 9.0.1399 instance. Any suggestions as to what may be causing this error message to be generated?]]>tarehart1Thu, 12 Jul 2007 08:03:43 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorWed, 11 Jul 2007 10:15:18 GMT
I cannot download zip file with code for this article. I get 404 error ’Page not found’.]]>kHemoWed, 11 Jul 2007 10:15:18 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorMon, 09 Jul 2007 13:37:39 GMT
Hi Deanna, SQLJob_Create.sql works on both SQL 2000 + SQL2005. But on SQL 2000, you need to modify the fail Job step to use: EXEC spDBA_job_notification [JOBID] instead of EXEC spDBA_job_notification $(ESCAPE_NONE(JOBID)) DBMail_Setup_sql is for SQL 2005 only. for SQL 2000 you will need to setup SQL Mail, refer to Books Online. spDBA_job_notification.sql This can be created on SQL 2000. but you will need to change the send email call from msdb.dbo.sp_send_dbmail (SQL 2005 specific) to use SQL 2000 SQL Mail procedure msdb..xp_sendmail I have successfully tested spDBA_job_notification procedure on SQL 2000 SP4: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: ) Can you tell the exact error message you are getting. Thank you, Jameel.]]>JameelMon, 09 Jul 2007 13:37:39 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorFri, 06 Jul 2007 13:36:59 GMT
I am trying to use these scripts and they are not working. Should they work in this version of SQL Server? Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) SP4 Enterprise Edition Thanks, Deanna]]>DeannaFri, 06 Jul 2007 13:36:59 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 05 Jul 2007 13:33:36 GMT
Hi; This is very good article, I just want to mention, the uploaded code, the spDBA_job_Notification is giving one syntax error when you run, It might be useful for others. set @Body = @Body + ’Step name= ’ + @step_name + @CrLf + ’DB Name = ’ + convert(varchar(50), ISNULL(@DBname,’)) + @CrLf + ’Run Date = ’ + convert(varchar(50),@run_datetime ) + @CrLf One close braket was missing in ISNULL function. Thanks]]>VICTORThu, 05 Jul 2007 13:33:36 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorThu, 05 Jul 2007 11:29:10 GMT
In the spDBA_job_notifications.sql script I’m getting the following error: Msg 156, Level 15, State 1, Procedure spDBA_job_notification, Line 68 Incorrect syntax near the keyword ’if’. Here’s the lines: if (@Err_severity<>0) set @Body = @Body + ’Severity = ’ + convert(varchar(10),@Err_severity) + @CrLf]]>RobertThu, 05 Jul 2007 11:29:10 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorTue, 03 Jul 2007 12:46:44 GMT
Which version of SQL Server and Service Pack are you using? I will need to regenerate the error to help you out.]]>JameelTue, 03 Jul 2007 12:46:44 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorhttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchorMon, 02 Jul 2007 10:04:28 GMT
I tried it for "before SP1" SQL Server 2005 using the command exec spDBA_job_notification [JOBID] and it failed with an error Error converting data type nvarchar to uniqueidentifier. [SQLSTATE 42000] (Error 8114). The step failed. Looks like something breaks when JOBID parameter is being passed to SP.]]>vitalyMon, 02 Jul 2007 10:04:28 GMThttp://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures#commentsAnchor