March 14, 2009 at 7:58 am
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.
March 14, 2009 at 8:30 am
Hi
Could you post the whole error message from job history?
Thanks
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 14, 2009 at 8:38 am
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.
March 14, 2009 at 8:48 am
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
How to Post Data/Code to get the best Help How to Post Performance Problems
March 14, 2009 at 9:35 am
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
March 14, 2009 at 9:41 am
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
March 14, 2009 at 12:59 pm
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
How to Post Data/Code to get the best Help How to Post Performance Problems
March 14, 2009 at 1:02 pm
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
How to Post Data/Code to get the best Help How to Post Performance Problems
January 21, 2011 at 11:53 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply