March 30, 2005 at 9:35 am
I have created a DTS package that runs fine when I execute it from the designer. What it does is import a table from an Access database, that I put out on a network share, into a table in SQLServer 7. I get the following error when I schedule the report to run in SQLServer:
Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8) Error string: The Microsoft Jet database engine cannot open the file '\\Hippo\Shared\Accessdb.mdb'. It is already opened exclusively by another user, or you need permission to view its data. Error source: Microsoft... Process Exit Code 1. The step failed.
Any help?
March 30, 2005 at 11:20 am
I'd put my money on a permissions problem. When you execute a package from DTS Designer on your workstation, it runs on your workstation under your security context. When you schedule a package, it runs on the server under the security context of the SQL Server Agent account. If that account doesn't have permission to access the network share, you'll get an error.
Hope that helps.
Greg
Greg
March 30, 2005 at 11:27 am
Greg,
The service runs under the Local System account. Would there be a need to have it start under a different account and assign permissions to that account?
March 30, 2005 at 12:19 pm
Yes, if the network share where the Access database is located is on a different server than SQL Server. You'd need to use a domain account with permissions for the share.
Greg
Greg
March 30, 2005 at 12:23 pm
Greg,
To change the account that the SQLServer Agent runs under I'm pretty sure I have to stop the service, change the startup options to use a Domain Account, then restart the service. Is there any need to change what account SQLServer starts under or can I leave it as the Local System account?
Regards,
Bessebo
March 30, 2005 at 12:41 pm
You can leave the SQLServer service account as Local System.
Greg
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply