Requirement:
On Insert, Trigger xp_sendmail to complete pre-formatted email with specific content and send to given email address.
Question:
What tools/methods are available to develop a template that SQL Mail's (SQL Server) xp_sendmail function can utilise without resorting to attachments.
e.g. - how do Amazon.com format and send their automated shipping confirmaion emails ??
I know formatting should be handled in the client app and not in SQL Serverbut this is the situation I must develop in so any help would beappreciated.I'm running the following in Query AnalyzerUSE PubsDECLARE @MessageSubject VARCHAR(50)SELECT @MessageSubject = 'Report'EXEC master.dbo.xp_sendmail 'me@mine.com',@query = 'SELECT au_fname, au_lname from pubs.dbo.authors',@subject = @MessageSubjectThe results in my email look like:au_fname au_lname------------------- ----------------------------------------Abraham BennetReginald Blotchet-HallsCheryl CarsonI would like the results to be like:First Name: AbrahamLast Name: BennetFirst Name: ReginaldLast Name: Blotchet-HallsFirst Name: CherylLast Name: CarsonThanks
View 2 Replies View RelatedI 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
when creating a sp in SQL 2K that uses xp_sendmail i get a messagesaying:Cannot add rows to sysdepends for the current stored procedure becauseit depends on the missing object 'xp_sendmail'. The stored procedurewill still be created.does this mean that only dependencies involving xp_sendmail will notbe stored? the other dependencies appear to be fine. i'm not surewhy sql would even bother telling me this info. thoughts?
View 1 Replies View RelatedI am having a problem sending an email using the stored procedure "xp_sendmail".
This query is good.
SELECT h.order_id, h.order_date, h.responsible2 as "requested by", sum(d.cur_amount) as amount
FROM table1 as h inner join table2 as d
on h.order_id = d.order_id
where h.accept_flag = 0
and h.order_date > ' jan 1 2006 '
GROUP BY h.order_id,h.order_date, h.responsible2
ORDER BY h.order_id
The same query used to send an email does not work, don't know why.
master.dbo.xp_sendmail 'email@domainname.com,
@subject= 'POs that need approval',
@dbuse= 'database',
@query = '
SELECT h.order_id, h.order_date, h.responsible2 as "requested by", sum(d.cur_amount) as amount
FROM table1 as h inner join table2 as d
on h.order_id = d.order_id
where h.accept_flag = 0
and h.order_date > ' jan 1 2006 '
GROUP BY h.order_id,h.order_date, h.responsible2
ORDER BY h.order_id
'
Here is the error msg
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'jan'.
Any help would be appreciated!
I migrated dts pckg into ssis . In that pckg i have one activex script for xp_sendmail.
exec maseter xp_sendmail,
@recipients='abc@gmail.com',
@query=@sqlsrd,
@subject='member load validation',
@attach_results=true,@width=250
above code run in ssis or i have to change if i have to chang then tell me where should i have to change.
Thanks
Aric
I am trying to use xp_sendmail with operator instead email id.
it is not taking operator
is there anyway to use operator with xp_sendmail
I have valid operator it is working with jobs
Thanks
Gan
Ok all...I'm not having much luck with this.
I'm using SQL Server 2005 MSE
I try to run
xp_sendmail 'adam@getpaid4spam.biz', 'This is a test'
And I get the following error...
Msg 17750, Level 16, State 0, Procedure xp_sendmail, Line 1
Could not load the DLL sqlmap90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Any ideas?
Adamus
I'm using SQL 2000 and would like to send a generated email using this stored procedure:
select Libraryrequest.LoanRequestID, Titles.Title, requestors.fname+ ' ' + requestors.lname as [Name], libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedatefrom libraryrequestjoin requestors on requestors.requestorid=libraryrequest.requestoridjoin Titles on Titles.Titleid = Libraryrequest.titleidwhere duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101')
I know I need to go to Management, SQL Server Agent, Jobs, New Job. Do I put the stored procedure in the descriptions part? After that I'm lost what do I do.
Thanks!
When excuting the xp_sendmail in the Query analyzer, my mail gets send. When doing this in my code it doesn't work eventhough I explicitly use startmailn then xp_sendmail and then sp_stopmail? I think this is a problem of user rights? I gave my user as parameter but even then it didn't work. Any suggestions? I'm pretty sure, the used code is correct.
greetings,
Geoff
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
Has anyone else noticed that if you create a non-existing file as an attachment using xp_sendmail in SQL Server 2000, it does not create a copy of that file on the Hard Drive, nor does it format the attached file sensibly if you attach it as a .csv file ?
I am using the procedure below :-
CREATE PROCEDURE mailtest AS
declare @sql varchar (255)
SELECT PERSONID, FORENAME, SURNAME INTO ##TEMP FROM PERSON
SELECT @sql='SELECT * FROM ##TEMP'
IF @@ROWCOUNT > 0
begin
exec master.dbo.xp_sendmail
@recipients = 'itsmarkdavies@hotmail.com',
@Message = 'Test file',
@Query = @sql,
@attachments = 'C:MARKTEST.CSV',
@Attach_Results = 'True',
@Message = '',
@Subject = 'test',
@No_Header = 'True',
@Width = 500,
@Separator = ','
end
DROP TABLE ##TEMP
GO
In the example above, the file MARKTEST.CSV does not currently exist, but the procedure should create it, put it in the root of C: and e:mail it, as it did when it ran under SQL Server 6.5. However, under 2000 it now doesn`t put a copy on the Hard Drive and it formats the .csv file in a very odd manner.
I have an app that emails automatically from an ASP page - it works fine but for one specific user I get the error message "MSG 17914, Level 18, State 1, line 0 - Unknown recipient: Parameter '@recipients', recipient 'nameofuser'" where 'nameofuser' is of course, the name of the user. Am I missing something? What's different about this guy that he can't use xp_sendmail? HELP!
View 1 Replies View RelatedHi,
I have a table of records which includes the field 'owner'. What I want to do is send each owner a list of their records using xp_sendmail.
Is there anyway of doing this? The actual selection of the records is pretty much straight forward....
SELECT * FROM [myTable]
GROUP BY [owner]
But how do I go about looping through the owner groups and send an email for each one?
Your help is appreciated.
Gary
Goal--I want this script to go through some tables and look at some equipment ID's and whoever has equipment assigned to them I want to send an e-mail to them telling them to bring in the equipment for inventory.
Problem--On line 28 I set my xp_sendmail variable "@query" equal to a select statement. Inside that select statement I refer to a variable @USERNAME from outside of the query. I get a must declare variable error. Is there any way to refer to a variable from outside this select statement?
Any suggestions?
Thanks!!!
SET NOCOUNT ON
go
PRINT 'Determining which users to email ...'
PRINT ' '
go
DECLARE @USERNAME varchar(30)
DECLARE USERNAME_CURSOR CURSOR FOR
select DISTINCT A.UserName from tblMISFixedAssetTable2 A, tblMISFixedAssetTable B
where A.BarCodeID = B.BarCodeID
and A.UserName is not null
and B.MobileEquipment = '-1'
and A.LastUpdate = (Select Max(C.LastUpdate)
FROM tblMISFixedAssetTable2 C
WHERE C.BarCodeID = A.BarCodeID)
OPEN USERNAME_CURSOR
FETCH NEXT FROM USERNAME_CURSOR INTO @USERNAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @USERNAME = RTRIM(@USERNAME)
exec master.dbo.xp_sendmail
@recipients = @USERNAME,
@subject = 'Inventory 2002',
@message = 'Please bring in your Portable/Mobile Equipment',
@query = 'SELECT A.BarCodeID, B.ItemCategory, B.ItemDescription
from MISAsset.dbo.tblMISFixedAssetTable2 A,
MISAsset.dbo.tblMISFixedAssetTable B
where A.BarCodeID = B.BarCodeID
and A.UserName = @USERNAME
and B.MobileEquipment = "-1"
and A.LastUpdate = (Select Max(C.LastUpdate)
FROM MISAsset.dbo.tblMISFixedAssetTable2 C
WHERE C.BarCodeID = A.BarCodeID)'
EXEC ("PRINT 'Emailing the user * " + @USERNAME + " with inventory items.*'")
END
FETCH NEXT FROM USERNAME_CURSOR INTO @USERNAME
END
DEALLOCATE USERNAME_CURSOR
PRINT ' '
PRINT 'Done!'
PRINT ' '
go
Hi,
Is it possible to send two files as attachments
in a single email using xp_sendmail?
Thanks,
Ann