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
-------------------
It's the end of the world as we know it...and I feel fine
SQL Server 2k to XML using DTS Problem
Posted - 12/12/2005 : 10:32:34
