I am running 64-bit SQL 2000 sp3 on top of Windows 2003 Advanced Server and
Windows Cluster Services. I have a DTS package which loads data from an xml
file in Sharepoint. I am able to run it successfully from EM using the SQL
Agent service account sql_agent, but it fails when it is scheduled under
sql_agent. I am expecting some sort of permissions issue, but cannot get
enough error information to isolate the problem.

The relevant code snippet is -

Function Main()
Dim objSrcADORS
Dim objDestADORS
Dim ObjSrcFields
Dim ObjSrcField
Dim HRRepValue

Const adOpenForwardOnly = 0
Const adLockBatchOptimistic = 4
Const adCmdFile = 256
Const adAddNew = 16778240
Const adOpenKeyset = 1
Const adLockOptimistic = 3

Set objSrcADORS = CreateObject("ADODB.Recordset")
Set objDestADORS = CreateObject("ADODB.Recordset")

'The XML File Recordset (Source)
objSrcADORS.Open
"http://intranet.xxxxxxxx.com/sites/hr/_vti_bin/owssvr.dll?XMLDATA=1&List={62FFC77D-6748-44F8-A04A-14B0A90C0136}", "Provider=MSPersist;", _
adOpenForwardOnly, adLockBatchOptimistic, adCmdFile

where it gives an error on the last line. The error as reported by the SQL
Server Agent is -

Executed as user: xxxxxx\SQL_Agent. ...uting... DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error
string: Error Code: 0 Error Source= Microsoft OLEDB Persistence Provider
Error Description: Unspecified error. Error on Line 18 Error
source: Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 4500 Error Detail Records: Error:
-2147220482 (800403FE); Provider Error: 0 (0) Error string: Error
Code: 0 Error Source= Microsoft OLEDB Persistence Provider Error
Description: Unspecified error. Error on Line 18 Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 4500 Error: -2147467259 (80004005);
Provider Error: 0 (0) Error string: . The step failed.

xxxxxx\SQL_Agent is a domain account which is a member of the administrators
group on the SQL box.I had this problem once before, and by putting
http://intranet.xxxxxxxx.com in the intranet sites and altering the security,
I was able to access a similar xml. from the same intranet site, although by
a different user-id. If I manually load the xml into IE and then save it
locally, it works, but since it is HR data, they are wary of where the file
gets moved around to. Logging the package has not provided any more info.

Any ideas?

Hello Geoff,

This error 80004005 would tell me it is either permisions or the resource
could not be found.

You might try logging in the package itself

Also have a look here

http://support.microsoft.com/?kbid=269074

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Allan,
Thanks for the response. I had looked at the KnowledgeBase article until
I was very confused.

I enabled package logging for that DTS and then got the following

-------
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft OLEDB Persistence Provider
Error Description: Unspecified error.

Error on Line 18
(Microsoft OLEDB Persistence Provider (80004005): Unspecified error.)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500
-------

so at least the 800403FE is something different. It googled to an error of
cannot create object or permissions denied, which is not surprising.

I have copied the DTSRUN command from the job and run it successfully in
a command prompt window. Currently, I have the job running successfully from
another server with a different SQL Server Agent account. Current plans are
to uncluster this server to free up resources for a SQL 2005 testbed, at
which time I should be able to switch the SQL Server Agent account on this
server to match the other.

Thanks again for looking at this
Geoff Bates
"Allan Mitchell" wrote:

Hello Geoff,


Ok So the error still says to me that something cannot be foind or there
is a perms failure. That said the 80040005 error is pretty generic.

An article i found though was this

http://www.kbalertz.com/329791/MSPer...correctly.aspx


Could there be anything in this?

On the server where it does work (Scheduled) what differs from the failing
server?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Allan,

I checked out the versions of MDAC between the servers. The job is
running successfully from a server with MDAC 2.8 RTM; it is failing from a
server with MDAC 2.8 sp2. The two SQL Server Agents are running under
separate accounts, but the accounts seem identical in their attributes. I can
only assume there is some subtle difference between them that works or
doesn't, because I agree that it is some permissions issue. When I get the
opportunity, I will switch the unsuccessful one over to the other account to
check the results. I should be consistent with accounts between servers, but
this one got away from me.

Thanks
Geoff

"Allan Mitchell" wrote: