asked on

SQLCLR stored procedure do not close Excel connection

I developed a SQLCLR stored procedure to refresh the pivot tables contained in a spreadsheet microsoft excel.
For every execution of the stored procedure, however, remains open on a task to excel. The only way to close is to act on the task manager. Have you any idea ?

Follows the sample code.

Thanks

            Excel.Application thisExcel = new Excel.Application();
            try
            {
                Excel.Workbook theWorkbook = thisExcel.Workbooks._Open
                  (FilePath,
                   0,
                   false,
                   5,
                   Password,
                   WriteResPassword,
                   false,
                   System.Reflection.Missing.Value,
                   System.Reflection.Missing.Value,
                   true,
                   false,
                   System.Reflection.Missing.Value,
                   false);
 
                theWorkbook.RefreshAll();
 
                theWorkbook.Save();
            }
            finally
            {
                thisExcel.Quit();
            }

Open in new window

Rory Archibald

You could replace the Save with this and see if it helps:

theWorkbook.Close(True, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

Open in new window

sgovoni

ASKER

Hi rorya,

and thanks for your comment... now i receive this error message:

Messaggio 6522, livello 16, stato 1, procedura up_excel_pivot_refreshtable, riga 0
Errore di .NET Framework durante l'esecuzione dell'aggregazione o routine definita dall'utente "up_excel_pivot_refreshtable": 
System.Runtime.InteropServices.COMException: Server RPC non disponibile. (Eccezione da HRESULT: 0x800706BA)
System.Runtime.InteropServices.COMException: 
   at Microsoft.Office.Interop.Excel.ApplicationClass.Quit()
   at StoredProcedures.up_excel_pivot_refreshtable(String FilePath, String Password, String WriteResPassword)

Open in new window

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

If that doesn't work, can you give more detail about the pivot tables? Are they based on external data? If so, are they set to BackgroundQuery?

sgovoni

ASKER

Hi rorya,

the pivot table is based on external data, they are not set option BackGroundQuery. I attach the .PNG file with pivot table options.

Thanks again.
PivotTableOptions.PNG

Rory Archibald

With your original code, if you set thisExcel.Visible = True near the start, can you see any messages in Excel when the processing has finished?

sgovoni

ASKER

The process does not start. I enabled debugging SQLCLR from Visual Studio, from debug I get this error:

System.Security.SecurityException: That assembly does not allow partially trusted callers.
System.Security.SecurityException:
at StoredProcedures.up_excel_pivot_refreshtable(String FilePath, String Password, String WriteResPassword)

Rory Archibald

That sounds like this issue: http://msdn.microsoft.com/en-us/library/ms345097.aspx
but I don't know enough about CLR and SQL to assist, I'm afraid. (I'm an Excel nerd).
Regards,
Rory

sgovoni

ASKER

Hi Rory,

I resolved the issue by changing the permission level the project to SQLCLR stored procedure.

However, even using the close method (theWorkbook.Close(...)) is always one (and only one) session of Excel works even when the stored procedure is finished.

Without the theWorkbook.Close(...) I had a task of Excel for each execution of the stored procedure, now I always just one (but is not closed at the end of operation).

Thanks

Sergio

sgovoni

ASKER

Look the complete solution to starting from this link (italian language): http://community.ugiss.org/blogs/sgovoni/archive/2009/04/11/sqlclr-instead-of-ole-automation-sp_5F00_oa_2A00_-method.aspx