Re: Pivot Table Timeout

Thanks for posting this workaround.

--
Regards,
Alvin Bruney

Auther Plug
OWC Blackbook now on download at www.lulu.com/owc

"Dan Hochman" <danhvos(at)comcast.net> wrote in message news:eNJjgjzqJHA.4364@xxxxxxxxxxxxxxxxxxxxxxx
After struggling with this for some time, I finally came up with a solution to get around the PivotTable control's timeout. In order to help others. I am posting my code below.

The solution involves using VBScript under ASP.

First, change the Pivot Table control to read from a local Access database table stored on the local computer. This keeps the refresh time well below the 30 second threshold.

When a refresh is required:
the local table is cleared of all records
a connection is opened to the SQL Server and the query is sent.
If the query takes a long time and times-out, it can be adjusted with properties in the SQL connection string.
Each record is looped through and written to the local table.

Once the above has occured, the Pivot Table control is refreshed and it quickly reads the data from the local table.


****Code starts below and can be inserted into any ASP procedure called on the page:****

'Open connection to local database
Set LocalConn = PivotTable1.Connection 'Very important that the local table use the same connection as the pivot table control...otherwise query is sent twice!

'Open Connection to remote SQL Server
set RemoteConn=createobject("ADODB.Connection")
RemoteConn.Open "Provider=SQLOLEDB.1;Password=<Password>;Persist Security Info=True;User ID=<User ID>;Initial Catalog=<Database>;Data Source=evergreen.bayhillcap.net\fir;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False" 'USE YOU OWN CONNECTION STRING IF YOU ALREADY HAVE ONE!


'Open recordset on SQL Server
sql = "select * from <Table>
Set RemoteRs = CreateObject("ADODB.Recordset")
RemoteRs.ActiveConnection = RemoteConn
RemoteRs.Source = sql
RemoteRs.CursorType = 0 'Forward only
RemoteRs.LockType=1 'Read only
RemoteRs.Open

'Delete all records from the local table
LocalConn.execute "DELETE * FROM <Local Table>"

'Open Local Recordset
sql = "<Local Table>"
Set LocalRs = CreateObject("ADODB.Recordset")
LocalRs.ActiveConnection = LocalConn
LocalRs.Source = sql
LocalRs.CursorType = 2 'Dynamic
LocalRs.LockType= 3 'Optimistic
LocalRs.Open

'Copy records to the local table
do while not RemoteRs.EOF
LocalRs.AddNew 'Add New local record


LocalRs("<Field Name>") = RemoteRs("<Field Name>")
Note: Repeat the above line for each fiels in the table



localRs.Update 'Save the new local record
RemoteRs.MoveNext
loop

'Close Resources
LocalRs.Close
RemoteRs.close
set LocalConn=Nothing
RemoteConn.close

PivotTable1.Refresh

****Code End****

"Dan Hochman" <danhvos(at)comcast.net> wrote in message news:OaDdkDboJHA.3840@xxxxxxxxxxxxxxxxxxxxxxx
We have a pivottable control embedded in an html file. The pivottable control requests data from a SQL Server.

It works fine unless the query that is selected takes longer than 30 seconds to complete. At that point, a message box appears stating, "Timeout expired".

I have tried eveything that I can think of to work around this limitation. Is there a known workaround or patch for this?

Thanks!

Replies accepted here or:
danhvos (at) comcast.net

.

Relevant Pages

  • Re: Query from ms sql server question
    ... For example, in my worksheet, there a cell which user will enter the production number and macro will perform an automatic connection to sql server and query the table base on the production number and retrieve the data base on the production alone and place it on the excel sheet. ...
    (microsoft.public.excel.programming)
  • Re: ANSI_WARNINGS error in Stored Procedure
    ... The error says to set these options for your _connection_ not for the query. ... > I have a linked Server on my regular SQL server, and when I try to create ... a stored procedure that reads from the linked server, ...
    (microsoft.public.sqlserver.security)
  • Re: Pivot Table Timeout
    ... If the query takes a long time and times-out, it can be adjusted with properties in the SQL connection string. ... 'Open Connection to remote SQL Server ...
    (microsoft.public.office.developer.web.components)
  • Re: Problem with blank entries being filled with other values
    ... We have a table in SQL Server ... We run a Make Table query within Access, which produces a table in Access ... The connection via DDE worked. ... >> in the Counter field are replaced with values from this field in other ...
    (microsoft.public.word.mailmerge.fields)
  • Re: SQLConnection Dillema
    ... > an open connection on a sql server has both for client and server or even ... > it is a multi-user scenario, this counter has to periodically be updated ... > open a connection, fetch the count, and then close it again or I just keep ...
    (microsoft.public.dotnet.framework.adonet)