p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server DTS (http://p2p.wrox.com/forumdisplay.php?f=108)
-   -   DTS Package Export Data to XML Problem (http://p2p.wrox.com/showthread.php?t=36711)

kwilliams January 3rd, 2006 01:17 PM

DTS Package Export Data to XML Problem
 
I have a DTS Package that uses an ActiveX Script using DOM to pull data from a database table, put XML tags around each field, and save that XML-formatted data to an XML file on the same server. I've included the ActiveX Script at the bottom of this post.

It works great when I have the data saved to a local path, but when I try to change it to a network path, I get this error message:

Windows - Delayed Write Failed
Windows was unable to save all the data for this file \SERVERNAME\DIRECTORY\docs\xml\sample.xml. The data has been lost. The error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere.

I think that the problem may be whether or not the SQL Server Agent has read/write permissions on that server, but I'm not sure how to set this up. If anyone could help me out with this process, that would be great. Thanks for any help.

ActiveX Script
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 tblTABLENAME 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

        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 "\\SERVERNAME\DIRECTORY\docs\xml\XMLFILE.xml" '<--THIS DOESN'T WORK
'    objXMLDoc.Save "B:\docs\xml\XMLFILE.xml" '<--THIS WORKS
    Main = DTSTaskExecResult_Success
End Function

KWilliams

David_the_DBA January 3rd, 2006 02:22 PM

KWilliams
What account is the SQL Server Agent Service running as? Is it running as localsystem? Is it running as a local user eg. Computername\SqlAgentUser? Is running as a domain user eg. Domain\SqlAgentUser?

Local System has no ability to see any resources outside the local computer.
A Local user can only see resources on other computers if there happens to be a local user on that computer with the same name and the same password, and has the appropriate permissions.
A Domain user can see resources on any domain member where he has permissions.

If it is not a domain user then that is the first problem. If it is a domain user then I suggest the following:
Logon to your sql server as the user account that runs the SQL Server Agent Service. Then goto the \\SERVERNAME\DIRECTORY\docs\xml directory and then create a file there. If this is successful then you should have no problems running the job.

If not successful then try and access the \\SERVERNAME\DIRECTORY this will ensure that you at least have read permissions to the share named DIRECTORY. You can check the permissions of the Share using Computer Manager, connecting to the SERVERNAME computer and then clicking on Shared Folders, and then clicking on Shares. Then right click on the share called DIRECTORY and select properties. Then you can look and see if the permissions at the share level allow the SQLAgentUser write (Must be Change or Full Control).

Then navigate to \\SERVERNAME\DIRECTORY\docs\ directory right click on the xml directory and select Properties. Go to the Security Tab and ensure that the NTFS permission permit the SQLAgentUser the ability to write and delete files.


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com


All times are GMT -4. The time now is 05:31 AM.

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