ODCB error 7312....[OLE/DB provider returned message: Timeout expired]

[RichardHayes]

When calling a stored procedure that takes some time to run from within an Access module, I get the following error (by cycling through the DBEngine.Errors collection):

"7312 (01000: [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Timeout expired])"

In order to avoid timeouts, I include the following line when creating the connection to the SQL Server via a DSN:

gdb.QueryTimeout = 0

Where is the timeout occurring? Any ideas how I can avoid this?

 

[jjlogan]

The problem may be in stored procedure, not your connection and local mdb environment. Have you ensured procedure is solid?
Jeff

 

[RichardHayes]

I'm fairly confident with the sp yes.

The problem may lie in the fact that it involves linked servers, and command shells on the linked server.

I think I may split the proc into smaller chunks to ascertain if a certain part is hanging.

 

[RichardHayes]

It was indeed the linked server causing the problem. When I ran the local stored proc (that call's the remote stored proc), it timed out, returning the following error.

OLE DB provider 'STREAM' reported an error.
[OLE/DB provider returned message: Timeout expired]

I have fixed it by changing the procedure to run locally, but I would be interested if you knew how this timeout occurred (after about 14 minutes). I looked at the settings for the linked server, and both the Connection Timeout and Query Timeout are set to zero.