SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL Server 2k to XML using DTS Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwilliams
Posting Yak Master

194 Posts

Posted - 12/12/2005 :  10:32:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 12/12/2005 :  10:53:00  Show Profile  Reply with Quote
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

kwilliams
Posting Yak Master

194 Posts

Posted - 12/12/2005 :  11:01:33  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000