Error with the database backup Failed Script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error with the database backup Failed Script

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-29 : 15:37:27

Hello

I have a script which sends mail to the dba mail box when the databse backup fails. I am getting an error near the statments of:
EXEC master..xp_sendmail
@recipients = '<Eamils>' ,@message = ' Failed Job(s) on the Server <Server_Name>'
,@subject = 'Failed Job(s) Notification',@query = 'Select [Job Name],[Failure Date],[Step #],[Step Name] from DB_Name..Job_Tracking'
,@attach_results = 'TRUE',@width = 300

The error I am encountring is : master.dbo.xp_smtp_sendmail

Can you help me out with this.

The whole script is as below:

Create Procedure Usp_Failed_Jobs_Notification
AS
Declare @Max_JobFailed_Error_Dt Datetime,
@Max_JobFailed_Error_Dt_Dup Datetime
If not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Job_Tracking]') )
CREATE TABLE dbo.Job_Tracking ([job Name] varchar (80) ,[Failure Date] datetime, [Step #] varchar (5),[Step Name] varchar (80) )
If not exists (select * from dbo.sysobjects where id = object_id(N'#Job_Tracking_dup') )
CREATE TABLE dbo.#Job_Tracking_dup ([job Name] varchar (80) NULL ,[Failure Date] datetime NULL ,[Step #] varchar (5) NULL ,[Step Name] varchar (80) NULL )
if (Select Count(*) from Job_Tracking) = 0
Begin
Insert Job_Tracking
SELECT SUBSTRING(msdb..sysjobs.name,1,80) 'Job Name',SUBSTRING(CAST(msdb.sysjobHistory.run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(msdb.sysjobHistory.run_date AS CHAR(8)),2) + '/' + LEFT(CAST(msdb.sysjobHistory.run_date AS CHAR(8)),4) + ' '+ LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) 'Failure Date',
msdb..sysjobHistory.step_id ,
msdb..sysjobHistory.step_name
FROM msdb..sysjobhistory
JOIN msdb..sysjobs
ON msdb..sysjobHistory.job_id = msdb..sysjobs.job_id
WHERE msdb..sysjobHistory.step_id != 0
And msdb..sysjobHistory.run_status != 1
And msdb..sysjobs.name <> 'Failed Job(s) Notification'
EXEC master..xp_sendmail
@recipients = '<Eamils>' ,@message = ' Failed Job(s) on the Server <Server_Name>'
,@subject = 'Failed Job(s) Notification',@query = 'Select [Job Name],[Failure Date],[Step #],[Step Name] from DB_Name..Job_Tracking'
,@attach_results = 'TRUE',@width = 300
End
Else
Begin
Insert #Job_Tracking_dup
SELECT SUBSTRING(msdb..sysjobs.name,1,80) ,SUBSTRING(CAST(msdb.sysjobHistory.run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(msdb.sysjobHistory.run_date AS CHAR(8)),2) + '/' + LEFT(CAST(msdb.sysjobHistory.run_date AS CHAR(8)),4) + ' '+ LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) ,
msdb..sysjobHistory.step_id ,
msdb..sysjobHistory.step_name
FROM msdb..sysjobhistory
JOIN msdb..sysjobs
ON msdb..sysjobHistory.job_id = msdb..sysjobs.job_id
WHERE msdb..sysjobHistory.step_id != 0
And msdb..sysjobHistory.run_status != 1
And msdb..sysjobs.name <> 'Failed Job(s) Notification'
End
SElect @Max_JobFailed_Error_Dt_Dup = Max([Failure Date]) from #Job_Tracking_dup
Select @Max_JobFailed_Error_Dt = Max([Failure Date]) from Job_Tracking
If @Max_JobFailed_Error_Dt_Dup > @Max_JobFailed_Error_Dt
Begin
Truncate table Job_Tracking
Insert Job_Tracking
SELECT * from #Job_Tracking_dup where
[Failure Date] > @Max_JobFailed_Error_Dt
Drop table #Job_Tracking_dup
EXEC master..xp_sendmail
@recipients = '<Emails>' ,@message = ' Failed Job(s) on the Server <Server_Name>'
,@subject = 'Failed Job(s) Notification',@query = 'Select [Job Name],[Failure Date],[Step #],[Step Name] from DB_Name..Job_Tracking',
@attach_results = 'TRUE',
@width = 300
End

Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 15:54:50

you did not post the error message you are getting.

-ec

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-29 : 16:21:57

Hello

The error that I am encountring is :

Error: unknown string parameter (5) with name @query specified , when executing :

EXEC master.dbo.xp_smtp_sendmail
@from ='testsql2000@is.depaul.edu',
@to = 'dvaddi@depaul.edu' ,@message = ' Failed Job(s) on the Server <Server_Name>'
,@subject = 'Failed Job(s) Notification',@query = 'Select [Job Name],[Failure Date],[Step #],[Step Name] from DB_Name..Job_Tracking'
,@attach_results = 'TRUE',@width = 300

Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 16:25:19

does that select you are trying to run (the @query) run on it's own from Query Analyzer?

also, you may want to edit your previous posts and remove your email unless you want an avalanche of spam.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-29 : 16:25:44

Your stored procedure is running xp_sendmail but you reference a different extended stored procedure when posting the error. So what stored procedure are you using?

Tara Kizer

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-29 : 16:36:13

sorry guys for the confusion but I am using the
dbo.xp_smtp_sendmail only in the whole of the script , I didnot make that change while posting it.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-29 : 16:39:51

Could you post the actual code then? Does xp_smtp_sendmail even allow you to pass a query to it? I know xp_sendmail does, but not sure about the smtp one.

Tara Kizer

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 16:53:37

yeah, tara hit the nail on the head. the xp_smtp_sendmail doesn't support the @query argument.

see http://sqldev.net/xp/xpsmtp.htm

-ec

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-29 : 16:53:55

I have checked and the xp_smtp_sendmail doesnot allow to pass a query. Thanks for the help.

Can I get the the stored procedure for the xp_sendmail

Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 16:55:50

quote:
Originally posted by vaddi

I have checked and the xp_smtp_sendmail doesnot allow to pass a query. Thanks for the help.

Can I get the the stored procedure for the xp_sendmail

Thanks

xp_sendmail is provided with SQL Server. Look it up in BOL for details.

btw, in the past you had to install the Outlook email client on your server in order to use xp_sendmail. I am not sure if that is still a requirement, but it might be.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-29 : 17:00:49

xp_sendmail is SQL Mail. Here's how to get it to work:

http://support.microsoft.com/kb/263556/

Tara Kizer

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-29 : 17:10:53

Hello once again

I am not using sql mail for my notifications. Can you let me know any script which sends a mail using the SMTP server when the database backup fails.

Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 17:15:15

quote:
Originally posted by vaddi

Hello once again

I am not using sql mail for my notifications. Can you let me know any script which sends a mail using the SMTP server when the database backup fails.

Thanks

the xp_smtp_sendmail extended sproc you are using will do that. You just can't have it include the resultset of a query. There is no replacement for xp_sendmail that I am aware of that allows attaching a query resultset.

If that is the feature you need, you will have to work some other method out. Maybe by storing the resultset as a textfile and then attaching the textfile as an email attachment. that is kinda kludgey though..

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-29 : 17:27:01

quote:
Originally posted by vaddi

Hello once again

I am not using sql mail for my notifications. Can you let me know any script which sends a mail using the SMTP server when the database backup fails.

Thanks

I didn't say you were using SQL Mail. You asked for the xp_sendmail stored procedure. That stored procedure is SQL Mail!

Tara Kizer

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-30 : 12:47:56

quote:

If that is the feature you need, you will have to work some other method out. Maybe by storing the resultset as a textfile and then attaching the textfile as an email attachment. that is kinda kludgey though..

Since that query looks like the resultset would be very small, I'd be tempted to just use a varchar() to concatenate all the columns with a delimiter, ending each row with a CR/LF, and use that as the body of the message.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources