I have a stored procedure that createz a DTS package on the fly using
the sp_OA... stored procs. The procedure copies data from a Sql table
into an Access .mdb file.

When the procedure is executed in a Sql Server Agent step, it inserts
rows into the Access file, but fails before finishing.
The logfile states that

Step error description : The number of failing rows exceeds the maximum
specified. (which is zero)
Step error code : 8004206A

Running the DTS package any other way succeeds (I save the package to a
structured storage file before it gets executed so that I can run it
other ways).

What would cause some rows to fail when using Sql Server Agent?
There's only one source table for all of teh data, so security is not
the issue.

Any clues?

I think you need to check the identity of the SQL Server Agent (by opening
SQL Server Agent properties) and change the identity with user that have
access on both the SQL server table (inside the database) and the MDB file
(file system)
HTH.

Regards,
Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC

If that was the cause, the process wouldn't even start.

It always stops at the same number of rows inserted into the Access table.

In fact, I setup another process that uses a Sql server table that is structurally an identical twin but contains data from a prior month.

The second process also fails at exactly the same record number.

I changed the destination of the Access table to a destination on the local Sql Server box, get the same behavior.

I added more columns to the process, which meant the disk size of the Access table was larger, and the process stopped at the same point, so size limitations are not an issue.



nntp://msnews.microsoft.com/microsoft.public.sqlserver.dts/<GXL51CR3EHA.3200 (AT) cpmsftngxa10 (DOT) phx.gbl>

I think you need to check the identity of the SQL Server Agent (by opening
SQL Server Agent properties) and change the identity with user that have
access on both the SQL server table (inside the database) and the MDB file
(file system)
HTH.

Regards,
Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC


[microsoft.public.sqlserver.dts]