I am trying to export some data from SQL Server to a table in MS-Access. I have setup a DTS object that pulls the data out in a query, drops and rebuilds the table in access, then copies the data over.
Running the DTS package manually, everything works.
When I schedual the package, the scheduled event fails. Looking at the error log, all the actions fail with an "Unspecified Error", ecpect for one:
---
Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
Error string: The Microsoft Jet database engine cannot open the file '\\UNC\Path\To\File\Database.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
---
The ServerAgent is set to use the SA login, and I checked that it definately has access to the file on the network. I also checed the no-one was using the file at the time.
The fact that it works when I run the package from DTS>Local Packages manually, and not when i schedule it for a weekly run is really confusing me.
Is there any known problem wusing SQL Server 7 with Access 2000? I can only think that it is opening the databse to connect, then trying to establish a second connectionwhile the database is still opened (and locked) from the first attempt.
---
The users are now using this system, and the DB is opened before I get in at 9, and closed after I leave at 5:30. So I cant update the table manually at all. I am just waiting to get a call from the users that the records they need arnt in the system.
Running the DTS package manually, everything works.
When I schedual the package, the scheduled event fails. Looking at the error log, all the actions fail with an "Unspecified Error", ecpect for one:
---
Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
Error string: The Microsoft Jet database engine cannot open the file '\\UNC\Path\To\File\Database.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
---
The ServerAgent is set to use the SA login, and I checked that it definately has access to the file on the network. I also checed the no-one was using the file at the time.
The fact that it works when I run the package from DTS>Local Packages manually, and not when i schedule it for a weekly run is really confusing me.
Is there any known problem wusing SQL Server 7 with Access 2000? I can only think that it is opening the databse to connect, then trying to establish a second connectionwhile the database is still opened (and locked) from the first attempt.
---
The users are now using this system, and the DB is opened before I get in at 9, and closed after I leave at 5:30. So I cant update the table manually at all. I am just waiting to get a call from the users that the records they need arnt in the system.