Timeout

[byrne1]

I am running a SQL query from a workstation to an MTS/SQL machine and I'm getting timeout errors. My DLL and ADO connections are on the MTS/SQL machine and I've set my timeout property to zero but it still times out after about 30 seconds. When I run the query in Query Analyzer it takes 1 minute to complete.

I've listed my code and query below. I hope someone can offer some help. Thanks in advance!

QUERY:
select inout.account_number as inout_acct, loans.account_number as loans_acct,loans.account_status as loans_stat, inout.status as inout_stat, max(inout_date) from inout inner join loans on loans.account_number=inout.account_number where status='O' and datediff(day, inout_date, getdate())<=30
and doc_type='LOAN FILE' and not loans.account_status='P'
group by inout.account_number, inout.status, loans.account_number, loans.account_status

WORKSTATION CODE:
Dim LoanIO As Object
Dim rsAging As ADODB.Recordset
Set LoanIO = CreateObject(&quot;LoanIODLL.Class1&quot;, &quot;devsql01&quot
Set rsAging = LoanIO.Get_Aging

DLL CODE:
Dim cnLoanIO As ADODB.Connection

Private Sub Class_Initialize()
Set cnLoanIO = New ADODB.Connection

'USE FOR SQL
cnLoanIO.CursorLocation = adUseClient
cnLoanIO.ConnectionTimeout = 0
cnLoanIO.ConnectionString = &quot;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mytable;Data Source=myserver&quot;
cnLoanIO.Open
End Sub

Public Function Get_Aging() as Recordset
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cnLoanIO
cmd.CommandText = &quot;sp_GetAgingData&quot;
Set Get_Aging = cmd.Execute
End Function

 

[stoolpigeon]

I'm not familiar w/setting up a connection this way-- but a couple thoughts that might help you along.

Setting the timeout to 0 should work- so it may have something to do w/where you set that property. Could it make a difference if you set that on the object you create on the workstation side?

One thing hindering the performance of your query is using &quot;not&quot;. It is probably much easier than doing it the other way around and listing all the status codes you do want-- but it really hurts your query.

 

[VogonPoet]

You might try also setting the timeout for the command object.

 

[byrne1]

Thanks for your help. I've set the ConnectionTimeout property of my Cmd object to zero. This is in the DLL that's on my MTS server but it did not fix the timeout issue. I do not know of a way to set a timeout property on the Object that I'm using to connect to my DLL (see code below)

WORKSTATION CODE:
Dim LoanIO As Object
Dim rsAging As ADODB.Recordset
Set LoanIO = CreateObject(&quot;LoanIODLL.Class1&quot;, &quot;devsql01&quot
Set rsAging = LoanIO.Get_Aging <--timeout occurs here

 

[VogonPoet]

Can you provide more information on &quot;LoanIODLL&quot;?

 

[byrne1]

Sure. Essentially the DLL sets up an ADO connection to the SQL Server and then executes a stored procedure via an ADO command object. The connection timeout property for the connection is set to zero. The connection is established in the Class_Initialize sub and the stored procedure is called in the Get_Aging function.

The code is as follows:

DLL CODE:
Dim cnLoanIO As ADODB.Connection
Private Sub Class_Initialize()
Set cnLoanIO = New ADODB.Connection
cnLoanIO.CursorLocation = adUseClient
cnLoanIO.ConnectionTimeout = 0
cnLoanIO.ConnectionString = &quot;Provider=SQEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mytable;Data Source=myserver&quot;
cnLoanIO.Open
End Sub

Public Function Get_Aging() as Recordset
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cnLoanIO
cmd.CommandText = &quot;sp_GetAgingData&quot;
Set Get_Aging = cmd.Execute
End Function

 

[VogonPoet]

If you set the command object's timeout to 0 in the DLL, then I don't have a clue why it isn't working - it looks like it should.

Sorry I wasn't able to help.

 

[stoolpigeon]

This may make absolutely know difference- but when I have set a timeout to zero on a connection I have done it after the connection was open.

You may want to give that a try in your dll. Move the code to change that property to the line below your call to the open method.

After that you've got me.