I have a site I created some time ago in DW connecting to a
SQL database using an ODBC connection. Some of the queries are now
taking so long that I have started getting these timeout errors;
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Microsoft][ODBC SQL Server Driver]Timeout expired
The underlying problem is that the queries on the SQL db are
now taking very much longer than they used to! I have got them
'tuned' as much as I can but the timeouts still occur - there is
just so much data in the tables!
Looking around various ASP/ADO tutorials would seem to
indicate I should be adjusting the 'connection.commandtimeout'
value but DW (I'm using MX v6) isn't being very obvious with where
I put in this value!
I have a connection file, connPathData.asp, that looks like
this;
<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_connPathData_STRING
MM_connPathData_STRING = "dsn=pathnet_data;"
%>
... although I could have used;
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_connPathData_STRING
MM_connPathData_STRING = "PROVIDER=SQLOLEDB;DATA
SOURCE=PATHOLOGY1;UID=user;PWD=pass;DATABASE=pathnet_data"
%>
Then the actual page that uses the data looks like this;
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/connPathData.asp" -->
<%
set rsDept_Totals = Server.CreateObject("ADODB.Recordset")
rsDept_Totals.ActiveConnection = MM_connPathData_STRING
rsDept_Totals.Source = "SELECT DEPT, Year, Month etc..."
rsDept_Totals.CursorType = 0
rsDept_Totals.CursorLocation = 2
rsDept_Totals.LockType = 1
rsDept_Totals.Open()
rsDept_Totals_numRows = 0
%>
My problem is that DW doesn't seem to be *explicitly* using
the connection object, so I can't see where to include the
commandtimeout value.
Apologies in advance for X-posting this to the 'general'
forum initially, upon reflection I thought folks round here would
be more versed in this kind of thing (-;
Simon Harris