I presume you have SQLXML3 SP3 installed? if not use this link to get it
http://www.microsoft.com/downloads/d...DisplayLang=en
Your SQL Agent is running as a Domain user for example KWDomain\SQLService? or is it a Local user like KWSQLBox\SQLService? If it is a local user switch it to a domain user that is a member of the local administrators on the SQL Server.
Have you tried using a local path, i.e. c:\SQLExports\Releases.xml, that exists on the server (have an identical path on your computer as well for testing)? Does that work?
In any case you may find the following code to be more efficient as it allows the SQL Server to return the XML instead of doing it yourself in your VBScript code (the forum is forcing some of the code lines to wrap)
Function Main()
Dim oConn
Dim oComm
Dim stOutput
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
Set stOutput = CreateObject("ADODB.Stream")
stOutput.Open
oConn.Open "provider=SQLXMLOLEDB;data provider=SQLOLEDB;data source=(local);initial catalog=Tempdb;Integrated Security='SSPI';"
oComm.ActiveConnection = oConn
oComm.Properties("ClientSideXML") = "True"
oComm.CommandText = "SELECT release_id ,start_date ,end_date ,title ,information FROM dbo.Release Release FOR XML NESTED ,Elements"
oComm.Properties("Output Stream").Value = stOutput
oComm.Properties("xml root") = "Root"
oComm.Execute , , 1024 ''adExecuteStream
stOutput.SaveToFile "C:\SQL_BagOTricks\ExportXML2.XML" , 2 'adSaveCreateOverwrite
stOutput.Close
oConn.Close
Set oComm = Nothing
Set oConn = Nothing
Set stOutput = Nothing
Main = DTSTaskExecResult_Success
End Function
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com