locked
Error when running EXE from SQL server RRS feed

  • 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.

     

    Monday, January 5, 2009 2:48 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 9, 2009 1:14 AM

All replies

  • User-2110079211 posted

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

    Monday, January 5, 2009 3:02 PM
  • 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();
            }            
        }

    Monday, January 5, 2009 4:17 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 9, 2009 1:14 AM