ADO errors after changing SP to use local variable

Changed stored procedure

[dbo].[spLogonName @.pNewLogonName varchar(60) AS

SELECT * FROM .dbo.tblUser Where vcLogonName = @.pNewLogonName

to

[dbo].[spLogonName @.pNewLogonName varchar(60) AS

DECLARE @.Local_pNewLogonName varchar(60)

SET @.Local_pNewLogonName = @.pNewLogonName

SELECT * FROM .dbo.tblUser Where vcLogonName = @.Local_pNewLogonName

and started getting this error on the web page.

ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Does anyone know why this is happening? Nothing on the site has changed. If I change the sp back the errors go away. I'm trying to use local variables in all SP to avoid the slowness that can happen when using the parameter varibles.

Hi,

I guess you've changed the stored proc to avoid parameter sniffing and make sure the execution plan is stable? :-)

Could you try adding SET NOCOUNT ON to the beginning of the procedure? I suspect you're getting an additional DONE token from the SET statement and multiple recordsets. You could probably test that by calling NextRecordset method of the recordset.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello and thanks for the response.

Yes, I'm trying to change the sp to avoid sniffing.

I added SET NOCOUNT ON to the beginning of the procedure but it did not help. I removed the local variable stuff and left the NOCOUNT statement in and it doesn't like it either. It seems like it doesn't like any statements in the sp except the SELECT.

Here is more information if it helps.

Set query = Session_LogonNameQuery(NewLogonName)
Set oRecordSet = New ADODB.Recordset
Set oRecordSet = RetrieveDataRS(myconn, query, adOpenForwardOnly, adLockOptimistic, 1)

Even though I have adLockOptimistic selected the recordset has adLockReadOnly set.

Any help would be appreciated.