Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL Server 2k to XML using DTS Problem

Author  Topic 


194 Posts

Posted - 2005-12-12 : 10:32:34
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", _

'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


Set objADORS = Nothing

'Save the created XML document

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:
FROM tblNewsReleases_test
WHERE (start_date < GETDATE()) AND (end_date > GETDATE()) 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.

It's the end of the world as we know it...and I feel fine

Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-12 : 10:53:00
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.
Go to Top of Page


194 Posts

Posted - 2005-12-12 : 11:01:33
Great! It works great, and it even came in a quick reply. Thanks for the heads-up on the better way to do this. I'm always learning, as I am obviously somewhat of a newbie to all of this. Thanks again Andrew.

It's the end of the world as we know it...and I feel fine
Go to Top of Page

- Advertisement -