Time out running stored procedure from visual basic

[Beau Lendman]

Would appreciate guidance on how to stop timeouts from
occurring on certain stored procedures when they are
executed from visual basic. These stored procedures run
fine when they are invoked from the database window but
time out when run through visual basic. I've tried using
the following code in visual basic but it doesn't seem to
make a difference:

Dim ws As Workspace
Dim db As Database
Dim con As String
Dim sql As String
con = "ODBC;DSN=xxx;USI=xxx; PWD=xxx;Database=xxx"
Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set db = ws.OpenDatabase("", Connect:=con)
db.QueryTimeout = 300

Any ideas would be most appreciated!!

Thanks,

Beau Lendman
(e-mail address removed)

 

[prabha]

Hi Beau,

You are most likely seeing this behavior
170548 PRB: DAO Transactions to ODBC Database Can Hang Application
http://support.microsoft.com/?id=170548

Why not use a ADO Command Object?

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."


--------------------
| Content-Class: urn:content-classes:message
| From: "Beau Lendman" <[email protected]>
| Sender: "Beau Lendman" <[email protected]>
| Subject: Time out running stored procedure from visual basic
| Date: Tue, 10 Feb 2004 13:16:05 -0800
| Lines: 25
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcPwGxfNI92RzBNPT6CSCRC2gQFY8w==
| Newsgroups: microsoft.public.access.adp.sqlserver
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.adp.sqlserver:16370
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| Would appreciate guidance on how to stop timeouts from
| occurring on certain stored procedures when they are
| executed from visual basic. These stored procedures run
| fine when they are invoked from the database window but
| time out when run through visual basic. I've tried using
| the following code in visual basic but it doesn't seem to
| make a difference:
|
| Dim ws As Workspace
| Dim db As Database
| Dim con As String
| Dim sql As String
| con = "ODBC;DSN=xxx;USI=xxx; PWD=xxx;Database=xxx"
| Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
| Set db = ws.OpenDatabase("", Connect:=con)
| db.QueryTimeout = 300
|
| Any ideas would be most appreciated!!
|
| Thanks,
|
| Beau Lendman
| (e-mail address removed)
|
|
|

 

[Guest]

Hi Eric,

Thanks for your response. The article seems relevant and
I'll try to research adjustments we can make in SQL that
could resolve this. But with respect to your comment that
we use the ADO Command object, I think we are doing this.
When we invoke the stored procedure from within visual
basic, we use the following code:

Set cmdl = New ADODB.Command
cmdl.ActiveConnection = CurrentProject.Connection
cmdl.CommandType = adCmdStoredProc
cmdl.CommandText = "spDFReceiving"
cmdl.Execute

If there some way that we can modify these commands that
could make a difference?

Thanks very much for your assistance!

Beau

-----Original Message-----
Hi Beau,

You are most likely seeing this behavior
170548 PRB: DAO Transactions to ODBC Database Can Hang Application
http://support.microsoft.com/?id=170548

Why not use a ADO Command Object?

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03 -026.asp> and/or
to visit Windows Update at

the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

 

[Barry Ray]

Hi Beau,

you can adjust the timeout value for the sproc by setting the
'CommandTimeout' property of the Command object.

In your code I have inserted the relevant line just before the Execute
method as follows...

Set cmdl = New ADODB.Command
cmdl.ActiveConnection = CurrentProject.Connection
cmdl.CommandType = adCmdStoredProc
cmdl.CommandText = "spDFReceiving"
cmdl.CommandTimeout = 300
cmdl.Execute

Note that setting the timeout value to 0 means the sproc will execute until
it completes, or forever!!
I believe that by default the timeout value is what is set up for 'OLE/DDE
timeout' in the Tools/Options/Advanced tab of the Access application you are
running. So you could adjust that to suit, but of course it will affect all
queries run via the application. It's best to set the application timeout
value to something reasonable (say 60 sec) and dynamically adjust the
timeout
as required for individual sprocs as illiustrated above

Hope that helps

Barry

 

[prabha]

Hi Beau,

See Barry's post:

cmdl.CommandTimeout = 300

Eric


--------------------
| Content-Class: urn:content-classes:message
| From: <[email protected]>
| Sender: <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: RE: Time out running stored procedure from visual basic
| Date: Wed, 11 Feb 2004 03:02:02 -0800
| Lines: 116
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcPwjnqQUPeMbiawRuOeSJqZyAY+rA==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.access.adp.sqlserver
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.adp.sqlserver:16377
| NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| Hi Eric,
|
| Thanks for your response. The article seems relevant and
| I'll try to research adjustments we can make in SQL that
| could resolve this. But with respect to your comment that
| we use the ADO Command object, I think we are doing this.
| When we invoke the stored procedure from within visual
| basic, we use the following code:
|
| Set cmdl = New ADODB.Command
| cmdl.ActiveConnection = CurrentProject.Connection
| cmdl.CommandType = adCmdStoredProc
| cmdl.CommandText = "spDFReceiving"
| cmdl.Execute
|
| If there some way that we can modify these commands that
| could make a difference?
|
| Thanks very much for your assistance!
|
| Beau
|
| >-----Original Message-----
| >Hi Beau,
| >
| >You are most likely seeing this behavior
| >170548 PRB: DAO Transactions to ODBC Database Can Hang
| Application
| >http://support.microsoft.com/?id=170548
| >
| >Why not use a ADO Command Object?
| >
| >I hope this helps! If you have additional questions on
| this topic, please
| >respond back to this posting.
| >
| >
| >Regards,
| >
| >Eric Butts
| >Microsoft Access Support
| >
| >"Microsoft Security Announcement: Have you installed the
| patch for
| >Microsoft Security Bulletin MS03-026? If not Microsoft
| strongly advises
| >you to review the information at the following link
| regarding Microsoft
| >Security Bulletin MS03-026
| ><http://www.microsoft.com/security/security_bulletins/ms03
| -026.asp> and/or
| >to visit Windows Update at
| <http://windowsupdate.microsoft.com/> to install
| >the patch. Running the SCAN program from the Windows
| Update site will help
| >to insure you are current with all security patches, not
| just MS03-026."
| >
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Beau Lendman"
| <[email protected]>
| >| Sender: "Beau Lendman"
| <[email protected]>
| >| Subject: Time out running stored procedure from visual
| basic
| >| Date: Tue, 10 Feb 2004 13:16:05 -0800
| >| Lines: 25
| >| Message-ID: <[email protected]>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Thread-Index: AcPwGxfNI92RzBNPT6CSCRC2gQFY8w==
| >| Newsgroups: microsoft.public.access.adp.sqlserver
| >| Path: cpmsftngxa07.phx.gbl
| >| Xref: cpmsftngxa07.phx.gbl
| microsoft.public.access.adp.sqlserver:16370
| >| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| >| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
| >|
| >| Would appreciate guidance on how to stop timeouts from
| >| occurring on certain stored procedures when they are
| >| executed from visual basic. These stored procedures
| run
| >| fine when they are invoked from the database window but
| >| time out when run through visual basic. I've tried
| using
| >| the following code in visual basic but it doesn't seem
| to
| >| make a difference:
| >|
| >| Dim ws As Workspace
| >| Dim db As Database
| >| Dim con As String
| >| Dim sql As String
| >| con = "ODBC;DSN=xxx;USI=xxx; PWD=xxx;Database=xxx"
| >| Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
| >| Set db = ws.OpenDatabase("", Connect:=con)
| >| db.QueryTimeout = 300
| >|
| >| Any ideas would be most appreciated!!
| >|
| >| Thanks,
| >|
| >| Beau Lendman
| >| (e-mail address removed)
| >|
| >|
| >|
| >
| >.
| >
|