get values from table and send mail

  • 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,

  • 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

  • Hi,

    are you talking about doing this as part of an SSIS package?

  • 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.

  • 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

  • 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?

  • 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

  • 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