SQL stored procedure running slow in .adp file 1

[greggranger]

I have a stored procedure that I created in SQL which takes about 90 seconds to run. When I initially ran the SP in my Access Project application, it would not run and returned a "timeout expired" error.

After changing the OLE/DDE timeout option for the Access Project application to 0, the stored procedure will run but takes about 600 seconds to return data to the screen. The record set returned by the sp is approx 70 records.

Any ideas on why the sp runs fine in SQL, but takes forever in my Access Project db? ANy assistance is greatley appreciated

 

[cmmrfrds]

Can you show the code where you are executing the SP or explain where it is located. e.g. Form rowsource? Does it use parameters. If so, describe how you are filling.

 

[greggranger]

When running the sp in Query Analyzer I simply type:

Execute dbo.spMyProcedure @REPORTDATE = '12/31/2004'

When executing the sp in the Access Project appliction, I'm simply double clicking the sp object in the 'Queries' object pane. I plan to eventually call the sp from VB.

The stored procedure accepts the parameter @REPORTDATE. When running the sp in Access, the system prompts the user to input the information.

 

[cmmrfrds]

Here is an example of running a stored procedure through the ADO command object. See if you get the same results.


Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String, bdate As String
Dim cmd As New ADODB.Command, parm1 As ADODB.Parameter
Set rs = New ADODB.Recordset
'--- connect to sql server
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

Set parm1 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append parm1
parm1.Value = "10255"
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic

Set cn.CommandTimeOut = 300 '- 5 minutes
Set cmd.CommandTimeOut = 300
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.CustOrdersDetail"
cmd.ActiveConnection = cn
Set rs = cmd.Execute
Debug.Print "value = "; rs(0)
Debug.Print "record count = "; rs.RecordCount

cn.Close
Set cn = Nothing
Set cmd = Nothing