Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL 2008 server (SQL2008S1) with Excel installed on it. I had to migrate databases and DTS packages from a SQL 2000 server (SQL2000S2) to this server. 1 DTS package is trying to access an Excel file on a remote server like \\SQL2000S2\C$\directory\someExcelFile.xls. The SQL server and agent on SQL2008S1 are both running under same service account (Domain\SQL2008S1_service) and has full permissions on \\SQL2000S2\C$\directory. I even granted Domain\SQL2008S1$ (machine name) full access to \\SQL2000S2\C$\directory\

When I open the DTS package on SQL2008S1 and execute it manually, it runs perfectly fine. But when I schedule it as job, the job reports:

Executed as user: Domain\SQL2008S1_service. ...p_DTSExecutePackageTask_5 DTSRun OnError: DTSStep_DTSExecutePackageTask_5, Error = -2147467259 (80004005) Error string: DTSStep_DTSExecuteSQLTask_2: The Microsoft Jet database engine cannot open the file '\\SQL2000S2\C$\directory\someExcelFile.xls'. It is already opened exclusively by another user, or you need permission to view its data.

I am 100% sure that the files are not open and I thought I have granted enough rights to the SQL agent/SQL service account.

Any help please?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Have you granted the account Domain\SQL2008S1_service admin rights to the server SQL2000S2? You will need to do so in order to use the C$ network share.

share|improve this answer
    
Perfect! No errors in the SQL agent job now. Thanks heaps! –  Manjot Apr 14 '11 at 4:09
    
No problem, happy to help. –  mrdenny Apr 14 '11 at 17:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.