2

In most of the cases this is due to overall row size for some particular record that could throw such error. But my case is different. I run a report in one server and it works fine. But the same report and the same database throw the row size error: Cannot create a row of size 8785 which is greater than the allowable maximum of 8060. Any suggestions as to how I can resolve this?

user2573455
  • 21
  • 1
  • 3
  • You need to show us the table definition as well as other settings such as whether snapshot isolation is enabled for the database... – Aaron Bertrand Jul 11 '13 at 16:11
  • The entire SSRS report is working fine in test server but the issue is in production server. Strangely, the table/view definitions are same in both the environments. There is no difference at the database level. I have cross checked this point about 15 times. Just wanted to know if there is any other factor which could cause that error. – user2573455 Jul 11 '13 at 16:19
  • So you get this error when you *run* the report? What does the report do? Does it call a stored procedure that creates a #temp table? Could you provide more details so we know what the heck you're talking about? – Aaron Bertrand Jul 11 '13 at 16:21
  • The query behind the report is just a select statement with few joins between 7-8 tables. There is no temp table or stored procedure involved at all. It works in one test envi but throws the above error in prod. – user2573455 Jul 11 '13 at 16:25
  • 2
    Doesn't make sense. A SELECT query doesn't create rows, so... – Aaron Bertrand Jul 11 '13 at 16:26
  • @AaronBertrand `SELECT * INTO...` – Karl Jul 11 '13 at 16:43
  • @AaronBertrand reports can do lot of things. Weird things. But there must be something else than that SELECT statement. Maybe there's a conversion int he statement? SELECT INTO? – Csaba Toth Jul 11 '13 at 16:43
  • @Karl why would a report be doing SELECT INTO? – Aaron Bertrand Jul 11 '13 at 16:43
  • @CsabaToth that is why I asked what the report does. The answer was it *is just a select statement.* So, you guess. I'm done. – Aaron Bertrand Jul 11 '13 at 16:44

3 Answers3

3

From http://blogs.msdn.com/b/msdnts/archive/2006/12/01/row-size-limitation-in-sql-2000-and-2005.aspx:

When you creating a table, you may encounter the following information: ' Table has been created but its maximum row size(11038) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE Of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Something in the report the second time around is hitting this maximum; possibly an edge case that you haven't spotted, where there is a difference in the data.

I'd suggest that you look at the allocations given to your database fields - for example, can a varchar(8000) actually be switched to varchar(max) - or look at restructuring your data across multiple tables.

Adrian Wragg
  • 7,311
  • 3
  • 26
  • 50
0

I was running into the same error using a query that just did a select statement. I converted a massive view I was using into a CTE and was still getting the error. I then removed unused columns from the CTE and the error went away. Not really sure what was going on since I thought the query optimized was supposed to ignore unused select columns.

Chris
  • 117
  • 1
  • 10
0

Having run into a similar problem with a single table that had this error for no apparent reason, running this query solved the issue:

ALTER TABLE [Table] REBUILD;

This does not change the values in the table, but updating row values with something other than NULL does become possible.

Apparently, previous edits can stay in SQL Server memory, even if the current table version seems fine in size. See also: Maximum row size exceeds the allowed maximum of 8060 bytes.

Denis G. Labrecque
  • 1,023
  • 13
  • 29