Cannot create a row of size 8075 which is greater than... (-214721

Hello all,
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/d...serr_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCulloughHi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/d...serr_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough