Multi-step OLE DB operation error

[Aaron [SQL Server MVP]]

Ugh, why all this code? Why a command object and a recordset object?

sql = "SELECT Extended_Notes FROM table" ' where ... ?
' or sql = "EXEC proc that SELECTs Extended_Notes"
set rs = conn.execute(sql)
eNotes = rs(0)
' ...
response.write eNotes

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Tyrel Schroeder" <TyrelSchroeder@discussions.microsoft.com> wrote in
message news:B4311675-A0C2-4AF8-83CE-0A00AF890776@microsoft.com...

> I've been trying to get data from an MSSQL database that has a text data

type column. I'm using the GetChunk method (after verifying that adFldLong
is set) and keep getting the following error message:

>
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> Multiple-step OLE DB operation generated errors. Check each OLE DB status

value, if available. No work was done.

>
> The code that is causing the error message is below (it's taken from

[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;194975[/url] but has been
modified a little):

>
> <!-- Code snipped from above -->
> RS2.Close
> RS2.CursorType = 1 'adOpenKeySet
> RS2.LockType = 3 'adLockOptimistic
> Set RS2 = cmd2.Execute
> If Not RS2.EOF Then
> While NOT RS2.EOF
> If IsNull(RS2("Extended_notes")) Then
> MyTextFile.WriteLine("problem here")
> Else
> If (RS2("Extended_notes").Attributes AND adFldLong) = adFldLong Then
> BlobToFile(RS2("Extended_notes"))
> End If
> End If
> RS2.MoveNext
> Wend
>
> Sub BlobToFile(fld) ' Modified from kbArticle
> Dim sData, adLongVarChar, adLongVarWChar, FieldSize
>
> adLongVarChar = 201
> adLongVarWChar = 203
>
> WriteFromUnsizedText fld ' Select statement eliminated here
> End Sub
>
> Sub WriteFromUnsizedText(fld)
> Dim Data, Temp, BLOCK_SIZE
>
> BLOCK_SIZE = 4096
> Do
> Temp = fld.GetChunk(BLOCK_SIZE) ' Error occurs here
> If IsNull(Temp) Then Exit Do
> Data = Temp
> Response.Write(Data)
> Loop While Len(Temp) = BLOCK_SIZE
> End Sub
>
> Any help would be greatly appreciated. Thanks in advance.

[Ray at]

Where is your connection string? Try switching to an OLEDB connection, and
on doing so, I believe you will get a more descriptive error.

See the OLE DB string samples for SQL Server at
[url]http://www.connectionstrings.com/[/url]

Ray at work

"Tyrel Schroeder" <TyrelSchroeder@discussions.microsoft.com> wrote in
message news:B4311675-A0C2-4AF8-83CE-0A00AF890776@microsoft.com...

> I've been trying to get data from an MSSQL database that has a text data
> type column. I'm using the GetChunk method (after verifying that
> adFldLong is set) and keep getting the following error message:
>
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
>
> The code that is causing the error message is below (it's taken from
> [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;194975[/url] but has
> been modified a little):
>
> <!-- Code snipped from above -->
> RS2.Close
> RS2.CursorType = 1 'adOpenKeySet
> RS2.LockType = 3 'adLockOptimistic
> Set RS2 = cmd2.Execute
> If Not RS2.EOF Then
> While NOT RS2.EOF
> If IsNull(RS2("Extended_notes")) Then
> MyTextFile.WriteLine("problem here")
> Else
> If (RS2("Extended_notes").Attributes AND adFldLong) = adFldLong Then
> BlobToFile(RS2("Extended_notes"))
> End If
> End If
> RS2.MoveNext
> Wend
>
> Sub BlobToFile(fld) ' Modified from kbArticle
> Dim sData, adLongVarChar, adLongVarWChar, FieldSize
>
> adLongVarChar = 201
> adLongVarWChar = 203
>
> WriteFromUnsizedText fld ' Select statement eliminated here
> End Sub
>
> Sub WriteFromUnsizedText(fld)
> Dim Data, Temp, BLOCK_SIZE
>
> BLOCK_SIZE = 4096
> Do
> Temp = fld.GetChunk(BLOCK_SIZE) ' Error occurs here
> If IsNull(Temp) Then Exit Do
> Data = Temp
> Response.Write(Data)
> Loop While Len(Temp) = BLOCK_SIZE
> End Sub
>
> Any help would be greatly appreciated. Thanks in advance.

[Rob Collyer]

=?Utf-8?B?VHlyZWwgU2Nocm9lZGVy?=
<TyrelSchroeder@discussions.microsoft.com> wrote in
news:B4311675-A0C2-4AF8-83CE-0A00AF890776@microsoft.com:

> I've been trying to get data from an MSSQL database that has a text
> data type column. I'm using the GetChunk method (after verifying that
> adFldLong is set) and keep getting the following error message:
>
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done.
>
> The code that is causing the error message is below (it's taken from
> [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;194975[/url] but has
> been modified a little):
>
> <!-- Code snipped from above -->
> RS2.Close
> RS2.CursorType = 1 'adOpenKeySet
> RS2.LockType = 3 'adLockOptimistic
> Set RS2 = cmd2.Execute
> If Not RS2.EOF Then
> While NOT RS2.EOF
> If IsNull(RS2("Extended_notes")) Then
> MyTextFile.WriteLine("problem here")
> Else
> If (RS2("Extended_notes").Attributes AND adFldLong) = adFldLong
> Then
> BlobToFile(RS2("Extended_notes"))
> End If
> End If
> RS2.MoveNext
> Wend
>
> Sub BlobToFile(fld) ' Modified from kbArticle
> Dim sData, adLongVarChar, adLongVarWChar, FieldSize
>
> adLongVarChar = 201
> adLongVarWChar = 203
>
> WriteFromUnsizedText fld ' Select statement eliminated here
> End Sub
>
> Sub WriteFromUnsizedText(fld)
> Dim Data, Temp, BLOCK_SIZE
>
> BLOCK_SIZE = 4096
> Do
> Temp = fld.GetChunk(BLOCK_SIZE) ' Error occurs here
> If IsNull(Temp) Then Exit Do
> Data = Temp
> Response.Write(Data)
> Loop While Len(Temp) = BLOCK_SIZE
> End Sub
>
> Any help would be greatly appreciated. Thanks in advance.
>

This generally means the field was blank..... check you have data stored
there, and you are not getting a chunk bigger than what is there.

--
Robert Collyer
[url]www.webforumz.com[/url]
Free Web Design and Development Help, Discussions, tips and Critique!
ASP, VB, .NET, SQL, CSS, HTML, Javascript, Flash, XML, SEO !

[Aaron [SQL Server MVP]]

> conn.Open("DSN=Interaction;uid=AspUser;pwd=passwor d;DATABASE=Interaction")

ODBC has been deprecated. Stop using DSNs! [url]http://www.aspfaq.com/2126[/url]

> I've never had a problem with it in the past.

You're having a problem now. Is it going to hurt to eliminate this as a
possible cause???

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Aaron [SQL Server MVP]]

> I use the command object so I can add a parameter for the stored
procedure....sorry I should have included that.

You can *TRY* this without the command object, e.g.

var = 5
set rs = conn.execute("EXEC procname @intParam=" & var)

> For the recordset, your code also uses a recordset so I don't really see

the difference there.

[url]http://www.aspfaq.com/2191[/url]

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Rob Collyer]

"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
news:uCuONqbbEHA.4092@TK2MSFTNGP11.phx.gbl:

> Where is your connection string? Try switching to an OLEDB
> connection, and on doing so, I believe you will get a more descriptive
> error.
>
> See the OLE DB string samples for SQL Server at
> [url]http://www.connectionstrings.com/[/url]
>
> Ray at work
>
> "Tyrel Schroeder" <TyrelSchroeder@discussions.microsoft.com> wrote in
> message news:B4311675-A0C2-4AF8-83CE-0A00AF890776@microsoft.com...

>> I've been trying to get data from an MSSQL database that has a text
>> data type column. I'm using the GetChunk method (after verifying
>> that adFldLong is set) and keep getting the following error message:
>>
>> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
>> Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done.
>>
>> The code that is causing the error message is below (it's taken from
>> [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;194975[/url] but
>> has been modified a little):
>>
>> <!-- Code snipped from above -->
>> RS2.Close
>> RS2.CursorType = 1 'adOpenKeySet
>> RS2.LockType = 3 'adLockOptimistic
>> Set RS2 = cmd2.Execute
>> If Not RS2.EOF Then
>> While NOT RS2.EOF
>> If IsNull(RS2("Extended_notes")) Then
>> MyTextFile.WriteLine("problem here")
>> Else
>> If (RS2("Extended_notes").Attributes AND adFldLong) = adFldLong
>> Then
>> BlobToFile(RS2("Extended_notes"))
>> End If
>> End If
>> RS2.MoveNext
>> Wend
>>
>> Sub BlobToFile(fld) ' Modified from kbArticle
>> Dim sData, adLongVarChar, adLongVarWChar, FieldSize
>>
>> adLongVarChar = 201
>> adLongVarWChar = 203
>>
>> WriteFromUnsizedText fld ' Select statement eliminated here
>> End Sub
>>
>> Sub WriteFromUnsizedText(fld)
>> Dim Data, Temp, BLOCK_SIZE
>>
>> BLOCK_SIZE = 4096
>> Do
>> Temp = fld.GetChunk(BLOCK_SIZE) ' Error occurs here
>> If IsNull(Temp) Then Exit Do
>> Data = Temp
>> Response.Write(Data)
>> Loop While Len(Temp) = BLOCK_SIZE
>> End Sub
>>
>> Any help would be greatly appreciated. Thanks in advance.

>
>
>

What error exactly were you getting?

--
Robert Collyer
[url]www.webforumz.com[/url]
Free Web Design and Development Help, Discussions, tips and Critique!
ASP, VB, .NET, SQL, CSS, HTML, Javascript, Flash, XML, SEO !

[Ray at]

"Rob Collyer" <webforumz@webforumz.com> wrote in message
news:Xns952BCC4E4F2B3webforumzwebforumzco@217.32.2 52.50...

>> "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in

> What error exactly were you getting?

I'm not getting an error. The OP is.

Ray at work

[Ken Schaefer]

As the error says, multiple errors were generated.

If you iterate through the errors collection of the ADODB.Connection object,
you can get access to the various errors that were thrown. That might give
you more of a clue as to what the real problem is.

Cheers
Ken

"Tyrel Schroeder" <TyrelSchroeder@discussions.microsoft.com> wrote in
message news:8CB1D414-6B25-4814-B970-5EAF2D877DB5@microsoft.com...

> Rob Collyer" wrote:
>

> > "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
> > news:uCuONqbbEHA.4092@TK2MSFTNGP11.phx.gbl:
> >

> > > Where is your connection string? Try switching to an OLEDB
> > > connection, and on doing so, I believe you will get a more descriptive
> > > error.
> > >
> > > See the OLE DB string samples for SQL Server at
> > > [url]http://www.connectionstrings.com/[/url]
> > >
> > > Ray at work
> > >
> > > "Tyrel Schroeder" <TyrelSchroeder@discussions.microsoft.com> wrote in
> > > message news:B4311675-A0C2-4AF8-83CE-0A00AF890776@microsoft.com...
> > >> I've been trying to get data from an MSSQL database that has a text
> > >> data type column. I'm using the GetChunk method (after verifying
> > >> that adFldLong is set) and keep getting the following error message:
> > >>
> > >> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> > >> Multiple-step OLE DB operation generated errors. Check each OLE DB
> > >> status value, if available. No work was done.
> > >>
> > >> The code that is causing the error message is below (it's taken from
> > >> [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;194975[/url] but
> > >> has been modified a little):
> > >>
> > >> <!-- Code snipped from above -->
> > >> RS2.Close
> > >> RS2.CursorType = 1 'adOpenKeySet
> > >> RS2.LockType = 3 'adLockOptimistic
> > >> Set RS2 = cmd2.Execute
> > >> If Not RS2.EOF Then
> > >> While NOT RS2.EOF
> > >> If IsNull(RS2("Extended_notes")) Then
> > >> MyTextFile.WriteLine("problem here")
> > >> Else
> > >> If (RS2("Extended_notes").Attributes AND adFldLong) = adFldLong
> > >> Then
> > >> BlobToFile(RS2("Extended_notes"))
> > >> End If
> > >> End If
> > >> RS2.MoveNext
> > >> Wend
> > >>
> > >> Sub BlobToFile(fld) ' Modified from kbArticle
> > >> Dim sData, adLongVarChar, adLongVarWChar, FieldSize
> > >>
> > >> adLongVarChar = 201
> > >> adLongVarWChar = 203
> > >>
> > >> WriteFromUnsizedText fld ' Select statement eliminated here
> > >> End Sub
> > >>
> > >> Sub WriteFromUnsizedText(fld)
> > >> Dim Data, Temp, BLOCK_SIZE
> > >>
> > >> BLOCK_SIZE = 4096
> > >> Do
> > >> Temp = fld.GetChunk(BLOCK_SIZE) ' Error occurs here
> > >> If IsNull(Temp) Then Exit Do
> > >> Data = Temp
> > >> Response.Write(Data)
> > >> Loop While Len(Temp) = BLOCK_SIZE
> > >> End Sub
> > >>
> > >> Any help would be greatly appreciated. Thanks in advance.
> > >
> > >
> > >

> >
> > What error exactly were you getting?
> >
> > --
> > Robert Collyer
> > [url]www.webforumz.com[/url]
> > Free Web Design and Development Help, Discussions, tips and Critique!
> > ASP, VB, .NET, SQL, CSS, HTML, Javascript, Flash, XML, SEO !
> >

>
> Here's the original error message I was receiving:
>
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done.
>