Click here to Skip to main content
14,735,366 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hey guys,
I tried to summarize this as much as possible, I am sorry in advance for the length of the text.
I need your help in solving a problem we've been trying to deal with for the past few days.
So the story is this:
we wrote a console application that extracts a large amount of data from a sql server data base and returns it as a data table.
The exe generates an Excel file using a microsoft dll called Microsoft.Office.Interop.Excel.dll and inserts all data from the database.
This Excel is saved in a certain path in the file system and eventually sent to a list of recipients.
My players are as follows:
- The IIS server on which the console application is installed (Let's just call the server 'thziis')
- The sql server on which the database (dahhh) is located (Let's just call the server 'thzSql')
Now the important thing - the exe is enabled as follows:
For business reasons, we run the process only when a entry enters a particular table in the database.
Therefore, we created an insert trigger that listens to the same table. When a new entry is entered,
it runs a job in the sql (which is not scheduled) and the job calls exe (CmdExec).
So what exactly is the problem?
When the process runs in the way I have described now, we get the following error:
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046}
failed due to the following error:
80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

According to the stack trace, the process failed when it tries to create an instance of the excel file:
Stack trace:
at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.Activator.CreateInstance(Type type)
   at ViolationsExcelMail.BL.ExcelGenerator.CreateExcel()
However, when we disable the trigger and manually run the exe (double click on the exe itself), the process works properly and without problems.
After we thoroughly explored the issue, we realized that in dcom config (Dcomcnfg.exe > Console Root > Component Service > Computers > My Computer > DCOM Config)
There must be the following line: Microsoft Excel Application.
At this stage we began to check the settings of the same line and we now know that there is a definition that says this:
"Which user account do you want to use to run this application?"
we selected the launching user (And only then did the process pass manually. Before that, he had always failed).
We need to know how to solve this problem so that the process can run in the original way and automatically, according to a call from the trigger.
In addition, we are subject to regulation that restricts us from executing installations on the sql server so that microsoft office can not be installed on this server,
we have office only in the iis server.
Any suggestion or idea are welcome, we will appreciate it very much. Thank you very much for reading.

What I have tried:

We tried to regsvr32 the DLL through the CMD without success, even with admin permissions.

We tried to install the conole application on the sql server so that it would run from there and we would encounter the same error (as expected)

We tried to run the job that calls the exe with all the possible permissions (including the highest ones) - without success.
Posted
Updated 22-Nov-20 23:28pm
v2
Comments
F-ES Sitecore 27-Mar-18 7:13am
   
Excel is a desktop application, it's not intended for automation from services that have no access to the desktop (which is what you're doing).

You need to forget using Excel for too many reasons to list, it's a desktop app not a service. Use something else to manipulate Excel files that is suitable for automation like the ODBC\OleDb driver or one of the various third party apps that do this.

1 solution

Quote:
microsoft office can not be installed on this server
You cannot automate Microsoft Office unless it is installed.

You also cannot use Office Interop from a non-interactive application, including SQL Server and ASP.NET:
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

There are various ways to create and manipulate Excel spreadsheets on the server without using Office. For example:

The good news is, none of these solutions will require you to install and licence Office on either server.
   
Comments
oronsultan 17-Apr-18 3:01am
   
Dear Richard, Thank you very much for the quick reply (and sorry for my late response). We decided to go with your first solution, EPPlus. Amazing solution. There was no need for any installation on any server, and the Excels look just wonderful (design, graphs, etc.). Again, Thank you very much!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900