Solved

Launching a DTS package through a Stored Procedure and writing to Excel

Posted on 2008-06-18
2
601 Views
Last Modified: 2013-11-30
I'm looking at a DTS Package here that is launched from a Stored Procedure. The DTS Package writes data to an Excel spreadsheet. For this it uses a JET connection (apparently!)

The problem is this: When I go in through Enterprise Manager, and I Execute the DTS Package by simply clicking on it, it runs fine. It creates the desired Excel spreadsheet, and it populates it.

However, if the DTS packages is lanuched through a Stored Procedure, the JET engine throws a wobbly. It reports error 80004005, with a "help context" (whatever that may be) of 5000000. To be extra helpful, it reports that this is an "unspecified error".

Googled this to death but I'm seeing nothing that strikes me as particularly valid for my situation. In fact, it looks like the same symptoms could result from various different causes. However, I think I can eliminate a few:

- I don't think there is a problem with DTS as I can run the package manually, and it succeeds.
- I don't think there is a problem with the JET Engine per se, for the same reason.
- It isn't a case of having mis-spelled the name of the DTS package. In fact, the DTS Package produces a log file. From the Log file it is clear that the DTS Package is found and executed, and all the steps are visited in the correct order. Except, they then fail. As all steps are designed to continue after completion, regardless of their status, the DTS package as a whole succeeds:

The execution of the following DTS Package succeeded:

Package Name: [name removed to protect the innocent]
Package Description: Create Excel Extracts for [yada, yada]
Package ID: {C7DCF808-6D75-4C1D-9BE2-56B3E143C273}
Package Version: {F90DE123-4396-4937-BA9E-ECF919537620}
Package Execution Lineage: {B74403F4-6318-4DC1-8F03-88086A2C64C6}
Executed On: [Our server here] (and yes, I verified that this is correct)
Executed By: [I can confirm that this is the user that I would like to see here]
Execution Started: 18/06/2008 12:10:01
Execution Completed: 18/06/2008 12:10:08
Total Execution Time: 7.093 seconds

But this is a typical step from that package:
Package Steps execution information:


Step 'DTSStep_DTSExecuteSQLTask_1' failed

Step Error Source: Microsoft JET Database Engine
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5000000

Step Execution Started: 18/06/2008 12:10:01
Step Execution Completed: 18/06/2008 12:10:01
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0


There also does not appear to be a problem with Active X itself, as one of the first steps in the DTS package creates an instance of Scripting.FileSystemObject which is then used to delete any existing copy of the spreadsheet, and that step succeeds without an error, and it does delete the file in question.

The Stored Procedure executes the DTS Package as follows:
      EXEC @iErrorValue = sp_OAMethod @iPackageObject,
      'LoadFromSqlServer',
      NULL,
      @ServerName = @ServerName,
      @ServerUserName = @ServerUserName,
      @PackageName = @PackageName,
      @Flags = 256, -- Use Windows Authentication to connect to an instance of SQL Server.
      @PackagePassword = '',
      @ServerPassword = @ServerPassword

I also tried running this with @Flags set to 0, and also for a specific ServerUserName and Password, both for @Flags = 0 and @Flags = 256, but every time it causes the same error.

At this stage I'd be inclined to think that this would be a permissions and/or privileges issue. But I tried writing the Excel spreadsheet to the %TEMP% folder (resolving the name to the actual physical locationl, of course) in the hope that there should be no issues with that folder, and I have tried giving various users, including, in the end, "Everyone" full access to the target folder. All to no avail.

Please help me before I start screaming and I create an unholy scene here in the office that will dwarf the following: http://www.youtube.com/watch?v=7Sq-HYGfnIo

May the FSM bless you and touch you with its noodly appendage.
0
Comment
Question by:WernerVonBraun
2 Comments
 
LVL 13

Accepted Solution

by:
rickchild earned 500 total points
ID: 21812035
It does sound like permissions.  

How is the stored procedure being run, and who is running it, the Agent or a person?
If the agent is running it from a job, check that the owner of the job has permissions.
If the SP is being run by an application, what account is being used here?


Also which user account is running your "MSSQLSERVER" Service in Windows?
We would use a service account here for the Services, which would be given access to write to the folder.
0
 
LVL 4

Author Comment

by:WernerVonBraun
ID: 21812233
So close!

I had actually stopped and started the SQLServerAgent service. But not the MSSQLSERVER one. I did that, and Robert turned out to be my mother's brother.

You're a star.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now