May 7, 2008 at 10:41 am
Hi All,
I have a SQL Server 2000 DTS job that exports 5 views from my database to Excel format on a secured network folder (The G: Drive in this case).
When I run the DTS job myself manually on my own pc in Enterprise Manager, the job runs perfectly.
I can also run the DTS job on the server itself manually and it runs just fine.
Where we run into problems is when the DTS job is added to a monthly scheduled job.
The user that runs the job is a service account that has full rights to the G: Drive folder, so there shouldn't be a security issue here. The service account also has 'sa' privileges on the SQL Server, which was a requirement since my own SQL Server account did not have the proper privileges to run scheduled jobs.
Do you know where the issue might be in this scenario? Any ideas?
Thanks!
Rich
May 7, 2008 at 11:48 am
Please post the error that occurs when the package is run in a scheduled job. This sure sounds like a permissions problem so I want to clarify something. A scheduled job is run as the login that runs SQL Server Agent. Is that the service account that runs the job?
Greg
May 7, 2008 at 1:57 pm
Thanks! Yes, the service account was set to run the job. Here's the error:
I gave the cdmservice SA privileges and set it to run at 2:45 to test it and here is the error message I received in the job history when it failed:
Executed as user: AUSSHP\SYSTEM. ...OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: Create Table COMBINEDCDM Step DTSRun OnError: Create Table COMBINEDCDM Step, Error = -2147467259 (80004005) Error string: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. Error source: Microsoft JET Database Engine Help file: Help context: 5003051 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -67568648 (FBF8FBF8) Error string: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. Error source: Microsoft JET Database Engine Help file: Help context: 5003051 DTSRun OnFinish: Create Table COMBINEDCDM Step DTSRun OnStart: Copy Data from... Process Exit Code 10. The step failed.
May 7, 2008 at 4:10 pm
Is the service account a domain login?
Greg
May 7, 2008 at 5:58 pm
Greg Charles (5/7/2008)
Is the service account a domain login?
Hi Greg,
Yes, the service account is called "cdmservice" and is a domain login account with full rights to the G: Drive I need to write the new Excel file to. This account also has system administrator rights on the SQL Server.
May 8, 2008 at 9:28 am
I'm stumped. Maybe you should enable package logging and see if you get any more error details.
Greg
May 8, 2008 at 1:58 pm
Greg Charles (5/8/2008)
I'm stumped. Maybe you should enable package logging and see if you get any more error details.
That's OK Greg...the IS database admins are all stumped, too. They've seen this issue before with other DTS jobs that end with an export to Excel on a shared drive.
June 23, 2008 at 3:45 pm
It looks as a "Lock" problem. SQL Server tends to switch to PAGE LOCKING when dealing with larger amounts of data. This is an automatic mechanism that occurs to streamline jobs.
So when you execute the job manually, it works fine, but when executed, perhaps in concert with other job, the exclusivity of the data is no longer valid.
Just a thought.
June 24, 2008 at 3:10 pm
Um, the record set doesn't exceed 64,000 records, does it? Not a security issue but it might create a problem.
Sorry if not applicable. Just trying to address a fresh approach. Others have thoroughly covered the permissions/security aspect.
August 19, 2008 at 4:12 pm
I have this exact same issue. I have use a domain admin account and still have the issue, so it may be some sort of locking issue as stated previously. Has anyone been able to resolve this issue if they encountered it?
August 19, 2008 at 4:41 pm
Actually I figured it out. It was indeed a rights issue. I ran the "move" command from a scheduled task and I got a more detailed error on the user that was being used to run the DTS job and then gave the needed permissions. All works now...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply