[RESOLVED] Multiple-step OLE DB operation generated errors

Hi,

I've got a problem with trying to open the following recordset. code breaks on the highlighted line

<sub>"Microsoft OLE DB Provider for SQL Server error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."</sub>

I've checked my version of MDAC it's 2.8 using SQL Server 2000 on windows 2003 server. Also not using the security info thing in my connection string

Does anyone have any idea what the problem might be?

VB Code:

Set rsRptSects = GetReportSections(38,"A")
 
Function GetReportSections(id,status)
    If LenB(status)=0 Then status=NULL
    Dim myCmd   : Set myCmd=Server.CreateObject("ADODB.Command")
    Dim rs      : Set rs=Server.CreateObject("ADODB.Recordset")
    'Response.Write "Setup ID: " & id & "<br>"
    With myCmd
        .CommandType=adCmdStoredProc
        .CommandText="up_mGetReport_Sections"
        .Prepared=True
        .ActiveConnection=cn
        .Parameters.Append .CreateParameter("@Setup_ID", adInteger, adParamInput, 18, cInt(id)) ' Report ID
        .Parameters.Append .CreateParameter("@Status", adVarchar, adParamInput, 100, status)
'       Set GetReportSections=.Execute
        [HL="#FFFF00"]rs.Open myCmd,,adOpenStatic,adLockReadOnly[/HL]
        Set GetReportSections=rs
    End With
    Set myCmd=Nothing
    Set rs=Nothing  
End Function

Cheers Al

Have you stepped through the code? and if so where abouts are you getting the error? Is the connection open?

I have to be honest and say that I would use the DIM statements different to you, I would have:

Code:

---

Dim myCmd as new adodb.command

---

Also I would add AS ADODB.RECORDSET onto the end of your function, so that the program knows what's comming back.

JP,
I'm using asp (vbscript) so can't declare variables as you suggest

Connection is definitely open, have added a hilight in the above code to show where the break is happening.

Cheers Al

You don't need to specify length for parameters of data type adInteger, and the value you have specified is also wrong (must be 4).

VB Code:

.Parameters.Append .CreateParameter("@Setup_ID", adInteger, adParamInput, 18, cInt(id))

Leave this parameter empty like this:
.Parameters.Append .CreateParameter("@Setup_ID", adInteger, adParamInput, , cInt(id))[/Highlight]

The "multistep OLEDB" error is usually caused by wrong parameters, e.g. passing a string to a parameter expecting a numeric value, or by specifying a wrong value for length.

kaffenils,

Unfortunately that's not made any difference

I've changed the param length to empy, 4 and 9 without success
I've also checked the table and setup_id is numeric(18,0)

here is the sproc declaration -

Code:

---

USE Sirius
Go
If Exists (SELECT * FROM sysobjects WHERE ID =
  OBJECT_ID('up_mGetReport_Sections') AND sysstat & 0xf = 4)
  DROP PROCEDURE up_mGetReport_Sections
Go
CREATE Procedure up_mGetReport_Sections
(
        @Setup_ID numeric
        ,@Status varchar(100)=Null
)
AS

SET NoCount ON
DECLARE @All bit

IF @Status Is Null
        SET @All=1
ELSE
        SET @All=0

SELECT Section_Id
        ,Setup_Id
        ,Section_No
        ,Section_Title
        ,Section_Type
        ,MultiCols
        ,Section_Header
        ,Section_Footer
        ,Date_Created
        ,Status
        ,Restricted
FROM REPORT_SECTIONS
WHERE Setup_Id=@Setup_ID -- Report_ID
AND ((@All=1) OR (Status IN (SELECT Convert(varchar,value) FROM dbo.Split(@Status,','))))
ORDER BY Section_No

SET NoCount OFF
GO

---

You must specify the Precision and Scale properties of the parameter object when using the Numeric datatype.

Code:

---

'in your case the Scale is 0, the default so no need to change it
.Parameters.Append .CreateParameter("@Setup_ID", adNumeric, adParamInput, , cInt(id))
.Parameters("@Setup_ID").Precision = 18

---

Brucevde,

Thanks for your reply, unfortunately that's not made any difference

VB Code:

Function GetReportSections(id,status)
    If LenB(status)=0 Then status=NULL
    Dim myCmd   : Set myCmd=Server.CreateObject("ADODB.Command")
    Dim rs      : Set rs=Server.CreateObject("ADODB.Recordset")
    With myCmd
        .CommandType=adCmdStoredProc
        .CommandText="up_mGetReport_Sections"
        .Prepared=True
        .ActiveConnection=cn
        .Parameters.Append .CreateParameter("@Setup_ID", adInteger, adParamInput,,cInt(id)) ' Report ID
        .Parameters("@Setup_ID").Precision = 18
        .Parameters.Append .CreateParameter("@Status", adVarchar, adParamInput, 100, status)
        '[HL="#FFFF00"]Set GetReportSections=.Execute[/HL]
        rs.Open myCmd,,adOpenStatic,adLockOptimistic
        Set GetReportSections=rs
    End With
    Set myCmd=Nothing
    Set rs=Nothing  
End Function

If I execute the above function with the hilighted line and loose the rs stuff it works okay.

The reason I'm trying to execute using a recordset object is so that I can specify the cursor type and lock type as when executing multiple recordsets in a transaction I get an error ("Cannot create new connection because in manual or distributed transaction mode.").
Research on the net indicates that if I use something other than the firehose cursor it should be allowed

Cheers Al

Depending on your sp, it looks like you are running the same sp twice, each from a different method. If your sp is updating a record and the rs starts to execute then that can also generate the multistep error. Why not just use the sp?

RobDog888,

Thanks for your reply

Quote:

---

'Set GetReportSections=.Execute

---

I'm sorry I don't follow what you're saying, however I think you mean the above hilighted line, which is commented out so as far as I can tell not executing twice?

Cheers Al

Oh I seen that you had referred to it as working but not that both are executed ;)

Your sp is just executing a select query with a subquery so no updates occuring so its good there exc ept for the part where you are dropping the sp if exists and then recreating it. Why? This may be an issue if it is being created and dropped by multiple calls from your code. Try commenting it out. You are using parameters anyways so there is no need to delete the sp each time. Plus, there will be less beinfit from the sp getting optimized from execution multiple times etc.

I was able to reproduce the error and found a work around. Not exactly sure why this makes a difference but add

rs.CursorLocation = adUseClient

before opening the recordset.

[Edit] Commenting out the .Prepared = True fixes the problem as well (without the rs.CursorLocation statement).

Quote:

---

Originally Posted by RobDog888

Oh I seen that you had referred to it as working but not that both are executed ;)

Your sp is just executing a select query with a subquery so no updates occuring so its good there exc ept for the part where you are dropping the sp if exists and then recreating it. Why? This may be an issue if it is being created and dropped by multiple calls from your code. Try commenting it out. You are using parameters anyways so there is no need to delete the sp each time. Plus, there will be less beinfit from the sp getting optimized from execution multiple times etc.

---

RD - the droping re-creating is not something that happens when the SP runs.... I assume that their environment is much like ours.... where it's part of a larger script that gets run during upgrades. Since you can only ALTER PROCEDURE when the procedure already exists, and you can only CREATE PROCEDURE if it doesn't exist.... it becomes convoluted when trying to send updated stored procedures. In our case, we simply check to see if the SP exists, and if so, drop it and then create it. If it doesn't then it simply gets created.

-tg

Good call Bruce... the Prepared is used when dealing with SQL strings passed from VB, andthat tells SQL to cache it (or use it from cache if already there) and does not apply to stored procedures. If I remember right. Why it can't simply ignore the setting, I don't know.

-tg

I'm probably showing my lack of experience with sp's but wouldnt it drop it each time the sp is executed? The sp script looks like its auto generated script of the existing sp object from when you right click on it and select script or such.

No.... the SCRIPT will drop and recreate the SP... but the SP itself will not drop itself.... note: THe drop is outside the CREATE PROCEDURE....

-tg

Right, the drop is a separate transaction and does execute.

Here is an example of VB6 code I used to run a stored procedure, and create a recordset, which is returned. Perhaps it will help?

Code:

---

Function RetrieveFileByProgram(prmPgmId As Long) As ADODB.Recordset

    Dim adcCmd As New ADODB.Command

    With adcCmd
        .CommandText = "spPgmFileListing"
        .CommandType = adCmdStoredProc
        .ActiveConnection = objDB.DbConnection
        .Prepared = True
        .Parameters.Append .CreateParameter("PgmId", adInteger, adParamInput, 10)
        .Parameters.Item("PgmId") = prmPgmId
        Set RetrieveFileByProgram = .Execute
    End With
   
End Function

---

My stored procedure is as follows:

Code:

---

Create Procedure spPgmFileListing
        @PgmId                        Int

As

        Select Distinct B.FileNme,
                Case B.Attribute
                        When 'P' Then 'Physical'
                        When 'L' Then 'Logical'
                End As FileType,
                B.Descript,
                Case B.Keyed
                        When 0 Then 'No' Else 'Yes'
                End As FileKeyed,
                Case B.UniqueKey
                        When 0 Then 'No' Else 'Yes'
                End As UKey,
                C.LibraryName,
                Case A.DrivingFile
                        When 0 Then 'No' Else 'Yes'
                End As Driver,
                case IsNull(D.FilesSelectOmitId,-1)
                        When -1 Then 'No' Else 'Yes'
                End As SelectOmit,
                Case IsNull(E.FileTriggerId, -1)
                        When -1 Then 'No' Else 'Yes'
                End As FileTriggers,
                A.PgmFilesId,
                B.FileId
        From PgmFiles A
        Inner Join Files B
                On A.FileId = B.FileId
        Inner Join Libraries C
                On B.LibraryId = C.LibraryId
        Left Join FilesSelectOmit D
                On B.FileId = D.FileId
        Left Join FileTriggers E
                On B.FileId = E.FileId
        Where A.Pgm = @PgmId
        Order By Driver Desc

Go

---

To run a stored procedure that does not return a recordset, I use the following:

Code:

---

Sub DeleteTrigger(prmTriggerId As Long)

    Dim adcCmd As New ADODB.Command

    With adcCmd
        .CommandText = "spDeleteTrigger"
        .CommandType = adCmdStoredProc
        .ActiveConnection = objDB.DbConnection
        .Prepared = True
        .Parameters.Append .CreateParameter("TriggerId", adInteger, adParamInput)
        .Parameters.Item("TriggerId") = prmTriggerId
        .Execute
    End With

End Sub

---

Stored Procedure:

Code:

---

Create Procedure spDeleteTrigger
        @TriggerId                Int

As

        Delete FileTriggers
        Where FileTriggerId = @TriggerId

Go

---

I hope some of this may be of use.

Brucevde: I've dispatched the .prepared=True, and the recordset returns, but the cursorType and lockType are being ignored, I know this because when I try to execute multiple recordsets I get the error -
"Cannot create new connection because in manual or distributed transaction mode."
If I then put in a check to get the recordcount I get the error -
"Rowset position cannot be restarted."
Which I think is telling me that my cursor is adOpenForwardOnly or adOpenDynamic.

EDIT: I then put in the rs.CursorLocation=adUseClient and it's worked

Thanks a bunch

TG:

Quote:

---

RD - the droping re-creating is not something that happens when the SP runs.... I assume that their environment is much like ours.... where it's part of a larger script that gets run during upgrades. Since you can only ALTER PROCEDURE when the procedure already exists, and you can only CREATE PROCEDURE if it doesn't exist.... it becomes convoluted when trying to send updated stored procedures. In our case, we simply check to see if the SP exists, and if so, drop it and then create it. If it doesn't then it simply gets created.

---

Your assumption was/is spot on

Thanks to everyone who replied, I really appreciate your help.

Cheers Al