February 7, 2012 at 10:52 am
Hi all -
I'm at a dead end in my investigations for a possible solution, so I'm hoping someone here has encountered this problem before and has an idea.
Basically, what I have is a SQL Server proc which populates a whole bunch of tables, and then runs a script that creates an Excel file with formatting and such, and outputs it into a destination directory.
Initially I had done the Excel creation part using SSIS and using the Microsoft.Office.Interop.Excel library. This worked perfectly in testing on my computer. The problem is when I tried running it using DTEXEC from SQL Server, I ran in to an error, because the SQL Server machine didn't have Excel installed on it.
I've tried every idea I can think of but nothing seems to work. I've gone with batch files, scheduled tasks, .NET packages, nothing seems to work. Every solution I come up with runs in to one problem or another.
Has anyone faced this situation before, and if so, come up with a working solution?
February 7, 2012 at 10:53 am
Sorry, I should make a few notes:
I know that another solution is to use OpenXML, however, that requires .NET framework 3.X, while the server has access to only 2.X. And, any solution which involves installing software on the server is not going to work, since the server is remotely managed and that's going to cost us money to implement.
February 7, 2012 at 11:47 am
Hah - okay, so I finally found a solution, but boy is it ever convoluted.
The solution:
Use msdb.dbo.sp_start_job to start a job, which is set up as a OperatingSystem (CmdExec) type job. The job uses schtasks, with the script:
schtasks /run /tn "[Name]" /s [Server]
In order to trigger a scheduled task on a machine that has Excel installed (*not* a database server machine, just a normal desktop PC).
The scheduled task points to a VB.NET executable, which contains the script that creates and manipulates the Excel files.
So ... all in all, that's a four-level indirection solution. Man do I hate it, but at least it works :/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply