September 29, 2008 at 1:15 am
Hi Friends,
I need to get values from table and send a mail to users adding that value as subject.
I have a table server.It has two columns 1.server 2.status.I need to get servername which status as 'n' and send mail as subject of that value.
Thanks,
October 1, 2008 at 2:22 am
Hi
The best way I can think of doing this is by using the xp_sendmail stored procedure as follows:
declare @NewSubject varchar(50)
select @NewSubject = server
from table
where status = 'n'
exec xp_sendmail @recipients='Recipients', @message='Message', @subject=@NewSubject
Hope this helps
October 1, 2008 at 3:48 am
Hi,
are you talking about doing this as part of an SSIS package?
October 1, 2008 at 3:58 am
Hi
The way I discribed above is how one would do it in TSQL. To use the code in SSIS you can pop it in a Execute SQL Task.
October 1, 2008 at 4:03 am
Sorry Christo - didn't make it clear that I was replying to the original poster to try and narrow down his requirements. I'm assuming that as it's posted in the BI section that it's more likely to be SSIS than T-SQL, but assumptions have a funny way of blowing up in your face if you act on them without checking
October 2, 2008 at 12:12 am
No worries.
Would it be possible in SSIS to get the server name using an SQL task and then assigning it to a variable in SSIS which can then be used in a Send Mail Task as the subject?
October 2, 2008 at 4:31 am
Christo Wolmarans (10/2/2008)
No worries.Would it be possible in SSIS to get the server name using an SQL task and then assigning it to a variable in SSIS which can then be used in a Send Mail Task as the subject?
No problem - you can just use the MachineName variable, and if you want the package too then use the PackageName variable.
When sending messages from SSIS my personal preference is to have the distribution lists, header, basic info to go into the body in a table with an identifier (eg FailureWarning) in a table in the target database and populate the variables used in the sendmail task from a sp (spEmailDetailsGet 'FailureWarning' ..... output parameters) then use a script task to add any further details and tag the machine name and package onto the bottom so you know where you're looking if you have to make changes - eg
Public Sub Main()
Try
Dts.Variables("EMailBody").Value = _
& vbCrLf & _ Dts.Variables("EMailBody").Value
& vbCrLf & _
Code to add in further details as appropriate
& vbCrLf & _
vbCrLf & _
vbCrLf & _
"This message is brought to you by;" & vbCrLf & _
Dts.Variables("MachineName").Value.ToString & "::" & Dts.Variables("PackageName").Value.ToString
Catch ex As Exception
Throw ex
Finally
Dts.TaskResult = Dts.Results.Success
End Try
End Sub
October 2, 2008 at 5:59 am
Ok that makes sense.
I presume that you then just assign the EMailBody variable to the subject of the sendmail task and you're all sorted.
The main thing is that you need to assign the values to the dts variables in a SQL Task and then assign the variables to the properties in the send mail task
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply