January 25, 2005 at 1:25 pm
I'm having a problem importing data from an access database stored on a network share into our SQL 7.0 server. I have created a local package to delete the records from the SQL table (under 200 records always) and then the local package is to import the data from the Access database into the SLQ Table. This package failed each time I executed in manually, when it got to the import step. I then created a package that only deleted the records from the SQL Table, and it worked fine when executed manually or scheduled. I then created another local package that imported the data from the Access 2000 database into the SQL table. This package worked when executed manually, but failed each time it was scheduled. I then decided to save that package as a DTS file and create a job under SQL Server Agent. That job also failed to perform as scheduled. The error messages were less than helpful, as they basically stated that the job failed, without any additional information.
I've done some research online, but haven't really seen anything that addresses this particular issue. I'm especially perplexed that the step in question works when executed manually, but fails when scheduled. Any assistance with this would be appreciated.
Thanks
January 27, 2005 at 11:49 am
James,
The import package may be failing when scheduled because the login used by SQL Server Agent doesn't have access to the Access 2000 database location.
Keep in mind that when a package is scheduled, it runs on the server under the security context of the SQL Server Agent login. When you execute a package in Enterprise Manager from your workstation, it executes on your workstation under your security context.
Greg
Greg
January 28, 2005 at 10:12 am
You may also want to consider "where" the file is located. For instance, if locally you reference the Access database (i use DB lightly with MS Access) from a mapped drive on your machine (e.g. H:\DB\DemoDB.MDB), you must have a drive mapped to H:\ on the server running MSSQL.
The above is just an explanation. The best case is to use UNC names for the location of the MS Access DB. Hope this helps.
JG4SMILE.
solutions...not answers...
-->January 28, 2005 at 10:21 am
I have the drive mapped correctly, as that was one of the initial problems I experienced.
January 28, 2005 at 10:25 am
I'll check the permissions of the SQL Server Agent on the database location, but think it has full control.
As for the other item, I'm actually executing the package on the SQL server from Enterprise Manager, not from my workstation, so that is probably not an issue, but I will check that also. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply