0

I have a service that data logs devices and records the info to a SQL Server. Each night at a random time after midnight I export out multiple excel documents to temp files then upload them to SharePoint. If I run the service code normally (by copying and pasting it to a form app for testing) the export work as expected. But when the service runs it I get a "The remote procedure call failed. (Exception from HRESULT: 0x800706BE)".

Is this a limitation of running the interops as a service? I can't see anything else that it could be since the code does work normally in a form app.

Couple things I have found online that did not help:

  • Make sure the directory C:\Windows\SysWOW64\config\systemprofile\Desktop exists. It does.
  • Make sure you are using local files and not ones form network resources. I am, it creates temp files using IO.Path.GetTempFileName
  • Switched to a user account instead of the Local System account for the service. No change.
ADY
  • 101
  • 1
  • 9
  • Access to COM Interop requires a STA Thread. Try using a Thread that calls `bool result = [Thread].TrySetApartmentState(ApartmentState.STA)` – Jimi Apr 26 '21 at 12:56
  • 1
    You could also add a package that doesn't require Interop to generate MS Office files. – Jimi Apr 26 '21 at 13:02
  • Currently the export to excel runs on a background worker thread. Could that be the issue? – ADY Apr 26 '21 at 13:07
  • The BackgroundWorker doesn't actually use a Thread, so making it STA is not really possible. As mentioned, start a Thread, I don't think you need the reporting capabilities of the BGW in a service. BUT, I suggest NOT to use Interop in any case. – Jimi Apr 26 '21 at 13:10
  • 1
    I agree, a non-interop solution is best. Having said that, try setting the excel app to visible. – dbasnett Apr 26 '21 at 13:49
  • So I tried the thread idea (STA) but got the same error. Then I tried it with Visible = true but again same error. I'm looking for non-interop solutions now although I'm not sure how successful I will be. The code I'm using takes data tables and creates worksheets from them and it works pretty well. – ADY Apr 26 '21 at 14:13
  • Your implementation of the *STA Thread Idea* is not visible, so it's impossible to say what goes wrong. A Service should never try to interact with the Desktop and break its Session 0 isolation. – Jimi Apr 26 '21 at 14:25
  • The problem I'm having is most non-Interop examples I've found are saving row by row. I have 300+ worksheets each with 10 - 20 columns and on average 5200 rows each. Using this example (https://stackoverflow.com/questions/18388592/fast-export-of-large-datatable-to-excel-spreadsheet-in-vb-net/56924365#56924365) it saves out the entire datatable as a range and runs extremely fast. I'll see what I can find though. – ADY Apr 26 '21 at 14:41
  • You have, e.g., [NPOI](https://github.com/nissl-lab/npoi), [ClosedXML](https://github.com/ClosedXML/ClosedXML) and others. All Open Source. – Jimi Apr 26 '21 at 15:08
  • I think I got ClosedXML working. The files it exports, when opened by Excel, say there are errors and gives the option to fix (which it does) but it at least is exporting while running as a service so thanks. – ADY Apr 26 '21 at 15:41

1 Answers1

0

I switched over to ClosedXML to generate the Excel file without using Office Interop and now everything works when running as a Service.

Jimi
  • 29,621
  • 8
  • 43
  • 61
ADY
  • 101
  • 1
  • 9