Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 3rd, 2006, 12:17 PM
Friend of Wrox
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
__________________
KWilliams
Reply With Quote
  #2 (permalink)  
Old January 3rd, 2006, 01:22 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , AZ, .
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to export the data to XML file format? mbshankar Pro VB 6 2 April 3rd, 2007 12:22 PM
DTS Package, XML task. Read XML file and store it Victoria SQL Server DTS 0 July 24th, 2006 02:43 PM
Problem loading a saved DTS package afward SQL Server DTS 1 June 1st, 2006 04:30 AM
Inserting data into table from DTS Package Crowandazi SQL Server DTS 1 July 29th, 2005 12:30 AM
Problem Scheduling a job on DTS package. pooh2323 SQL Server DTS 2 February 12th, 2004 01:12 PM



All times are GMT -4. The time now is 08:18 AM.


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