Stored Procedure Quits Early

[jpuls]

I'm having trouble getting a stored procedure to run from an Access
..adp project form.

The stored procedure runs fine from SQL Query Analyzer. It has no
inputs and no outputs, and takes about 3 minutes to run on a local copy
of SQL Server Developer Edition.

There are a series of User Configurable events that I can see happening
in SQL Profiler. When I try to run the stored procedure from an Access
form, it runs for a minute or so, then quits (I can see this in Task
Manager) with no error indication.

The test form is unbound, and has a single button with minimal On Click
event code:

Private Sub cmdTest_Click()
MsgBox ("Before Procedure")
CurrentProject.Connection.CommandTimeout = 60 * 10
CurrentProject.Connection.Execute "dbo.pReassignCodes"
MsgBox ("After Procedure")
End Sub

When the stored procedure quits early, the "After Procedure" message
box comes on.

I have db_owner permissions on the database in question.

Any suggestions would be gratefully appreciated!

Thanks,

Jim

Jim Puls
Sentinel Technologies

 

[Philipp Stiefel]

I'm having trouble getting a stored procedure to run from an Access
.adp project form.

The stored procedure runs fine from SQL Query Analyzer. It has no
inputs and no outputs, and takes about 3 minutes to run on a local copy
of SQL Server Developer Edition.

There are a series of User Configurable events that I can see happening
in SQL Profiler. When I try to run the stored procedure from an Access
form, it runs for a minute or so, then quits (I can see this in Task
Manager) with no error indication.

The test form is unbound, and has a single button with minimal On Click
event code:

Private Sub cmdTest_Click()
MsgBox ("Before Procedure")
CurrentProject.Connection.CommandTimeout = 60 * 10
CurrentProject.Connection.Execute "dbo.pReassignCodes"
MsgBox ("After Procedure")
End Sub

The CommandTimeout-Property of the connection is _not_
inherited by Command-Object. To deal with that, try to
use an ADODB.Command-Object to run you SP.

Dim cmd as ADODB.Command
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.pReassignCodes"
.CommandType = adCmdStoredProc
.CommandTimeout = 60 * 10
.Execute
End with


HTH
Phil

 

[jpuls]

Phil,

You are quite right! The ADODB command object works fine.

Thanks!

Jim