Results 1 to 18 of 18

Thread: [RESOLVED] Multiple-step OLE DB operation generated errors

  1. #1

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Resolved [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

    "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."


    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:
    1. Set rsRptSects = GetReportSections(38,"A")
    2.  
    3. Function GetReportSections(id,status)
    4.     If LenB(status)=0 Then status=NULL
    5.     Dim myCmd   : Set myCmd=Server.CreateObject("ADODB.Command")
    6.     Dim rs      : Set rs=Server.CreateObject("ADODB.Recordset")
    7.     'Response.Write "Setup ID: " & id & "<br>"
    8.     With myCmd
    9.         .CommandType=adCmdStoredProc
    10.         .CommandText="up_mGetReport_Sections"
    11.         .Prepared=True
    12.         .ActiveConnection=cn
    13.         .Parameters.Append .CreateParameter("@Setup_ID", adInteger, adParamInput, 18, cInt(id)) ' Report ID
    14.         .Parameters.Append .CreateParameter("@Status", adVarchar, adParamInput, 100, status)
    15. '       Set GetReportSections=.Execute
    16.         [HL="#FFFF00"]rs.Open myCmd,,adOpenStatic,adLockReadOnly[/HL]
    17.         Set GetReportSections=rs
    18.     End With
    19.     Set myCmd=Nothing
    20.     Set rs=Nothing 
    21. End Function
    Cheers Al
    Last edited by aconybeare; Dec 5th, 2006 at 08:55 AM. Reason: hilight error line

  2. #2
    Addicted Member
    Join Date
    Oct 2004
    Location
    Clane, Ireland
    Posts
    179

    Re: Multiple-step OLE DB operation generated errors

    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

    Please rate the postings

  3. #3

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: Multiple-step OLE DB operation generated errors

    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

  4. #4
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: Multiple-step OLE DB operation generated errors

    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:
    1. .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.

  5. #5

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: Multiple-step OLE DB operation generated errors

    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

  6. #6
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Multiple-step OLE DB operation generated errors

    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

  7. #7

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: Multiple-step OLE DB operation generated errors

    Brucevde,

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

    VB Code:
    1. Function GetReportSections(id,status)
    2.     If LenB(status)=0 Then status=NULL
    3.     Dim myCmd   : Set myCmd=Server.CreateObject("ADODB.Command")
    4.     Dim rs      : Set rs=Server.CreateObject("ADODB.Recordset")
    5.     With myCmd
    6.         .CommandType=adCmdStoredProc
    7.         .CommandText="up_mGetReport_Sections"
    8.         .Prepared=True
    9.         .ActiveConnection=cn
    10.         .Parameters.Append .CreateParameter("@Setup_ID", adInteger, adParamInput,,cInt(id)) ' Report ID
    11.         .Parameters("@Setup_ID").Precision = 18
    12.         .Parameters.Append .CreateParameter("@Status", adVarchar, adParamInput, 100, status)
    13.         '[HL="#FFFF00"]Set GetReportSections=.Execute[/HL]
    14.         rs.Open myCmd,,adOpenStatic,adLockOptimistic
    15.         Set GetReportSections=rs
    16.     End With
    17.     Set myCmd=Nothing
    18.     Set rs=Nothing 
    19. 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

  8. #8
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Multiple-step OLE DB operation generated errors

    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?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: Multiple-step OLE DB operation generated errors

    RobDog888,

    Thanks for your reply

    '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

  10. #10
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Multiple-step OLE DB operation generated errors

    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.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  11. #11
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Multiple-step OLE DB operation generated errors

    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).
    Last edited by brucevde; Dec 5th, 2006 at 12:53 PM.

  12. #12
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Multiple-step OLE DB operation generated errors

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Multiple-step OLE DB operation generated errors

    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Multiple-step OLE DB operation generated errors

    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.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  15. #15
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Multiple-step OLE DB operation generated errors

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Multiple-step OLE DB operation generated errors

    Right, the drop is a separate transaction and does execute.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  17. #17
    Addicted Member
    Join Date
    Oct 2004
    Location
    Clane, Ireland
    Posts
    179

    Re: Multiple-step OLE DB operation generated errors

    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.
    JP

    Please rate the postings

  18. #18

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: Multiple-step OLE DB operation generated errors

    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:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width