I've been asked to create a DTS package that will execute 2 other DTS packages (yeah, I know... personally I want to write a sp for it *shrug*) and send an e-mail out reporting a failure and failure details.
So I have a couple of questions for you; How do I pick up the error number (and description?) from a failed DTS and pass it to my sendmail task? Is it possible to send anything other than plain text e-mails? Can I change the e-mail priority to "high"?
i am trying to configure a mail acccount on a sql server8.0 to mail alerts at the start/end or failure of a load.
i have configured the mail account(distribution group) on the server iam able to send mails but unable to receive mails when i try to run a dts package with a task,
i get a error msg- Server: Msg 18030, Level 16, State 1, Line 0 xp_sendmail: 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.
My mail client is outlook6 and sql ver is 8.0 is it a client problem or do i have to change any settings
Can someone please tell me if this bug with the xp_sendmail proc hasbeen addressed with SP4 of SQL Server 2000."BUG: XP_SENDMAIL Fails to Send Mail to Personal Distribution List"If not, what is the best alternative? I have tried using SQLAgentMailbut still could not get my query to work.Best,Sandeep Madduri
SQL server agent and SQL both log on as the same user and use the same mail profile. SQL server agent sends mail beautifully. I can view the properties of the SQL Server Agent and SQL mail.
When I execute xp_sendmail it does not send a mail message. When I go to view the properties of SQL Mail or SQL Server Agent the Enterprise Manager locks up.
The application logs shows an error# 18009 with a severity of 18.
I reboot the machine and all is well. I still cannot use xp_sendmail
I have a stored procedure that loops through a customer list and email the customer via xp_sendmail. My problem is that if there is a bad email address, the sproc ends right there.
Is there a way to trap for xp_sendmail errors so my sproc won't fail? I didn't see anything in BOL.
I was testing SQL Mail and I kept getting Unknown Recipient error regardless of what type of user names I tried to put in, I'd appreciate it if any of you could help.
Server: NT 4.0 Server (SP4) SQL Server: 6.5 (SP4) Mail Client: Exchange (5.0)
Since my company doesn't have a designated mailbox for SQL Server, I set it up to use my personal mailbox and profile. I was able to get SQL Mail up and running but couldn't not figure out a correct user name to pass to xp_sendmail.
The usual format of our e-mail addresses is <last name><first name>@xxx.com so I tried '<last name><first name>', '<last name>, <first name>' (the same format shown in the Address Book)...etc. but none of those worked.
Send Mail works fine when I just want to send a message. However, when I try to send the results from a query as an attachment, I get a ODBC error. Here is example of the code and the error code that I recieve.
Use MyDatabase go exec master.dbo.xp_sendmail 'Doe,John', @query = 'Select * from tblMyTable', @subject = 'SQL Server Report', @message = 'The contents of the tblMyTable:', @attach_results = 'TRUE', @width = 250 go
I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded. So I try to have it executed in a trigger but it failed.
Here is the trigger creation script and error message
use mlcb go if exists (select name from sysobjects where name = 'test' and type = 'TR') DROP TRIGGER TEST GO
CREATE TRIGGER test on mlcb.dbo.trans_errlog for insert as declare @email_subject varchar(100), @email_content varchar(4000), @email_recipients varchar(50)
set @email_subject='SQL Mail test mail' set @email_recipients='some@world.com.tw' set @email_content='this is a test mail, don't reply this mail'
exec master.dbo.xp_sendmail @recipients=@email_recipients,@subject=@email_subj ect,@message=@email_content GO
Error Message: Server: Msg 2812, Level 16, State 62, Line 6 Could not find stored procedure 'master.xp_startmail'. The statement has been terminated.
I'm trying to send an email using master..xp_sendmail with parms andthe default Outlook profile. I can successfully test the profile on myown computer and on the server.I'm running using Windows authentication and have given myself fulladmin rights in MSSQL.Which account is mssql trying to send the email from? Is it theservers or my local clients?The mailbox does not appear to be full and it's possible to send &receive emails on the server and on my own machine.What could be a possible cause or the next thing I can check?Thanks!
Hello!My environment is: Win 2000 Server, MS SQL-Server 2000 (SP2)and MS ExchangeServer 5.5the two following TransactSQL-codepieces show differentresults:1. with attachmentmaster..xp_sendmail@recipients = 'j.hetzel@rac.de',@subject = 'test',@message = 'test',-- ******************************@attachments = 'c: est.txt',-- ******************************@no_output = 'False'-->18025 level 16 state 1 line 0xp_sendmail failed with mail error 0x800040052. without attachmentmaster..xp_sendmail@recipients = 'j.hetzel@rac.de',@subject = 'test',@message = 'test',-- ******************************-- @attachments = 'c: est.txt',-- ******************************@no_output = 'False'-->Mail sent.why am I not able to send emails with attachments?Who can help me???Thanks in advanceJürgen
After consulting with the Exchange Administrator, I was advise that there is a limit on file attachments. This explains why I get this message with large (>20MB) attachments.
With this additional information I need to compress my file using a compression tool like winzip or pkzip. My company uses the windows version of pkzip. However, I have downloaded an evaluation version of the PKzip command line version.
Can someone provide guidance how I can execute this utility within my sql procedure?
Hello I am receiving the dreaded mail error listed above. I can send out a test E-mail from Enterprise Manager to operators, but I cannot run this Transact query:
I am trying to use xp_sendmail with no luck. My SQL mail works fine and I am logged in as sa. Here is the csript that I am using. The script works on my local box(which is running Win 2K Advanced Server and SQL 7). It will not work on my production boxes. Any suggestions?
I am using xp_sendmail in a stored procedure.I need to update the fields after the mail sent. Is there a way to capture the errors or server messages that occurs. I am using the following code.....
If @send_Mail=0 Begin update MasterleadPool/*Updates the MasterleadPool table once the mail sent */ Set EmailSent='Y', Dateout=getdate() where AssToID=@AssToID and EmailSent='N' and AssToFlag='Y' End
I used @@error to capture the error but it does'nt work. I got server message as follows.... So the problem is it should'nt go to update statement if there is any type of errors in sending a mail..
Server: Msg 17903, Level 18, State 1, Line 0 MAPI login failure.
However in doing this, it never works (go figure or I wouldn't be submitting this). I have defined the variable @EMAIL_ADDRESS_LNK appropriately to reference a field in a table and I'm positive that the value in the field is valid.
What am I missing? If this is not possible, is there a workaround. Thanks in advance for any assistance you can provide.
How robust is xp_sendmail? I would like to roll thru 10 - 20 thousand records, strip the email and send a newsletter...
I'm a definite newby when it comes to mail servers (and how they interact with SQL Server 7.0)...so I'm not sure whether this type of processing would crash/stall the server.
can xp_sendmail handle this type of processing?
I appreciate your help,
p.s. can u point me to any good articles on the subject?
I have a query that works on its own, however when I put it into xp_sendmail it fails. It appears that the "set" command does not work. Here is the query. Any suggestions? Can you use variables within xp_sendmail @ query section?
EXEC master.dbo.xp_sendmail @recipients = 'Richard Peoples', @subject = 'The following Budget Checked items need attention.', @query ='DECLARE @A1 CHAR (20) DECLARE @A2 CHAR (2) SET @A1 = 'Yvette Palomo' SET @A2 = 'N' IF (select COUNT (*) from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK where RETURN_TO_ANALYST = @A1 AND BUDGET_CHECK_CLEAR= @A2) > 0 begin select A.JOURNAL_ID, A.PROJECT_ID, A.ACCOUNT, B.XLATLONGNAME from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK A INNER JOIN FSPROD75.dbo.XLATTABLE B ON A.BCM_LINE_STATUS = B.FIELDVALUE where (((A.RETURN_TO_ANALYST)= @A1) AND ((A.BUDGET_CHECK_CLEAR)=@A2)) AND (B.FIELDNAME = "BCM_LINE_STATUS") end'
I'm using XP_Sendmail on an NT Server and it works fine. We have a new Windows 2000 server, which the SQL Mail has been set up correctly, and the test passes.
When I use execute xp_sendmail in the query analyzer on the 2000 server, it just sits there and processes for over an hour, without any error messages.
select @MB_Free = MB_Free from #FreeSpace where Drive = 'J'
if @MB_Free < 550 exec master.dbo.xp_sendmail @recipients = 'test@yahoo.com', @message = 'Running low on free space'
I can run it as a job step. However, the problem is that we don't have Microsoft Outlook to run xp_sendmail proc to get notify when the free space on the drive is low. I can run mailsend which is os command and add that as a job step. But I don't know how to combine the code above and mailsend, since one is the t-sql and the other one is command script. Any help is appreciated.
I have recently added columns to a table that is part of a xp_send mail script. meaning i run a query off of the table that had the columns added. I am now getting the error: failed with operating system error 32
I have no idea what this means. This is a copy of the script. exec xp_sendmail 'dionne, jim;eddens, david;Wiggs, Alexander;Miller, Debbie;conmdi', '(scrbbususcnc01) Failure 322 Load', @Attachments = '322Error.txt;\scrbbususcnc01archive322msg322M SG.txt;', @query = 'Use [maersk data warehouse] create table #Duplicate_Records (Equipment_Number varchar(10) ,Activity_Date varchar(6) ,Activity_Time varchar(4) ,Sighting_Code varchar(2) ,CountOfEquipment_Number int) insert into #Duplicate_Records SELECT STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code, Count(STG_INTERMODAL_322MSG_TBL.Equipment_Number) AS CountOfEquipment_Number FROM STG_INTERMODAL_322MSG_TBL GROUP BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code HAVING (((Count(STG_INTERMODAL_322MSG_TBL.Equipment_Numbe r))>1))
insert into #None SELECT DISTINCT STG_INTERMODAL_322MSG_TBL.* FROM [#Duplicate_Records] INNER JOIN STG_INTERMODAL_322MSG_TBL ON ([#Duplicate_Records].Sighting_Code = STG_INTERMODAL_322MSG_TBL.Sighting_Code) AND ([#Duplicate_Records].Activity_Time = STG_INTERMODAL_322MSG_TBL.Activity_Time) AND ([#Duplicate_Records].Activity_Date = STG_INTERMODAL_322MSG_TBL.Activity_Date) AND ([#Duplicate_Records].Equipment_Number = STG_INTERMODAL_322MSG_TBL.Equipment_Number) ORDER BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code;
create table #Real (Equipment_Number varchar(10) ,Activity_Date varchar(6) ,Activity_Time varchar(4) ,Sighting_Code varchar(2) ,CountOfEquipment_Number int) insert into #Real SELECT [#None].Equipment_Number, [#None].Activity_Date, [#None].Activity_Time, [#None].Sighting_Code, Count([#None].Equipment_Number) AS CountOfEquipment_Number FROM [#None] GROUP BY [#None].Equipment_Number, [#None].Activity_Date, [#None].Activity_Time, [#None].Sighting_Code HAVING (((Count([#None].Equipment_Number))>1));
SELECT STG_INTERMODAL_322MSG_TBL.* FROM [#Real] INNER JOIN STG_INTERMODAL_322MSG_TBL ON ([#Real].Equipment_Number = STG_INTERMODAL_322MSG_TBL.Equipment_Number) AND ([#Real].Activity_Date = STG_INTERMODAL_322MSG_TBL.Activity_Date) AND ([#Real].Activity_Time = STG_INTERMODAL_322MSG_TBL.Activity_Time) AND ([#Real].Sighting_Code = STG_INTERMODAL_322MSG_TBL.Sighting_Code) ORDER BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code;
drop table #Duplicate_Records drop table #None drop table #Real', @subject = '(scrbbususcnc01) Failure 322 Load' , @Attach_Results = true, @width = 3000, @Separator = '|'
it gives error saying that @table_var must be declared. even if i use temporary table, the message i get is "cannot reference object in tempdb database."
I want to send a reslut as a xls file. so I did it throug BCP. The same time i need to send file in mail. Here i am stucked. Could any one please advise me to solve this problem?