hello,
can xp_sendmail send on behalf of someone, i.e. i need to send it from a sql profile mailbox, but i need the address to read FROM: Someoneelse@domain.com
i know you can do this easily with XPSMTP, but i need a way to do it with xp_sendmail (sql mail)
thanks
Does anyone know if this can be done? I've looked everywhere. This is how my info is appearing in an email...
These are the files I requested and this is how it's being displayed. Any suggestions? Requestor
Requested Shipped
Due
------------------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------
-----------------------------------------------------------------------
---- ------------------------------ ------------------------------
------------------------------
Approaching the Corporate Heart
klm@yahoo.com
02/07/2008 02/07/2008
02/06/2008
PASSION FOR CUSTOMERS 3 copies
Nina.Childress@ssa.gov
Can i send a file as the body of @message
in the extended sp xp_sendmail?
Pls help! thanks for any help.
How yould i loop trought all the records in a table and fetching a specic record that is flagged and sending for each record found a email with that records data to a mail recipient. this should be part of a step in a sql job. PLZ HELP
View 2 Replies View RelatedI am using SQL Mail to send an email. Is there some way to send it with an HTML body?
View 1 Replies View Relatedquote:Originally posted by maya_zakry
hello, can someone fix my multiple case statement..
SELECT col1, col2 from tblOne
CASE
WHEN (substring(d.StorageStatus,1,1)='H') THEN
Sum(d.HoldQty)
WHEN (substring(d.StorageStatus,1,1)='Q') THEN
Sum(d.QCQty)
ELSE
sum(ISNULL(d.OriginQty,0))-sum(ISNULL(d.HoldQty,0))- sum(ISNULL (d.QCQty,0))- sum(ISNULL(d.BookQty,0))- sum(ISNULL(p.PickQty,0)) AS BalQty END
AS BalQty
WHERE blablah
is this multiple case allowed?
NO!
But if you are trying to sum up different columns depening on some status column, try thisselectcol1,
col2,
sum(case
when d.storagestatus like 'h%' then isnull(d.holdqty, 0)
when d.storagestatus like 'q%' then isnull(d.qcqty, 0)
else isnull(d.originqty, 0) - isnull(d.holdqty, 0) - isnull (d.qcqty, 0) - isnull(d.bookqty, 0) - isnull(p.pickqty, 0)
end) as qty
fromtblone
group bycol1,
col2
order bycol1,
col2
Peter Larsson
Helsingborg, Sweden
Hi all ..
I am new to sql server reporting services. I have two parametrized fields(database) in the report and i want to populate the second one on behalf of the first dropdownlist's selected item.
Thanks in advance for your valuable replies
Regards
HI,
is there any statement by using that i can delete data from the table on behalf multi rows.
like
delete from Table where = (select id from table1 where group=1)
second statemetn returns 4 rows by group =1
is there single statment..
Thanks
Picture tells all what i need. Anyway i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012 ....
View 5 Replies View Relatedi want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012.
This is the image path :
[URL]
Hi,
I have a string almost 11006 length.. when i try to send back from SQLCLR procedure
it says cannot send ..
here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."
Max limit to send a string using pipe is 4000
How I can send a string which is large in size than 4000.
Thanks
Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
I have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'
[code]....
Hi Guys..
i don't know weather is it possible or not..but Can any One tell's me How can i refresh the Chart Values.. acutally what's happening..
i have two Chart in a reprot .. One is Main Category and other one is SubCategory... acutally what i want.. in Main Category chart sum of Quantities of Subcategory values comes in bar or any other format.. and when i click on Main chart any bar it's refresh the other chart and return the result of subcategory under that main category in details...
i don't know is this possible .. acutally i m very new in reproting.. infact that's my first report.. so i want to do this.. if any article or any help anyone can provide me..
Thanks
Is there anyway to send excel file from ssis using send mail task without saving the excel file locally. I need to automate the process which involves loading the excel file from the database and send it to some people.
View 6 Replies View RelatedI have attempted to report out errors at the end of an ETL process by alerting supporting DBAs of errors using the SSIS "Send Mail Task". Task completes along with the sequenced packages, but does not mail anything out. No logic at this time for trigger, just success from the previous task triggering the task to send mail. I also get no errors in the output, and I get no output indicating the send mail task fired, but it does go "green". Do I have to enable database mail and have privileges?
Component Configuration:
SMTP Connection Manager - SMTP Server: arsocex02
Send Mail Editor -
From: messerj@arsocdev.bdev.lab.mil
To: sanderss@arsocdev.bdev.lab.mil
MessageSourceType: Direct Input
Expression: MessageSource = "Package>>> " + @[System:ackageName] +" was executed at>>> " + (DT_WSTR, 40) @[System:tartTime] + " by user>>> " + @[System::UserName] + " on Machine>>> " + @[System::MachineName] + " Errors reported to ERRORS_COURSE_CLASS_STATUS_T: " + (DT_WSTR, 50) @[User::ErrorCourseClassStatus]
Thanks
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.