When I supply the following xp_sendmail statement:
EXEC Master..xp_sendmail
'msantoro@itginc.com',
'',
'SELECT last_name, first_name FROM db.dbo.tablename'
I get the following message:
"Supplied datatype for @query is not allowed, expecting 'varchar' "
If I change the query above to select just ONE field instead of two it works fine!
Any ideas are most appreciated.
thanx...
SQL Server 2000
Good day all,
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 appreciate any help you can offer me :)
Hello Pros
I am using xp_sendmail to send mail messages from SQL Server
Everything is ok on the development box, but on the production box ,
Am getting "xp_sendmail: failed with mail error 0x80004005"
Help Please
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,
DECLARE @RowCount int, @msg varchar(255)
SET @msg = 'Test start '
EXEC Master.dbo.xp_sendmail @recipients = 'dw@x.net',
@message = @msg,
@subject = 'start'
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
View 2 Replies View RelatedSQL 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
Help.
Hello all.
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.
Thanks.
Hi All,
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.
Am I missing something?
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
---------------------------------------------------
Error: ODBC error 208 (42S02) Invalid object name 'tblMYTABLE'.
Can someone tell me what am I doing wrong?
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.
Appreciate any prompt reply.
JD
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!
View 1 Replies View RelatedHello!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
View 2 Replies View Related
I'm a user of SQL 2000//2005, and I'm trying to send files using SQL Mail.
I'm am successful when I send a file of approx 13MB in size but, when I try to send a 24MB file, I receive the above subject error.
Is anyone aware of a file size limitation on attachments?
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:
EXEC master.dbo.xp_sendmail @RECIPIENTS = araz***@***.com(removed email address),
@SUBJECT = 'test'
I receive:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111
I have stopped/restarted the SQL SERVER AGENT but haven't done much else as I haven't been able to.
Should it work through transact SQL if the test email works from Enterprise Manager?
This is SQL 2000 SP4 running on Win2K in the domain. Thank you.
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?
EXEC master..xp_sendmail
@recipients = 'Richard Peoples,
@message = 'Test',
@subject = 'Long-Running Job to Check'
Thanks!!!
Hi,
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.....
select @myquery= 'Execute LeadDistribution..sp_sendmailB ' + convert(char,@AssToID) /*calling the sp_sendmailB procedure */
execute master.dbo.xp_sendmail @recipients='lakshmip@oasisnetwork.com', @message='Leads Information',@query=@myquery
Select @send_Mail=@@Error
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.
(1 row(s) affected)
Please help me if anyone knows....
Thanks,
Sailaja
Greetings,
I am trying to utilize xp_sendmail passing a variable as the @recipients value.
EXEC master..xp_sendmail
@recipients = @EMAIL_ADDRESS_LNK,
etc.......
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.
Is there anyway to format the message text when using xp_sendmail ?? E.g. making some of it bold or in italics ??
View 1 Replies View RelatedI am using xp_sendmail in stored procedure and sending query using @query option. Is there a way to format the query output?
View 4 Replies View RelatedHello,
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?
Hi
can the receiver list (xp_sendmail parameter) be a query. Becuase i must to concatenate. Thanks
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 executing this statement:
exec xp_sendmail @recipients='[email add]', @message='hello'
and i get an error 0x80040111
what does that mean? what am i doing wrong?
i'm already using SQL Server authentication. it was working last week, but now it isn't.
help please
Is it possible to set the "From" address using the xp_sendmail procedure?
View 1 Replies View RelatedDear Friends,
I need to configure SQL Server to send mails automatically. How to configure this?
Also what all are required for this? Is exchange server a must?
Hoping to get reply
Vipin
i'm trying to send email using sql server, but every time i try to run xp_sendmail i get this message:
xp_sendmail: failed with mail error 0x80040111
can anyone tell me what this error means???
Hi,
I want to use xp_sendmail like this
declare @user varchar
select @user = user
from users
where userid =1
exec.xp_sendmail @user, 'The master database is full.'
But I get an error message saying ambiguous recipient
Is there any way I cn do this?
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.
Has anyone seen this before?
Hi All,
I have the following script that checks the amount of free space on the drive:
declare @MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
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))
create table #None ([Equipment_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Equip_Nbr_11_Digit] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Activity_Date] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Activity_Time] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sighting_Code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filler] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location_City] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location_State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Train_Junction] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_Date] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_Time] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Road] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Destination_City] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Destination_State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Source] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Flatcar_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chassis_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Work_Order_Number] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BKG_BL_Number] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Train_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Free_Date] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Process_Date] [datetime] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[splc_code] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS null,
[Origin_SPLC] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Operator] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Shpt_Type] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pickup_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
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 = '|'
any thoughts would be great.
Jim
I am trying to send a resultset via email using xp_sendmail.
I need to send the email when an earlier executed query has any results.
Got the query results into a table variable/temp table and then
in xp_sendmail, using
"Declare @table_var table(...)
..query excution..
EXEC master.dbo.xp_sendmail @recipients = 'xx@xx.com',
@query = 'select * from @table_var'"
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."
any ideas on this.
Thanks in advance.
Hello,
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?
Declare @date varchar(100), @SqlBcp Varchar(1000)
Select @date = Convert( varchar, getdate(),112)+Replace (Convert(varchar, Getdate(),114),':','')
Set @SqlBcp = 'Master..Xp_CmdShell '+'''bcp "Select * from KK_TEST..JobStatus" queryout "C: esting'+@date+'.xls" -c -T'''
EXEC master.dbo.xp_sendmail
@recipients = N'xxxx@xxxxxxxxx.com',
@query = N'Exec ('+ @SqlBcp +')',
@subject = N'Failed Job Details',
@message = N'The contents of JobStatus ',
@attach_results = 'TRUE',
@width = 250 ;
Thanks
Krishna