Urgent!!! Cannot Create ActiveX Component.Excel from SQL Server 2005 Job

Question

Post reply

Urgent!!! Cannot Create ActiveX Component.Excel from SQL Server 2005 Job

yoginraval

Old Hand

Points: 394
More actions
March 14, 2009 at 7:58 am

#94739

Hi All,
I need urgent help on this issue I'm facing in calling an executable from SQL Server 2005 Job. The Job is executing under an domain user account (say XXXX) and it is calling an executable developed in .Net which tries to create an excel file. The code to create excel object in
Set objExcel = CreateObject("Excel.Application")
At this line I'm getting an error "Cannot Create ActiveX Component.Excel" and it is not creating the excel file.
If I logon to the same server with domain user account (say XXXX) and launch the same executable by double clicking on it. It works fine and excel file is created as expected. But when executable is called from SQL Server Job it is erroring out.
The domain user account (XXXX) is added to Administrator group on the server and SQL Server Agent also runs under same domain user account (XXXX). Office 2007 is installed on the server.
Let me know what I'm doing wrong? Do I need to do any special settings to make this work.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Could you post the whole error message from job history?

Thanks

Flo

yoginraval Old Hand Points: 394 More actions · Answer 2

The job is going thru fine and it is not showing any errors, the executable called from the job is throwing the error. But when same executable is launched manually by the user, it is going thru fine. This makes me think that there is no problem with executable itself, but may be how SQL job is calling that executable.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

If your executable is throwing an error usually the error should be logged into the job history.

Does your executable support any logging? Maybe, if you wrote it, try to enclose your execution with a try-catch block and log the complete error message (and the stack trace) to a text file. You wrote that you are using a .Net executable your posted code snippet looks more like vb-script. Is there any script between the job and the executable?

Does your executable work with some other files? If yes, do you work with relative pathes? If yes, log the current working directory to a file.

I'm out now for some hours but check your reply later.

Good luck!

Flo

yoginraval Old Hand Points: 394 More actions · Answer 4

Hi Flo

There is no script between job and executable. I'm handling any errors thrown by executable in try, catch block and may be because of that it now logged in SQL Job history. The executable doesn't work with other files and it doesn't use any relative paths. Below is the piece of code written in .Net executable

Try

Dim lstrFilepath As String

lstrFilepath = "d:\temp\test.xls"

'Assigning

Call WriteError("Before creating excel object", System.Diagnostics.TraceEventType.Information)

xlapp = CreateObject("Excel.Application")

Call WriteError("After creating excel object", System.Diagnostics.TraceEventType.Information)

xlapp.DisplayAlerts = False

Catch

yoginraval Old Hand Points: 394 More actions · Answer 5

Hi Flo

There is no script between job and executable. I'm handling any errors thrown by executable in try, catch block and may be because of that it now logged in SQL Job history. The executable doesn't work with other files and it doesn't use any relative paths. Below is the piece of code written in .Net executable

Try

Dim lstrFilepath As String

lstrFilepath = "d:\temp\test.xls"

Dim objExcel as Object

'Assigning

Call WriteError("Before creating excel object", System.Diagnostics.TraceEventType.Information)

objExcel= CreateObject("Excel.Application")

Call WriteError("After creating excel object", System.Diagnostics.TraceEventType.Information)

objExcel.DisplayAlerts = False

Catch

Call WriteError(exp.Message + "Excel", System.Diagnostics.TraceEventType.Error)

End Try

The log created by above shown piece fo code

Before creating excel object

Cannot create ActiveX component.Excel

What amuses is same piece of code is working fine when executable is launched manually but when executable is launched from SQL Server job it throws exception.

Hope this helps you to find what I'm I missing!! Thanks

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Hi

Sorry I really don't see any problem within the code... 🙁

Maybe also log the stack trace of the thrown exception to see which part of .Net internal code throws it, but I think cannot help any more. I hope somebody else knows the problem.

Wish you best!

Flo

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

Jeff Moden (3/14/2009)
You've probably hit on one of the only places I've ever used SQL_Variant... logging in EAV's/NVP's.

Thank's for your affirmation Jeff! It's also the only business case where I'm using them.

Greets

Flo

Mohan.deval Say Hey Kid Points: 713 More actions · Answer 8

Hi yoginraval ,

Did you ever get a solution for your issue ?...

I am in the same boat.

I am trying to create an Excel file in Script task, and fails some times while making it run as a job ( 7 out of 10 times ) complaining about cannot create Active-x Object at CreateObject("Exel.Application") statement, executes successfully 3 times creating an excel file ( !! ) works 10 out of 10 times while trying to run from VS2008 . Server is SQl2008R2. Created a sysProxy account as administrator & running SSQLAgent service on it, JOB & Package is owned by sa..

Thanx, in advance