Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQLCLR stored procedure do not close Excel connection

Posted on 2009-03-31
10
Medium Priority
?
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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 Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

609 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