www.webdeveloper.com
Results 1 to 2 of 2

Thread: SQL Server 2k to XML using DTS Problem

  1. #1
    Join Date
    May 2003
    Posts
    599

    Question SQL Server 2k to XML using DTS Problem

    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:
    Code:
    '**********************************************************************
    '  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:
    Code:
    ...
    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

  2. #2
    Join Date
    May 2003
    Posts
    599

    Thumbs up

    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:
    Code:
    ...
    '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.
    KWilliams
    -----------------------
    It's the end of the world as we know it...and I feel fine

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles