Error when running EXE from SQL server

Question

User-1949910660 posted

I have an .EXE created in C# … this program will combine all .csv files into 1 Excel file. I can run it on CMD (Start -> Run) and it will generate the excel file successfully. However, when I run it from SQL server … it gives me below Error:

System.Runtime.InteropServices.COMException (0x80080005): Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005.
 
Again, if I run it from Start->Run .. it works. I wonder how to fix it. I also noticed if I rebooted the server, then it works. But I can’t reboot it everytime I need to run this EXE.

 

Answer 1

User-2110079211 posted

 check are you disposing all the com objects in your code ?? use obj.Dispose method

Answer 2

User-1949910660 posted

dispose the COM object BEFORE or AFTER the excel file being generated? Below is the code:

 static void CreateExcel (string fileLocation, string folderName)
    {
        string[] FileName = { "A.CSV" ,
                              "B.CSV",
                              "C.CSV
                            };

      
        string[] PageTitle = {"",  // Blank item because Excel will create a blank worksheet first before copy
                              "Data A",
                              " Data B ",
                              " Data C"
                             };

        try
        {
            Microsoft.Office.Interop.Excel.Application oXL;
            Microsoft.Office.Interop.Excel._Workbook oWB;

            oXL = new Microsoft.Office.Interop.Excel.Application();

            // Workbook to include ALL CSV Reports
            oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(System.Type.Missing));

            int j = 0;

            // Read each Workbook from CSV Reports
            foreach (string file in FileName)
            {

                //////////////Copy each CSV file to each Worksheet///////////////

                Microsoft.Office.Interop.Excel._Workbook oWBCSV;
                oWBCSV = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Open(fileLocation + "\\" + folderName + "\\" + file, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing));
                oWBCSV = oXL.ActiveWorkbook;

                Microsoft.Office.Interop.Excel._Worksheet csvWS = ((Microsoft.Office.Interop.Excel._Worksheet)oWBCSV.Worksheets[1]);
                Microsoft.Office.Interop.Excel._Worksheet mergeWS = ((Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets[j + 1]);

                csvWS.Copy(System.Type.Missing, mergeWS);

                Microsoft.Office.Interop.Excel._Worksheet oWS;
                oWS = (Microsoft.Office.Interop.Excel._Worksheet)oWBCSV.ActiveSheet;

                oWS = mergeWS;

                // Print Title in 1st Line

                oWS.get_Range("A1", "Z1").Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
               
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oWS);
                 
                oWS = null;

                mergeWS = null;

                oWBCSV.Close(null, null, null);
                oWBCSV = null;
               
                j = j + 1;

            }

            string strFile = "Names_" + folderName.Substring(3).ToString() + ".xls";

            string outputLocation = fileLocation + folderName + "\\" + strFile;

            oXL.DisplayAlerts = false;

            oWB.SaveAs(outputLocation, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);

            // Need all following code to clean up and extingush all references!!!
            oWB.Close(null, null, null);

            oXL.Workbooks.Close();
            oXL.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
 
            oWB = null;
            oXL = null;
        }

        catch (Exception ex)
        {
            WriteToLog LogFile = new WriteToLog(fileLocation + "File_" + folderName.Substring(3).ToString() + ".log");

            LogFile.EventMessage = " Error : " + ex.ToString();
            LogFile.WriteEventLog();
        }            
    }

Answer 3

User-1136466523 posted

Hi,

As you have disposed the objects you used, you need to care about the enviroment for using automation of Office.

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.

For more information see:

http://support.microsoft.com/default.aspx?scid=kb;en-us;257757

Thanks.