Solved

SQLCLR stored procedure do not close Excel connection

Posted on 2009-03-31
10
384 Views
Last Modified: 2013-11-10
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
Comment
Question by:sgovoni
  • 5
  • 5
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24030595
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
 

Author Comment

by:sgovoni
ID: 24048204
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 24048621
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24048632
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
 

Author Comment

by:sgovoni
ID: 24049152
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24049716
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
 

Author Comment

by:sgovoni
ID: 24050090
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24050181
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
 

Author Closing Comment

by:sgovoni
ID: 31564910
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
 

Author Comment

by:sgovoni
ID: 24126888
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question