SQL Statement in Active X not working

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@mongo.com"
.To = "mongo@mongo.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:
> 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 :)

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"

SQL Statement in Active X not working

SQL Statement in Active X not working

RE: SQL Statement in Active X not working

Re: SQL Statement in Active X not working

Re: SQL Statement in Active X not working

Re: SQL Statement in Active X not working