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****
