SQL Server 2k to XML using DTS Problem

Please start any new threads on our new site at https://forums.sqlteam.com. 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

kwilliams

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", _
		"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

AndrewMurphy
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.

kwilliams

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources