Email based on query results.

[travisbrown]

I've always sort of avoided DTS jobs, save simple things like data import and export, but now I need to schedule an email based on query results.

I run a query on an orders table. If there are any orders flagged as new, I need to send an email. If not, then finish. I've sent emails in DTS before, but not conditional on the results of a query. How might one get started on this?

Query is simply

SELECT COUNT(order_status) AS Expr1
FROM dbo.tbl_orders
WHERE (order_status = 1)
GROUP BY order_status

or

SELECT order_status
FROM dbo.tbl_orders
WHERE (order_status = 1)

 

[unclerico]

travis, see thread961-1281304, it should give you an idea on how to do this...let me know if it doesn't help

 

[travisbrown]

Thanks. I get the general principle from that post. Can you see if I'm on the right track?

-I create a connection to the db
-Create an execute SQL task with the query.
-Create an output global var called NewOrders set to <other>, param type set to Rowset
-Create an AX object with this code, however, I'm a little unclear on how to link up the AX.

Function Main()
        Dim oResults
        Dim bMail

        'set the oResults variable to retrieve the rowset
        Set oResults = DTSGlobalVariables("NewOrders").Value

IF NOT oResults = NULL THEN
   bMail = SendMail()
END IF

End Function

Function SendMail(iRecCount)
    Dim objMessage
    On Error Resume Next

    Dim myMail
    Dim iCount

    Set myMail=CreateObject("CDO.Message")

    myMail.Subject="test title"
    myMail.From="travis@test.ca"
    myMail.To="travis@test.ca"
    myMail.TextBody="test"
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")=2[/URL]
    'Name or IP of remote SMTP server
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] _
        ="smtpservername"
    'Server port
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] _
        =25 
    myMail.Configuration.Fields.Update
    myMail.Send
    set myMail=nothing

End Function

 

[AlexCuse]

This part:

IF NOT oResults = NULL THEN
   bMail = SendMail()

Should read something like this:

IF oResults.RecordCount <> 0 THEN
   bMail = SendMail(oResults.RecordCount)

I think that the rowcount will return a zero, not a null. You also are not passing the global variable value to your mail function in your current setup.

Also, ="smtpservername" needs to be changed to your SMTP server's IP address

You would also want to include iRecCount in your mail output somewhere I imagine?

Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[travisbrown]

Missed a couple things in the post. Sorry.

I don't need to send the rowcount value. Just an email saying, "there's orders here." This is why I wasn't passing in a variable to the email. Forgot to take it out of the function.

Will this query return NULL or 0, or should I use the count function so it will always return an integer?

SELECT order_status
FROM dbo.tbl_orders
WHERE (order_status = 1)

Here's what i meant to put:

Function Main()
        Dim oResults

        Set oResults = DTSGlobalVariables("NewOrders").Value

IF NOT oResults = NULL
  CALL SendMail()
END IF

End Function

Function SendMail()...

 

[AlexCuse]

Because you are returning a rowcount I believe it would be zero (not null).

Try removing your IF NOT NULL constraint, and pass the variable to your test email to see what it reads. My money is on zero but I am not 100% sure.

When running 'select * from table where 1=2' it returns zero rows, and if you try the same with a delete query rowcount returns '0 rows deleted'. So it should be zero by my estimation.

Hope this helps,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[travisbrown]

Here's what I've got, though I'm getting a type mismatch on this line: Set oResults = DTSGlobalVariables("NewOrders").Value. I think it probably relates back to setting up the global param. To revisit,

- I set up a Execute SQL Task
- SQL = SELECT order_status FROM dbo.tbl_orders WHERE(order_status = 1)
- create output parameter called NewOrders with type <other> (this automatically gets switched to "Dispatch" and value to <not displayable> when I save.)
- select Rowset and set the combo to NewOrders

Function Main()
        Dim oResults
        Set oResults = DTSGlobalVariables("NewOrders").Value
       	 IF  oResults > 0 THEN
        		CALL SendMail()
	END IF
End Function


Function SendMail()
        Dim objMessage

    Dim myMail

    Set myMail=CreateObject("CDO.Message")

    myMail.Subject="test title"
    myMail.From="travis@lacuna.ca"
    myMail.To="travis@lacuna.ca"
    myMail.TextBody="test"
   ' myMail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")=2[/URL]
   ' myMail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] ="smtpservername"
    'Server port
   ' myMail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL]  =25 
   ' myMail.Configuration.Fields.Update
    myMail.Send
    set myMail=nothing

End Function

 

[travisbrown]

Oh, ignore the commented out lines in CDO. I haven't got that far yet.

 

[AlexCuse]

Travis -

You want to call your function in a similar fashion to Rico's, so that the step returns success or failure.

That being said, your problem is that you need to format your variable as an integer for it to be used in the > 0 statement. I am having trouble getting my head around that. Perhaps set up an ActiveX to set a second global variable (integer) to the value returned to NewOrders?

Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[travisbrown]

I think the success or failure step is for what to do after the code executes. I don't have a next step, so I don't think it's relevant though.

In any case, the code isn't getting that far yet. I'll futz around, but I don't know what you mean setting the var with a second AX object.

 

[travisbrown]

I set it up like this, with a success step going to a sendmail. Still can't get teh param passed correctly: type mismatch.

Function Main()
        	Dim oResults
        	Set oResults = DTSGlobalVariables("NewOrders").Value
       		IF  oResults > 0 THEN
        		 	Main = DTSTaskExecResult_Success
		END IF
End Function


CALL Main()

 

[travisbrown]

I changed the query to COUNT and changed the output param to Row Value. Works now.

Used this:

Function Main()
IF DTSGlobalVariables("Rows").Value > 0 THEN
Main = DTSTaskExecResult_Success
END IF
End Function

CALL Main()