Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 12th, 2005, 11:43 AM
Banned
 
Join Date: Jul 2005
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
 
Old December 12th, 2005, 12:11 PM
Banned
 
Join Date: Jul 2005
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just received a quick reply from the SQLTeam forum, and it worked great for me. Here's what he said:

Quote:
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.
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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in accessing DTS in SQL SERVER 2005 Jigna SQL Server DTS 1 September 12th, 2007 04:23 PM
SQL Server 2K, Access 2003 and VBA SteveBH SQL Server 2000 1 January 11th, 2007 01:23 PM
AutoIncrement in Insert on SQL Server 2K CarlosV SQL Server 2000 5 January 19th, 2006 06:01 PM
Professional SQL Server Development with Access 2K Fern All Other Wrox Books 0 January 29th, 2004 01:00 AM
Connectivity Problem To SQL Server 2k ctanchan Pro VB Databases 4 October 7th, 2003 11:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.