Post reply

sp_OAMethod: Refresh pivot sheet without open

  • March 26, 2009 at 1:03 pm

    #966978

    Hi

    Just wanted to say thanks ..your solution saved my life..I spent 2 days trying to sort it out before I found it 🙂

  • March 27, 2009 at 1:23 am

    #967235

    Hi susannah,

    I am very happy that the solution will be helpful.

    In addition, I recommend you to use the latest version of the stored procedure [dbo]. [USP_DMO_Refresh_Excel_Pivot_Table] that you can find (with example) at this link: http://www.ugiss.org/Content/Article/Aggiornare-una-tabella-pivot-di-Microsoft-Excel.aspx

    The latest version has the following features:

    - Possibility to refresh pivot sheets with Excel password

    - Possibility to refresh all worksheets (with pivot) on a single call of stored procedure

    - Improved the use of sp_OA* methods

    You can find a small article on my Italian blog 🙂 at: http://community.ugiss.org/blogs/sgovoni/archive/2009/01/09/aggiornare-una-tabella-pivot-di-microsoft-excel.aspx

    Bye

    Sergio

  • April 1, 2009 at 7:13 am

    #969798

    Hi Sergio the Life Saver

    Hope you can help me again 🙂

    I am using your sp , however, I only seem to be able to refresh around 2.5k records and I am working with 150k records.

    I am using it in an SSIS package which has a Foreach loop. I need to refresh 12 spreadsheets which have the same pivot but one for each of the Western Europe countries , so one for Italy, one for Spain etc. The data and pivot is the same format column names etc just different local data, names & addresses etc. My pivot is based on a SQL view. If I run my package with TOP 1 percent in my view the package run brilliantly. But if I make it Top 2 percent it hangs half way through the package, refreshes 7 spreadsheets and stops. If I do top 100 percent it does the first refresh but hangs on the 2nd spreadsheet. Strange hey!!! It’s like there is a limit or a memory restriction.

    Any suggestions?

    Thanks for your assistance 🙂

    Susannah

  • April 1, 2009 at 7:27 am

    #969810

    Hi Susannah,

    I check it... in the meantime might you post the call that you use for the stored procedure ?

    Thanks

    Sergio

  • April 1, 2009 at 9:29 am

    #969993

    Hi

    I get the same result in the SSIS package or just running it from query editor

    exec dbo.USP_DMO_Excel_Pivot_RefreshTable

    @FileName = 'C:\GRS\EPG_ContactProfiling\EPGContactDQAnalysis_V2.xlsx',

    @WorksheetIndex = 1,

    @PivotTableName = 'PivotTable1',

    @Password = '',

    @WriteResPassword = '',

    @Debug = 1

    This did work fine for 1 report with 40k rows, I change the view to the next country and it hung. It will work for all reports if I return small numbers but not all the rows that I want.

    I am installing service pack 3 at the moment to see if that make a difference. I saw there was a memory leakage problem at one time.

    thanks

    Susannah

  • April 1, 2009 at 3:14 pm

    #970258

    Hi Susannah,

    I am installing service pack 3 at the moment to see if that make a difference. I saw there was a memory leakage problem at one time

    for memory leaks, I found this article: http://support.microsoft.com/kb/937277/en-us , but I fear that the problem is not memory leaks.

    If spreadsheets are all on the same .xlsx file (all on C:\GRS\EPG_ContactProfiling\EPGContactDQAnalysis_V2.xlsx), I suggest you use this call:

    exec dbo.USP_DMO_Excel_Pivot_RefreshTable

    @FileName = 'C:\GRS\EPG_ContactProfiling\EPGContactDQAnalysis_V2.xlsx',

    @WorksheetIndex = NULL,

    @PivotTableName = '',

    @Password = '',

    @WriteResPassword = '',

    @Debug = 1

    In this way, the stored procedure tries to refresh all pivot tables contained in the 12 spreadsheets (one for each of the Western Europe countries, so one for Italy, one for Spain etc).

    Let me know if it works well.

    Bye

    Sergio

  • April 2, 2009 at 8:59 am

    #970795

    Hi

    I tried this but still no joy :crying:

    Thanks any way for your help

    Regards

    Susannah

  • April 2, 2009 at 4:00 pm

    #971162

    Hi Susannah,

    the problem may be related to the use of OLE Automation objects, for example OLE Automation objects compete for the same memory space with SQL Server.

    I'm rewriting 🙂 the stored procedure using CLR language instead of Component Object Model (COM) and OLE Automation. CLR should work better for access to external resources such as Microsoft Excel worksheet.

    If you want, when I have finished the development, I notice you about the new stored procedure.

    Bye

    Sergio

  • April 3, 2009 at 8:49 am

    #971633

    Hi

    I have change the source dataset to a temp table instead of a view and it is now refreshing 5 out of the 12 spreadsheets before hanging....so some progress. Yes please send me the new sp. This is an excellent sp and I would really like to use it.

    Thanks

    Susannah

  • April 8, 2009 at 8:46 am

    #974376

    Hi

    I have change the source dataset to a temp table instead of a view and it is now refreshing 5 out of the 12 spreadsheets before hanging....so some progress

    This result would appear to be a problem with performance in reading. If you work with SQL Server 2005 you could use indexed views to increase performance dramatically.

    However, I am completing the stored procedure in CLR language... I hope to be able to finish in a few days.

    Bye

    Sergio

  • April 15, 2009 at 3:45 pm

    #978465

    Hi Susannah,

    I have completed the stored procedures in CLR languages 🙂 take a look at this post: http://community.ugiss.org/blogs/sgovoni/archive/2009/04/11/sqlclr-instead-of-ole-automation-sp_5F00_oa_2A00_-method.aspx the article that you reach by clicking on read more explains the complete solution.

    I hope can be useful.

    Bye

    Sergio

  • April 15, 2009 at 4:07 pm

    #978479

    sgovoni (4/15/2009)

    Hi Susannah,

    I have completed the stored procedures in CLR languages 🙂 take a look at this post: http://community.ugiss.org/blogs/sgovoni/archive/2009/04/11/sqlclr-instead-of-ole-automation-sp_5F00_oa_2A00_-method.aspx the article that you reach by clicking on read more explains the complete solution.

    I hope can be useful.

    Bye

    Sergio

    Hi Sergio

    Great solution! I think Active-X should really become obsolete with Sql2k5 and CLR!

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • April 28, 2009 at 10:44 am

    #985311

    Hi Sergio

    Nearly there....I found this a bit difficult, I'm using Office 2007 & Visual Studio 2008 ...but just stuck on the last page now..

    I have a Sp called up_excel_pivot_refreshtable, however, I haven't created a CRL_Excel_Interf.dll. How do I do that?

    If I run the SP as in your Example I get an error message...

    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.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, Int32 Debug)

    Is this because I haven't created the .dll

    Really appreciate your help.

    Thanks

    Susannah

  • April 29, 2009 at 10:55 am

    #986133

    Hi Susannah,

    I haven't created a CRL_Excel_Interf.dll. How do I do that?

    The CRL_Excel_Interf.dll is created (in automatic mode, the output is) doing the build of the CLR project (CLR_Excel_Interf.csproj) on Microsoft Visual Studio (C #).

    If I run the SP as in your Example I get an error message...

    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.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, Int32 Debug)

    Is this because I haven't created the .dll

    Try to set the permission level for Unsafe in the database options for the project CLR_Excel_Interf.csproj (See attached image) ... rebuild the project CLR_Excel_Interf.csproj (automatic recreate the CRL_Excel_Interf.dll) and recreate assembly and stored procedure with:

    -- create assembly

    create assembly CLR_Excel_Interf

    from 'C:\Documents and Settings\Administrator\Documenti\Visual Studio 2005\Projects\SQLCLR\CLR_Excel_Interf\bin\Debug\CLR_Excel_Interf.dll'

    with permission_set = unsafe -- external_access

    go

    -- create procedure .NET

    create procedure dbo.up_excel_pivot_refreshtable

    (

    @FilePath [nvarchar](max),

    @Password [nvarchar](max),

    @WriteResPassword [nvarchar](max)

    )

    as

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

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

    go

    Near solution 🙂

    Bye

  • April 30, 2009 at 10:27 am

    #986762

    Hi Sergio

    Thanks trying it out

Viewing 15 posts - 16 through 30 (of 37 total)

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