SQL Server 2k to XML using DTS Problem

[kwilliams]

I've created an ActiveX Script that's located within a DTS package to export database table data into an XML file. It works great, but now I need to filter the data to be exported a bit.

The first setup was to get all "archived" records by using the "end_date" and current date, like this:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim objADORS
Dim objXMLDoc

Dim nodeRoot
Dim nodeTemp
Dim nodeRelease

'Create ADO and MSXML DOMDocument Objects
Set objADORS = CreateObject("ADODB.Recordset")
Set objXMLDoc = CreateObject("MSXML2.DOMDocument.3.0")

'Run the stored procedure and load the Recordset
objADORS.Open "SELECT * FROM tblNewsReleases_test", _
"PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=UID;PWD=PWD;DATABASE=DBNAME;"

'Prepare the XML Document
objXMLDoc.loadXML "<root />"
Set nodeRoot = objXMLDoc.documentElement

'For each record in the Recordset
While Not objADORS.EOF and objADORS.Fields("item_date").Value < date()

Set nodeRelease = objXMLDoc.createElement("release")
nodeRoot.appendChild nodeRelease

Set nodeTemp = objXMLDoc.createElement("release_id")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("release_id").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("start_date")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("start_date").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("end_date")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("end_date").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("title")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("title").Value)
nodeRelease.appendChild nodeTemp

Set nodeTemp = objXMLDoc.createElement("information")
nodeTemp.nodeTypedValue = Trim(objADORS.Fields("information").Value)
nodeRelease.appendChild nodeTemp

objADORS.moveNext
Wend

objADORS.Close
Set objADORS = Nothing

'Save the created XML document
objXMLDoc.Save "B:\XMLEXPORTFILE.xml"

Main = DTSTaskExecResult_Success
End Function

But when I try a similar setup to get all records in which the current date is between the "start date" and "end_date" of the record like this, no records come up:
...
While Not objADORS.EOF and objADORS.Fields("start_date").Value < date() and objADORS.Fields("end_date").Value > date()
...

I was easily able to do this in SQL Server using this query:
SELECT *
FROM tblNewsReleases_test
WHERE (start_date < GETDATE()) AND (end_date > GETDATE())

...so I know that I have the right idea. I'm just not sure what I'm doing wrong, and I'm hoping that you can help me out. If anyone can see where I'm making a mistake with the second setup, it would be very helpful. Thanks.

[kwilliams]

I just received a quick reply from the SQLTeam forum, and it worked great for me. Here's what he said:

The "and objADORS.Fields("start_date").Value < date() and objADORS.Fields("end_date").Value > date()"...should be put into your SQL query.

And not into the loop where you are checking the result of your query.

It's a bit like selecting 1000 records, inspecting each and then discarding 995. You should just write a query to look for 5 in the 1st place.

So I changed my script to read like this:
...
'Run the stored procedure and load the Recordset
objADORS.Open "SELECT * FROM tblNewsReleases_test WHERE (start_date < GETDATE()) AND (end_date > GETDATE())", _
"PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=UID;PWD=PWD;DATABASE=DBNAME;"

'Prepare the XML Document
objXMLDoc.loadXML "<root />"
Set nodeRoot = objXMLDoc.documentElement

'For each record in the Recordset
While Not objADORS.EOF
...

...and it works great. Thanks.