• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

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

0
sgovoni
Asked:
sgovoni
  • 5
  • 5
1 Solution
 
Rory ArchibaldCommented:
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

0
 
sgovoniAuthor Commented:
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

0
 
Rory ArchibaldCommented:
How about:
 

            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();
                theWorkbook.Close(False, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            }
            finally
            {
                thisExcel.Quit();
            }

Open in new window

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Rory ArchibaldCommented:
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?
0
 
sgovoniAuthor Commented:
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
0
 
Rory ArchibaldCommented:
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?
0
 
sgovoniAuthor Commented:
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)
0
 
Rory ArchibaldCommented:
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
0
 
sgovoniAuthor Commented:
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
0
 
sgovoniAuthor Commented:
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now