How to set the TimeOut of a SQL Server Stored Procedure (1 Viewer)

[accesser2003]

I have a stored procedure that takes time to execute.
It is supposed to work properly, but the problem is that it shows the message "Timeout expired" before showing making the results. How can I increase the total Timeout so it will not give me this message?

Thanks in advance.

 

[Banana]

I'm pretty sure the only way to modify timeout property is to use a separate workspace in DAO, or a separate connection in ADODB. You can't modify the default workspace's or connection's properties as they're always open and won't close.

 

[accesser2003]

My code is as follow, please tell me What I should modify or add

Dim db As Database
Dim SQLstr As String

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administrator;DATABASE=Bahrain;Trusted_Connection=Yes")

SQL = "PlugEmployeesAttendanceReportSourceRosterTmpLeaves"
db.Execute SQL, dbSQLPassThrough

 

[Banana]

I have to correct myself a bit.

There's a property for QueryDef called ODBCTimeout, which you can set at anytime.

You'd have to create a new passthrough query, put that SP in it, then set the ODBCTimeOut property to whatever, then do a .Execute method upon the querydef instead of db.

Dim qdf As DAO.QueryDef

Set qdf= CurrentDb.QueryDefs("NameOfQuery")

qdf.ODBCTimeout=XX

qdf.Execute

 

[datAdrenaline]

Expanding and adapting on Bannana's fine advice ... The will use a temporary query object .... its "AIR CODE" ...so no garentees!!! ...

Public Sub RunSP()
    With CurrentDb.CreateQueryDef()
        .Connect = "<Valid ODBC Connect String>"
        .SQL = "EXEC PlugEmployeesAttendanceReportSourceRosterTmpLeave s"
        .ODBCTimeout = 300 'Seconds
        .Execute
    End With
End Sub

 

[accesser2003]

Mr. dAtarenaline, thanks very much for your continuous help and participations.
When I run this code, it gives me the following error:

Run-time error '3420':

Object invalid or no longer set.

but I am sure that the object (SP) is there. I even tested another object (SP), it gives me the same error.
I dont know what is the problem.

Usually to run a stored procedure, am using the code:

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administrator;DATABASE=Bahrain;Trusted_Connection=Yes")

SQL = "PlugEmployeesAttendanceReportSourceRosterTmpLeaves"
db.Execute SQL, dbSQLPassThrough

How can I add the timeout to this code

 

[datAdrenaline]

Public Sub RunSP()
    Dim db As DAO.Database
    Set db = CurrentDB
    With Db.CreateQueryDef()
        .Connect = "<Valid ODBC Connect String>"
        .SQL = "EXEC PlugEmployeesAttendanceReportSourceRosterTmpLeave s"
        .ODBCTimeout = 300 'Seconds
        .Execute
    End With
End Sub

 

[accesser2003]

I modify my code as you advise, but the problem still.

 

[datAdrenaline]

Sorry ... I answered to quickly! .... I used this code successfully on a StoredProcedure of mine ...

Public Sub RunSP()
    With CurrentDb.CreateQueryDef("")
        .Connect = "<valid ODBC connect string>"
        .SQL = "EXEC StoredProcedure1"
        .ODBCTimeout = 300 'Seconds
        .ReturnsRecords = False
        .Execute
    End With
    
End Sub

The key is to have a name argument of a Zero Length String. Plus the .ReturnsRecords needs to be set as well, otherwise Access thinks its a Select query and tosses up a error for trying to execute a SELECT ...

 

[accesser2003]

Thanks very much it works.
I observed that the MS SQL Server UDFs take the same time taken by the VB functions.
Does the SQL SPs take less time than the UDFs or same if the code algorithm is same?

My SP which calls a UDF called FindLeaveReason for every record of the table.
Is the calling way to the UDF FindLeaveReason from the SP is correct?

ALTER PROCEDURE dbo.PlugEmployeesAttendanceReportSourceNormalTmpLeaves
AS UPDATE dbo.EmployeesAttendanceReportSource_NormalTmp
SET Leave = dbo.FindLeaveReason(Indx, AttDay, Department)