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.
Author |
Topic |
jocampo
Starting Member
48 Posts |
Posted - 2006-08-11 : 13:26:09
|
This is a very strange issue. I have the following 3 steps jobs succesfuly running on 2 servers and not in another one ...Job:1st step: truncate table MyTable;2nd step:insert into MyTableselect top 25 * from MyTable2order by insert_dm desc;3rd step:EXEC xp_sendmail @recipients = 'xxx.xxx@x.com', @query = 'SELECT * FROM table.dbo.MyTable', @subject = 'Whatever', @message = 'Whatever', @attach_results = 'TRUE', @width = 250When job reachs third step ... it just failed ...Facts:-SQL Service Pack SP3a-Windows 2003 (Cluster node) with SP1 and hotfixes-Maintenance plans are able to send messages-No Exchange issue.-No network connectivity issue.Any ideas? I already install/reinstall Outlook 2003 SP2 with no success. It ran beautiful in the other servers, similar configuration and using the same MS-Exchange server. |
|
schuhtl
Posting Yak Master
102 Posts |
Posted - 2006-08-11 : 16:17:33
|
What does your job history say? I think you need to add EXEC master..xp_sendmail ....... |
|
|
jocampo
Starting Member
48 Posts |
Posted - 2006-08-11 : 16:33:55
|
quote: Originally posted by schuhtl What does your job history say? I think you need to add EXEC master..xp_sendmail .......
The job history says:The job failed. The Job was invoked by Schedule 19 (xxx). The last step to run was step 3 (Read xxx table and send email). |
|
|
schuhtl
Posting Yak Master
102 Posts |
Posted - 2006-08-11 : 16:57:23
|
What does it say for step 3 when you check "Show step details"? |
|
|
jocampo
Starting Member
48 Posts |
Posted - 2006-08-14 : 13:32:21
|
step details? there is no such options ... i think ... anyway, i found a workaround using xp_smtp_sendmail, but the problem now is that i'm not able to send attachements ...Here is my final step:declare @rc intexec @rc = master.dbo.xp_smtp_sendmail @FROM = N'MyEmail@MyDomain.com', @FROM_NAME = N'Joe Mailman', @TO = N'mymail@mail.com', @priority = N'HIGH', @subject = N'Hello SQL Server SMTP Mail', @type = N'text/plain', @attachment = N'c:\prueba.txt', @server = N'smtp.server.test' select RC = @rcWhy it does not work? if i use no attachs i recieved the message ... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 13:44:15
|
EXEC xp_sendmail @recipients = 'xxx.xxx@x.com', @query = 'SELECT * FROM table.dbo.MyTable',@subject = 'Whatever',@message = 'Whatever',@attach_results = 'TRUE', @width = 250Run the above in Query Analyzer on the database server that is not working. Post the error message here.Tara Kizer |
|
|
jocampo
Starting Member
48 Posts |
Posted - 2006-08-14 : 15:28:04
|
quote: Originally posted by tkizer EXEC xp_sendmail @recipients = 'xxx.xxx@x.com', @query = 'SELECT * FROM table.dbo.MyTable',@subject = 'Whatever',@message = 'Whatever',@attach_results = 'TRUE', @width = 250Run the above in Query Analyzer on the database server that is not working. Post the error message here.Tara Kizer
Hello Tara.Here's the error:Server: Msg 18025, Level 16, State 1, Line 0xp_sendmail: failed with mail error 0x80040111 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 15:35:57
|
quote: Q18: Why am I getting mail error 0x80040111 (or similar hex number) when I try to send mail?A18: The first step in resolving this problem is to determine the error message associated with the hex error number. MAPI does not convert error numbers to strings, so SQL Mail only returns the error number. For additional information on how to interpret the MAPI return code, click the article number below to view the article in the Microsoft Knowledge Base: 238119 (http://support.microsoft.com/kb/238119/EN-US/) INFO: List of Extended MAPI Numeric Result Codes When you look up the hex value 0x80040111, you see that this corresponds to the MAPI error message MAPI_E_LOGON_FAILED, which indicates that SQL Mail failed to logon with the mail profile provided. You can then take corrective action based on the error message.
Taken from this KB article: http://support.microsoft.com/kb/315886/Tara Kizer |
|
|
jocampo
Starting Member
48 Posts |
Posted - 2006-08-14 : 16:17:47
|
quote: Originally posted by tkizer
quote: Q18: Why am I getting mail error 0x80040111 (or similar hex number) when I try to send mail?A18: The first step in resolving this problem is to determine the error message associated with the hex error number. MAPI does not convert error numbers to strings, so SQL Mail only returns the error number. For additional information on how to interpret the MAPI return code, click the article number below to view the article in the Microsoft Knowledge Base: 238119 (http://support.microsoft.com/kb/238119/EN-US/) INFO: List of Extended MAPI Numeric Result Codes When you look up the hex value 0x80040111, you see that this corresponds to the MAPI error message MAPI_E_LOGON_FAILED, which indicates that SQL Mail failed to logon with the mail profile provided. You can then take corrective action based on the error message.
Taken from this KB article: http://support.microsoft.com/kb/315886/Tara Kizer
Thanks Tara ...But it does not worked for me. I already checked. I have no Outlook or Exchange issue. In fact, i can recieve all Maintenance Plan alerts via email. No logon issues either with the SQL agent account. My problem is with the xp_sendmail store procedure. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 16:22:47
|
I guess you missed the point. The article that I posted has to do with xp_sendmail. Google your error (xp_sendmail: failed with mail error 0x80040111) and try everything that the links suggest.Tara Kizer |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-14 : 16:29:49
|
The fact that you can get SQL Agent alerts only means that the Agent Email profile is setup correctly. They are configured separately, and may or may not use the same profile. If the logon account for the SQL Server service is not the same as the logon account for SQL Agent, they can't use the same profile.The SQL Mail profile is setup in Enterprise Manager under Support Services, SQL Mail. The mail profile for the SQL Agent is setup under the SQL Server Agent, Properties, General tab.CODO ERGO SUM |
|
|
DBASlut
Yak Posting Veteran
71 Posts |
Posted - 2006-08-14 : 16:54:47
|
also test your email by commenting out the attachement parameters. If it sends email, check how the job is running and make sure it has rights to do the select on the table. |
|
|
PatDeV
Posting Yak Master
197 Posts |
Posted - 2006-08-14 : 17:12:50
|
for SMTP try this procedurecreate Procedure [dbo].[spSQLSMTPMail] @vcTo varchar(2048) = null, @vcBody varchar(8000) = '', @vcSubject varchar(255) = null, @vcAttachments varchar(1024) = null, @vcQuery varchar(8000) = null, @vcFrom varchar(128) = null, @vcCC varchar(2048) = '', @vcBCC varchar(2048) = '', @vcSMTPServer varchar(255) = 'IP address of Emai server or Exchange Server here ' @cSendUsing char(1) = '2', @vcPort varchar(3) = '25', @cAuthenticate char(1) = '0', @vcDSNOptions varchar(2) = '0', @vcTimeout varchar(2) = '30', @vcSenderName varchar(128) = null, @vcServerName sysname = null As /*******************************************************************/ --Name : sp_SQLSMTPMail --Server : Generic --Description : SQL smtp e-mail using CDOSYS, OLE Automation and a -- network smtp server; For SQL Servers running on -- windows 2000. -- --Note : Be sure to set the default for @vcSMTPServer above to -- the company network smtp server or you will have to -- pass it in each time. -- --Comments : Getting the network SMTP configured to work properly -- may require engaging your company network or -- server people who deal with the netowrk SMTP server. -- Some errors that the stored proc returns relate to -- incorrect permissions for the various SQL Servers to -- use the SMTP relay server to bouce out going mail. -- Without proper permissions the SQL server appears as -- a spammer to the local SMTP network server. -- --Parameters : See the 'Syntax' Print statements below or call the -- sp with '?' as the first input. -- -- --History : /*******************************************************************/ Set nocount on -- Determine if the user requested syntax. If @vcTo = '?' Begin Print 'Syntax for sp_SQLSMTPMail (based on CDOSYS):' Print 'Exec master.dbo.sp_SQLSMTPMail' Print ' @vcTo (varchar(2048)) - Recipient e-mail address list separating each with a '';'' ' Print ' or a '',''. Use a ''?'' to return the syntax.' Print ' @vcBody (varchar(8000)) - Text body; use embedded char(13) + char(10)' Print ' for carriage returns. The default is nothing' Print ' @vcSubject (varchar(255))) - E-mail subject. The default is a message from' Print ' @@servername.' Print ' @vcAttachments (varchar(1024)) - Attachment list separating each with a '';''.' Print ' The default is no attachments.' Print ' @vcQuery (varchar(8000)) - In-line query or a query file path; do not ' Print ' use double quotes within the query.' Print ' @vcFrom (varchar(128)) - Sender list defaulted to @@ServerName.' Print ' @vcCC (varchar(2048)) - CC list separating each with a '';'' or a '',''' Print ' The default is no CC addresses.' Print ' @vcBCC (varchar(2048)) - Blind CC list separating each with a '';'' or a '',''' Print ' The default is no BCC addresses.' Print ' @vcSMTPServer (varchar(255)) - Network smtp server defaulted to your companies network' Print ' smtp server. Set this in the stored proc code.' Print ' @cSendUsing (char(1)) - Specifies the smpt server method, local or network. The' Print ' default is network, a value of ''2''.' Print ' @vcPort (varchar(3)) - The smtp server communication port defaulted to ''25''.' Print ' @cAuthenticate (char(1)) - The smtp server authentication method defaulted to ' Print ' anonymous, a value of ''0''.' Print ' @vcDSNOptions (varchar(2)) - The smtp server delivery status defaulted to none,' Print ' a value of ''0''.' Print ' @vcTimeout (varchar(2)) - The smtp server connection timeout defaulted to 30 seconds.' Print ' @vcSenderName (varchar(128)) - Primary sender name defaulted to @@ServerName.' Print ' @vcServerName (sysname) - SQL Server to which the query is directed defaulted' Print ' to @@ServerName.' Print '' Print '' Print 'Example:' Print 'sp_SQLSMTPMail ''<user@mycompany.com>'', ''This is a test'', @vcSMTPServer = <network smtp relay server>' Print '' Print 'The above example will send an smpt e-mail to <user@mycompany.com> from @@ServerName' Print 'with a subject of ''Message from SQL Server <@@ServerName>'' and a' Print 'text body of ''This is a test'' using the network smtp server specified.' Print 'See the MSDN online library, Messaging and Collaboration, at ' Print 'http://www.msdn.microsoft.com/library/ for details about CDOSYS.' Print 'subheadings: Messaging and Collaboration>Collaboration Data Objects>CDO for Windows 2000>' Print 'Reference>Fields>http://schemas.microsoft.com/cdo/configuration/>smtpserver field' Print '' Print 'Be sure to set the default for @vcSMTPServer before compiling this stored procedure.' Print '' Return End -- Declare variables Declare @iMessageObjId int Declare @iHr int Declare @iRtn int Declare @iFileExists tinyint Declare @vcCmd varchar(255) Declare @vcQueryOutPath varchar(50) Declare @dtDatetime datetime Declare @vcErrMssg varchar(255) Declare @vcAttachment varchar(1024) Declare @iPos int Declare @vcErrSource varchar(255) Declare @vcErrDescription varchar(255) -- Set local variables. Set @dtDatetime = getdate() Set @iHr = 0 -- Check for minimum parameters. If @vcTo is null Begin Set @vcErrMssg = 'You must supply at least 1 recipient.' Goto ErrMssg End -- CDOSYS uses commas to separate recipients. Allow users to use -- either a comma or a semi-colon by replacing semi-colons in the -- To, CCs and BCCs. Select @vcTo = Replace(@vcTo, ';', ',') Select @vcCC = Replace(@vcCC, ';', ',') Select @vcBCC = Replace(@vcBCC, ';', ',') -- Set the default SQL Server to the local SQL Server if one -- is not provided to accommodate instances in SQL 2000. If @vcServerName is null Set @vcServerName = @@servername -- Set a default "subject" if one is not provided. If @vcSubject is null Set @vcSubject = 'Message from SQL Server ' + @vcServerName -- Set a default "from" if one is not provided. If @vcFrom is null Set @vcFrom = 'SQL-' + Replace(@vcServerName,'\','_') -- Set a default "sender name" if one is not provided. If @vcSenderName is null Set @vcSenderName = 'SQL-' + Replace(@vcServerName,'\','_') -- Create the SMTP message object. EXEC @iHr = sp_OACreate 'CDO.Message', @iMessageObjId OUT IF @iHr <> 0 Begin Set @vcErrMssg = 'Error creating object CDO.Message.' Goto ErrMssg End -- Set SMTP message object parameters. -- To EXEC @iHr = sp_OASetProperty @iMessageObjId, 'To', @vcTo IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message parameter "To".' Goto ErrMssg End -- Subject EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Subject', @vcSubject IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message parameter "Subject".' Goto ErrMssg End -- From EXEC @iHr = sp_OASetProperty @iMessageObjId, 'From', @vcFrom IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message parameter "From".' Goto ErrMssg End -- CC EXEC @iHr = sp_OASetProperty @iMessageObjId, 'CC', @vcCC IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message parameter "CC".' Goto ErrMssg End -- BCC EXEC @iHr = sp_OASetProperty @iMessageObjId, 'BCC', @vcBCC IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message parameter "BCC".' Goto ErrMssg End -- DSNOptions EXEC @iHr = sp_OASetProperty @iMessageObjId, 'DSNOptions', @vcDSNOptions IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message parameter "DSNOptions".' Goto ErrMssg End -- Sender EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Sender', @vcSenderName IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message parameter "Sender".' Goto ErrMssg End -- Is there a query to run? If @vcQuery is not null and @vcQuery <> '' Begin -- We have a query result to include; temporarily send the output to the -- drive with the most free space. Use xp_fixeddrives to determine this. -- If a temp table exists with the following name drop it. If (Select object_id('tempdb.dbo.#fixeddrives')) > 0 Exec ('Drop table #fixeddrives') -- Create a temp table to work with xp_fixeddrives. Create table #fixeddrives( Drive char(1) null, FreeSpace varchar(15) null) -- Get the fixeddrive info. Insert into #fixeddrives Exec master.dbo.xp_fixeddrives -- Get the drive letter of the drive with the most free space -- Note: The OSQL output file name must be unique for each call within the same session. -- Apparently OSQL does not release its lock on the first file created until the session ends. -- Hence this alleviates a problem with queries from multiple calls in a cursor or other loop. Select @vcQueryOutPath = Drive + ':\TempQueryOut' + ltrim(str(datepart(hh,getdate()))) + ltrim(str(datepart(mi,getdate()))) + ltrim(str(datepart(ss,getdate()))) + ltrim(str(datepart(ms,getdate()))) + '.txt' from #fixeddrives where FreeSpace = (select max(FreeSpace) from #fixeddrives ) -- Check for a pattern of '\\*\' or '?:\'. -- If found assume the query is a file path. If Left(@vcQuery, 35) like '\\%\%' or Left(@vcQuery, 5) like '_:\%' Begin Select @vcCmd = 'osql /S' + @vcServerName + ' /E /i' + convert(varchar(1024),@vcQuery) + ' /o' + @vcQueryOutPath + ' -n -w5000 ' End Else Begin Select @vcCmd = 'osql /S' + @vcServerName + ' /E /Q"' + @vcQuery + '" /o' + @vcQueryOutPath + ' -n -w5000 ' End -- Execute the query Exec master.dbo.xp_cmdshell @vcCmd, no_output -- Add the query results as an attachment if the file was successfully created. -- Check to see if the file exists. Use xp_fileexist to determine this. -- If a temp table exists with the following name drop it. If (Select object_id('tempdb.dbo.#fileexists')) > 0 Exec ('Drop table #fileexists') -- Create a temp table to work with xp_fileexist. Create table #fileexists( FileExists tinyint null, FileIsDirectory tinyint null, ParentDirectoryExists tinyint null) -- Execute xp_fileexist Insert into #fileexists exec master.dbo.xp_fileexist @vcQueryOutPath -- Now see if we need to add the file as an attachment If (select FileExists from #fileexists) = 1 Begin -- Set a variable for later use to delete the file. Select @iFileExists = 1 -- Add the file path to the attachment variable. If @vcAttachments is null Select @vcAttachments = @vcQueryOutPath Else Select @vcAttachments = @vcAttachments + '; ' + @vcQueryOutPath End End -- Check for multiple attachments separated by a semi-colon ';'. If @vcAttachments is not null Begin If right(@vcAttachments,1) <> ';' Select @vcAttachments = @vcAttachments + '; ' Select @iPos = CharIndex(';', @vcAttachments, 1) While @iPos > 0 Begin Select @vcAttachment = ltrim(rtrim(substring(@vcAttachments, 1, @iPos -1))) Select @vcAttachments = substring(@vcAttachments, @iPos + 1, Len(@vcAttachments)-@iPos) EXEC @iHr = sp_OAMethod @iMessageObjId, 'AddAttachment', @iRtn Out, @vcAttachment IF @iHr <> 0 Begin EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out Select @vcBody = @vcBody + char(13) + char(10) + char(13) + char(10) + char(13) + char(10) + 'Error adding attachment: ' + char(13) + char(10) + @vcErrSource + char(13) + char(10) + @vcAttachment End Select @iPos = CharIndex(';', @vcAttachments, 1) End End -- TextBody EXEC @iHr = sp_OASetProperty @iMessageObjId, 'TextBody', @vcBody IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message parameter "TextBody".' Goto ErrMssg End -- Other Message parameters for reference --EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MimeFormatted', False --EXEC @iHr = sp_OASetProperty @iMessageObjId, 'AutoGenerateTextBody', False --EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MDNRequested', True -- Set SMTP Message configuration property values. -- Network SMTP Server location EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @vcSMTPServer IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "smtpserver".' Goto ErrMssg End -- Sendusing EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', @cSendUsing IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "sendusing".' Goto ErrMssg End -- SMTPConnectionTimeout EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout").Value', @vcTimeout IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "SMTPConnectionTimeout".' Goto ErrMssg End -- SMTPServerPort EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPServerPort").Value', @vcPort IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "SMTPServerPort".' Goto ErrMssg End -- SMTPAuthenticate EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate").Value', @cAuthenticate IF @iHr <> 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "SMTPAuthenticate".' Goto ErrMssg End -- Other Message Configuration fields for reference --EXEC @iHr = sp_OASetProperty @iMessageObjId, --'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPUseSSL").Value',True --EXEC @iHr = sp_OASetProperty @iMessageObjId, --'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/LanguageCode").Value','en' --EXEC @iHr = sp_OASetProperty @iMessageObjId, --'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendEmailAddress").Value', 'Test User' --EXEC @iHr = sp_OASetProperty @iMessageObjId, --'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value',null --EXEC @iHr = sp_OASetProperty @iMessageObjId, --'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value',null -- Update the Message object fields and configuration fields. EXEC @iHr = sp_OAMethod @iMessageObjId, 'Configuration.Fields.Update' IF @iHr <> 0 Begin Set @vcErrMssg = 'Error updating Message configuration fields.' Goto ErrMssg End EXEC @iHr = sp_OAMethod @iMessageObjId, 'Fields.Update' IF @iHr <> 0 Begin Set @vcErrMssg = 'Error updating Message parameters.' Goto ErrMssg End -- Send the message. EXEC @iHr = sp_OAMethod @iMessageObjId, 'Send' IF @iHr <> 0 Begin Set @vcErrMssg = 'Error Sending e-mail.' Goto ErrMssg End Else Print 'Mail sent.' Cleanup: -- Destroy the object and return. EXEC @iHr = sp_OADestroy @iMessageObjId --EXEC @iHr = sp_OAStop -- Delete the query output file if one exists. If @iFileExists = 1 Begin Select @vcCmd = 'del ' + @vcQueryOutPath Exec master.dbo.xp_cmdshell @vcCmd, no_output End Return ErrMssg: Begin Print @vcErrMssg If @iHr <> 0 Begin EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out Print @vcErrSource Print @vcErrDescription End -- Determine whether to exist or go to Cleanup. If @vcErrMssg = 'Error creating object CDO.Message.' Return Else Goto Cleanup End |
|
|
|
|
|
|
|