November 19, 2007 at 7:14 am
Hi experts,
I am trying to develop the capability to use xp_test_mapi_profile and xp_sendmail but I am having no luck. However my SQL Mail agent can send mail. Below is the error and the TSQL that I am trying to develop. Outlook opens on the server and the mailbox is for Administrator. If the TSQL is correct (it parses fine) can you make any suggestions about what to look for in the Outlook setup on the SQL Server.
What other methods do you use for getting information from stored procedures? I have a job that when run against db 'A' it takes 8.5 hours but when run against db pwProgressive it is still running after 13 hours. I need to know what step (there are ten stored procedures being executed in the job) the job is on. Individually I have run all the steps successfully. I have recreated the job and the stored procedures. This is why I want to have each stored procedure send me an email that it completed so I can tell what step has been done.
While waiting for your expert advice about the right way to debug my problem I am going to make a temporary table in db pwProgressive and modify the stored procedures to insert information about what has just completed.
Hope
>>>>>>Error Message<<<<<<<<
Server: Msg 18030, Level 16, State 1, Line 0
xp_test_mapi_profile : Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
>>>>>>>TSQL under development<<<<<<<
DECLARE @recipients VARCHAR(255)
DECLARE @subject VARCHAR(50)
DECLARE @profilename SYSNAME
DECLARE @message VARCHAR(200)
DECLARE @rc VARCHAR(2000)
SELECT @recipients = 'Hope Schlais'
SELECT @subject = 'SQL Server Job Status - z_pes_sync_cl_mast_2007'
SELECT @profilename = 'Administrator'
EXEC @rc = pwProgressive.dbo.z_pes_sync_cl_mast_2007
IF @rc = 0
SELECT @message = 'JOB RUN: pwProgressive.dbo.z_pes_sync_cl_mast_2007 WAS RUN ON put_date_here AT put_time_here' +
'DURATION: put_duration_here' +
'STATUS: Succeeded'
ELSE
SELECT @message = 'JOB RUN: pwProgressive.dbo.z_pes_sync_cl_mast_2007 WAS RUN ON put_date_here AT put_time_here' +
'DURATION: put_duration_here' +
'STATUS: Failed ' + @@ERROR
EXEC @rc = master.dbo.xp_test_mapi_profile @profilename
IF @rc = 0
EXEC master.dbo.xp_sendmail @recipients, @subject = @subject, @message = @message, @query = @rc,
@attachments = 'false', @width = 2000, @no_output = 'true'
--ELSE
--try net_send
November 19, 2007 at 7:53 am
Continuing...
I found the following in the 'Scripts' section of this forum. Can anyone tell me where the table 'inserted' is? Thank you.
Hope
>>>>>>>Found this in the 'Scripts' section<<<<<<<<<
create trigger trg_stepfailures
on sysjobhistory
for insert
as
declare @strMsg varchar(400),
@strRecipient varchar(128)
set @strRecipient = 'ken.singer@sagepub.com'
if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg =
convert(char(10),'Server') + char(58) + @@servername +
char(10) +
convert(char(10),'Job') + char(58) + convert(varchar(50), sysjobs.name) +
char(10) +
convert(char(10),'Step') + char(58) + convert(varchar(50), inserted.step_name)+
char(10) +
convert(char(10),'Message') + char(58) + convert(varchar(150), inserted.message)
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
raiserror (@strMsg, 16, 10) with log
exec master.dbo.xp_sendmail @recipients = @strRecipient,
@message = @strMsg,
@subject = 'Job Failure'
end
November 19, 2007 at 9:20 am
Continuing...
Found the 'inserted' table and now know a little bit about it and the 'deleted' table. I also found in the msdb database 'sysjobhistory', 'sysjobs', 'sysjobsteps' and many other potentially helpful tables. I now know which step is having a problem but not yet why.
I could still use some advice on how to best handle step success/failure reporting.
November 20, 2007 at 5:29 pm
1) Does the SQL Server service account match the SQL Server Agent service account?
2) If so, have you followed the steps in Books Online under the section, "Configuring Mail Profiles?" The reason I ask is SQL Mail and SQL Agent Mail are configured differently within SQL Server. Having one set up doesn't mean the other is.
2) In cases like these you're probably better off writing to a log table and then querying that log table to see where things are. A failure sending the email will cause the job step to fail, which will cause the job to fail (assuming default workflow). This isn't what you want.
K. Brian Kelley
@kbriankelley
November 21, 2007 at 6:42 am
Thank you Brian this is just the amount of direction/confirmation I needed!
I will do exactly as you suggest - create a (permanent) log table in my database with some stored procedures to help standardize the method of row insertion.
2) In cases like these you're probably better off writing to a log table and then querying that log table to see where things are. A failure sending the email will cause the job step to fail, which will cause the job to fail (assuming default workflow). This isn't what you want.
Warm regards,
Hope
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply