I put a SQL Statement in my ActiveX and I can't get the correct
Textbody to be sent. The Sql Statement returns a 1 in query analyzer.
However, when the email is sent from this DTS Task, it returns

"No Errors Found" and there are no file attachements.

Any pointers on where I am going wrong here? Thanks


************************************************** ********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim sSQLStatement

' Build new SQL Statement
sSQLStatement = "if exists (select * from table1 (with nolock) where
id = 1011) select 1 else select 0"


Dim yesterday 'As Date
Dim yesterdaystr 'As String
yesterday = DateAdd ("d" , -1 , Now() )
yesterdaystr = Year(yesterday) & "/" & Month(yesterday) & "/"
& Day(yesterday)

sch = "http://schemas.microsoft.com/cdo/configuration/"

Set cdoConfig = CreateObject("CDO.Configuration")

With cdoConfig.Fields
.Item(sch & "sendusing") = 2 ' cdoSendUsingPort
.Item(sch & "smtpserver") = "qarelay1"
.update
End With

Set cdoMessage = CreateObject("CDO.Message")

With cdoMessage
Set .Configuration = cdoConfig
.From = "mongo (AT) mongo (DOT) com"
.To = "mongo (AT) mongo (DOT) com"
.Subject = "daily reports."

'Both files must be in error to send the following text message


If sSQLStatement ="1" then



.TextBody ="Errors Found"
Else



.TextBody = "No Errors Found"

End If

'Attach file only if error found


If sSQLStatement ="1" then


.AddAttachment ("\\archive\Daily\Report.xls")

End If

End With

Set cdoMessage = Nothing
Set cdoConfig = Nothing


Main = DTSTaskExecResult_Success
End Function

You forgot to run the statement in sSQLStatement against the database. It is
just a string value now!

And, of course, the value "if exists (select * from table1 (with nolock)
where id = 1011) select 1 else select 0" is not 1 or 0

Thanks

Is there a resource where I can read up on how to call a sql statement
in VB Script?


Frans van Bree wrote:

I always use an UDL file for my connections. You can create one by creating
an empty text file called myDB.udl and double-clicking it. Rest should be
straightforward. Now some code (adapt it to your own needs) I am doing this
from my head, e.g. fields(0) might be fields(1).

But give it a try, I think it will help you further:

Dim oConn
Dim sSQLStatement

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "File Name=c:\myDB.udl"
sSQLStatement = "if exists (select * from table1 (with nolock) where
id = 1011) select 1 else select 0"
Set oRs = CreateObject("ADODB.Recordset")
oRs.Open sSQL, oConn

If oRs.Fields(0).Value = 1 then
..TextBody ="Errors Found"
Else ...

etc

oRs.Open sSQL, oConn

should be

oRs.Open sSQLStatement, oConn

and if you don't want to use an udl, look here:
www.connectionstrings.com

Put the appropriate string where it says "c:\myDB.udl"