October 28, 2004 at 9:48 pm
I have found much information in and out of SQL Server, tried many and I am still unable to get SS to email me if a job fails or even if it succeeds! First I found information that I needed a MAPI program such as Outlook installed. Did that and put in an admin email address that sends and receives mail from the Outlook account. When I test it in SQL Server Agent mail, it tells me that it is unable to use the .pst file due to being used.... which it is not! The mail account is set up in the same user that the Sql Server Agent service is running in. Why can't Sql Server mail be easy like other programs? I have found many users that have similar problems to mine and I don't think anyone has gotten it to work. The last one I read told them it isn't the best idea using a MAPI program for mail anyway and directed them to buy one - wasn't on sqlservercentral by the way. It is all set up in Sql, but seems to be a carrot on a string.......If anyone has got any answers, appreciate them sharing. Thanks.
October 29, 2004 at 12:27 am
See, if this helps:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;315886
http://support.microsoft.com/default.aspx?scid=kb;EN-US;312839
http://support.microsoft.com/default.aspx?scid=kb;EN-US;263556
http://support.microsoft.com/default.aspx?scid=kb;EN-US;311231
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 29, 2004 at 12:42 am
On our servers we have installed a Tool called SENDMAIL. I use this tool to send dynamic error mails to selected people if jobs end with error.
The syntax looks like this...(Important: it is not possible to change sendmail steps which are in the same job as the stored procedure call is, because it seems like that the job is bufferd before it starts. Changes like step updates will be effect principal after the job ends. Thats why i allways create a new sendmail job.)
-- Job Sendmail
BEGIN TRANSACTION
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'LnSendMailNotOk')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''LnSendMailNotOk'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback1
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'LnSendMailNotOk'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'LnSendMailNotOk',
@owner_login_name = N'???',
@description = N'Dynamischer Emailversand',
@category_name = N'[Uncategorized (Local)]',
@enabled = 0,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
-- Add the job steps
set @queryjob1 = 'Set o = CreateObject("WScript.Shell")
o.Run """e:\program files\sendmail\lnsendmail.exe"" /R recepien /s ""DWH_VC NOT OK!!!"" /b ""Die Befüllung des DWH VC hat fehlgeschlagen."" /b "" "" /b ""Server: Msg 50000, Level 16, State 1, Procedure DWH_ETL_Beladung_neu ."" /b""' + @errmsg + '"" /b"" "" /b"" "" /b ""Freundliche Grüße "" /b"" "" /b ""SWE Datawarehouse Team"" "
Set o = Nothing'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'LnSendMailNotOk_Beladung',
@command = @queryjob1,
@database_name = N'VBScript',
@server = N'', @database_user_name = N'',
@subsystem = N'ActiveScripting',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
-- Job START
execute @ReturnCode = msdb.dbo.sp_start_job
@Job_ID = @JobID
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
END
COMMIT TRANSACTION
GOTO EndSave1
QuitWithRollback1:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave1:
raiserror (@errmsg, 16, 1)
October 29, 2004 at 8:13 am
We use outlook on every SQLServer and it works fine.
I saw you were using a pst-file, this means you are using a personal store so I asume you use a pop-mailbox.
We have an exchange organisation. The steps we follow:
Login on the server with the account wich starts the SQLServerAgent and configure outlook for the mailbox you want to use.
Check the internet explorer properties, the default mailprogram in Internet Explorer will be the program wich is used by SQLServer
Use Enterprise manager to select the mailprofile in the SQLAgent properties page.
Hope it help's...
Regards
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply