-
Dec 5th, 2006, 07:18 AM
#1
Thread Starter
Fanatic Member
[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:
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
Last edited by aconybeare; Dec 5th, 2006 at 08:55 AM.
Reason: hilight error line
-
Dec 5th, 2006, 07:56 AM
#2
Addicted Member
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
-
Dec 5th, 2006, 09:00 AM
#3
Thread Starter
Fanatic Member
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
-
Dec 5th, 2006, 10:36 AM
#4
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:
.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.
-
Dec 5th, 2006, 10:51 AM
#5
Thread Starter
Fanatic Member
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
-
Dec 5th, 2006, 11:27 AM
#6
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
-
Dec 5th, 2006, 11:54 AM
#7
Thread Starter
Fanatic Member
Re: Multiple-step OLE DB operation generated errors
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
-
Dec 5th, 2006, 12:03 PM
#8
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Dec 5th, 2006, 12:10 PM
#9
Thread Starter
Fanatic Member
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
-
Dec 5th, 2006, 12:24 PM
#10
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Dec 5th, 2006, 12:47 PM
#11
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.
-
Dec 5th, 2006, 02:07 PM
#12
Re: Multiple-step OLE DB operation generated errors
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
-
Dec 5th, 2006, 02:09 PM
#13
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
-
Dec 5th, 2006, 02:14 PM
#14
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Dec 5th, 2006, 03:51 PM
#15
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
-
Dec 5th, 2006, 06:37 PM
#16
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Dec 5th, 2006, 06:51 PM
#17
Addicted Member
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
-
Dec 6th, 2006, 04:40 AM
#18
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|