sp_OAMethod: Refresh pivot sheet without open

Question

Post reply

sp_OAMethod: Refresh pivot sheet without open

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

sgovoni Ten Centuries Points: 1156 More actions · Answer 1

Hi Susannah,

Bet you wished you never started this Sorry to bother you again

No problem 🙂

In fact, the CLR stored procedure accepts one parameter more, the fourth (@Debug)... this is my forgetfulness :blush:.

Try this:

-- create procedure .NET

create procedure dbo.up_excel_pivot_refreshtable

(

@FilePath [nvarchar](max),

@Password [nvarchar](max),

@WriteResPassword [nvarchar](max),

@Debug [int]

)

as

-- EXTERNAL NAME [nome_assembly].[nome_classe].[nome_metodo]

external name [CLR_Excel_Interf].[StoredProcedures].[up_excel_pivot_refreshtable]

go

Bye

susannah_britcher SSC Enthusiast Points: 119 More actions · Answer 2

Hi Sergio

Next Problem

I ran the sp and got an error message:

(1 row(s) affected)

Msg 6522, Level 16, State 1, Procedure up_excel_pivot_refreshtable, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "up_excel_pivot_refreshtable":

System.Runtime.InteropServices.COMException: Microsoft Office Excel cannot access the file 'C:\WEDMO\GRS\PriortyContacts\GRS_Contacts_Temp_EC.xlsx'. There are several possible reasons:

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

System.Runtime.InteropServices.COMException:

at Microsoft.Office.Interop.Excel.Workbooks._Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru)

at StoredProcedures.up_excel_pivot_refreshtable(String FilePath, String Password, String WriteResPassword, Int32 Debug)

.

I then installed this on another machine and this time got:

Msg 6522, Level 16, State 1, Procedure up_excel_pivot_refreshtable, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "up_excel_pivot_refreshtable":

System.Runtime.InteropServices.COMException: The RPC server is unavailable. (Exception from 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, Int32 Debug)

I did get a warning when I created the assemblies because of different versions, but they did get created.

Any ideas?

Thanks again

Regards

Susannah

sgovoni Ten Centuries Points: 1156 More actions · Answer 3

Hi Susannah,

seems a problem of method Quit ()... I was not able to reproduce the problem... I check...

sgovoni Ten Centuries Points: 1156 More actions · Answer 4

Hi Susannah,

what message you get by running the stored procedure in debug mode, with the following example command (with @Debug = 1) ?

-- exec StoredProcedureName

exec dbo.up_excel_pivot_refreshtable

@FilePath = 'C:\temp\refresh.xls',

@Password = '',

@WriteResPassword = '',

@Debug = 1

Try to see how many records you are trying to refresh in excel sheet. Excel cannot handle more than about 65,000 records in one worksheet.

Bye

susannah_britcher SSC Enthusiast Points: 119 More actions · Answer 5

Hi Sergio

When I run the sp it never finishes, so I have been terminating it with task manager and get the error :-

Msg 6522, Level 16, State 1, Procedure up_excel_pivot_refreshtable, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "up_excel_pivot_refreshtable":

System.Runtime.InteropServices.COMException: The RPC server is unavailable. (Exception from 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, Int32 Debug)

.

If I refresh the pivots manually, close the file and then run the sp it works, or at least come back with the message 'Execution Completed Successfully'. If I then change the view to the next set of data to be refreshed it doesn't finish. I was only refreshing about 100 records and I am using Excel 2007 so number of rows should be too much of a problem.

I know it must be something not set up quite right on my side. When I created the assemblies I did get warning about SQL using 2.xx for .NET and the PIA using 1.xx, I did use the Office 2007 PIA's.

Also I notice when I open an excel file manually it is using EXCEL.EXE *32 , when I run the sp, EXCEL.EXE is active, don't know if that is relevant.

I am using Windows 2003, SQL Server 2005 & Office 2007.

Thanks again for your help.

Regards

Susannah

sgovoni Ten Centuries Points: 1156 More actions · Answer 6

Hi Susannah

I'm trying to reproduce the error:

Msg 6522, Level 16, State 1, Procedure up_excel_pivot_refreshtable, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "up_excel_pivot_refreshtable":
System.Runtime.InteropServices.COMException: The RPC server is unavailable. (Exception from 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, Int32 Debug)

I am able to reproduce it using a pivot table (based on a view) in which I have not specified the option "save password" in the external data options (see image in attach). In this case (no specified "save password") the stored procedure is still waiting for a password ... without terminating the execution.

Have a look in the options "save password" as shown in figure.

I know it must be something not set up quite right on my side. When I created the assemblies I did get warning about SQL using 2.xx for .NET and the PIA using 1.xx, I did use the Office 2007 PIA's.

I received the same warning too when I create the assembly, i check it...

Bye

sgovoni Ten Centuries Points: 1156 More actions · Answer 7

Hi Susannah

do you remember the first solution based on the use of OLE Automation Stored Procedures ? Now is available an update of the stored procedure dbo.USP_DMO_Excel_Pivot_RefreshTable: I fixed a bug 🙂 and I have implemented the ability to update the QueryTables in a worksheets.

Here is the link where you can get the stored procedure: http://community.ugiss.org/blogs/sgovoni/archive/2009/05/17/aggiornare-una-tabella-pivot-di-microsoft-excel-2.aspx

As for the solution based on using SQLCLR (the best solution for SQL Server 2005) I'm checking your error reports ... in the meantime could you check the option "save password" in the external data options ?

Bye

Sergio

sgovoni Ten Centuries Points: 1156 More actions · Answer 8

Hi,

I need to update a pivot table that resides on a Microsoft Excel worksheet.

I created a stored procedure dbo.USP_DMO_EXCEL_Pivot_RefreshTable(http://www.ugiss.org/Content/Article/Aggiornare-una-tabella-pivot-di-Microsoft-Excel.aspx) that uses OLE Automation (sp_OA*) to refresh, every night (without open the Excel file), the pivot table data in Microsoft Excel worksheet.

The stored procedure works well in this enviroment:

- OS Windows Server 2003

- Office 2003

- SQL Server 2008

But it does not work with:

- OS Windows Server 2008 64-bit or Windows 7 64-bit

- Office 2007

- SQL Server 2008 64-bit

The pivot table connecting to SQL Server with an ODBC connection (32 bit).

The Excel file will open correctly, bit I get an error during execution method "RefreshTable" in the following line of code:

Exec sp_OAMethod @objWorkSheet PivotTables('Pivot_Name').RefreshTable

The execution of stored procedures will not be completed, the only way (to stop it) is to interrupt forcefully the task "EXEC.EXE *32".

When I forced to close the task, in SSMS I get the following error:

-2146827284 Unable to find Microsoft Office Excel PivotTables property for the class Worksheet. C:\Program Files (x86)\Microsoft Office\Office12\1040\0 XLMAIN11.CHM

Message 50000, Level 16, State 1, Server <name> USP_DMO_Excel_Pivot_RefreshTable procedure, line 369

Whilst Error: Return object workbooks, Could not find property PivotTables for Class Worksheet

I also installed the 2007 Office System Driver: Data Connectivity Components (http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en) but without improvement.

Any suggestions?

Thanks a lot!