Solved

SQLCLR stored procedure do not close Excel connection

Posted on 2009-03-31
10
379 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now