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
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Thursday, March 8, 2012
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/de...err_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCullough
Hi,
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/de...err_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough
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/de...err_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCullough
Hi,
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/de...err_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough
Cannot create a row of size %d which is greater than the allowable maximum of %d
I'm trying to execute an insert with a PreparedStatement object and I am getting the following Error message:
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8320 which is greater than the allowable maximum of 8060.
Does anyone have a solution for this?You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).|||Originally posted by Mulligan
You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).
Thanks for the link. I had already taken a look there before finding this forum but I couldn't understand why my data was longer than the page size for SQL because I was only inserting a small amount of data.
I found that my problem due to MS SQL creating a temporary table based on my Insert Statement using the table definition for the size of the columns. In this case, I had a nvarchar(4000) column. This then generated a temp table with a column size equivalent to 8363 bytes but the page size is 8060. This happens even though the data I was inserting was much smaller than the max length.
To resolve the problem I had to reduce to the column size to a maximum of 3868. This resolved the problem.|||Nice bit of detective work. And thanks for posting back with the solution - it makes a refreshing change from all the "How can I fix problem X?" "Like this." "Bye" postings :)
Mull.
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8320 which is greater than the allowable maximum of 8060.
Does anyone have a solution for this?You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).|||Originally posted by Mulligan
You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).
Thanks for the link. I had already taken a look there before finding this forum but I couldn't understand why my data was longer than the page size for SQL because I was only inserting a small amount of data.
I found that my problem due to MS SQL creating a temporary table based on my Insert Statement using the table definition for the size of the columns. In this case, I had a nvarchar(4000) column. This then generated a temp table with a column size equivalent to 8363 bytes but the page size is 8060. This happens even though the data I was inserting was much smaller than the max length.
To resolve the problem I had to reduce to the column size to a maximum of 3868. This resolved the problem.|||Nice bit of detective work. And thanks for posting back with the solution - it makes a refreshing change from all the "How can I fix problem X?" "Like this." "Bye" postings :)
Mull.
Subscribe to:
Posts (Atom)