Reports Error after reinstalling SQL Server

Question

Post reply

Reports Error after reinstalling SQL Server

dale.schwabjr

SSC-Addicted

Points: 442
More actions
December 5, 2011 at 4:26 am

#264168

All
I upgraded our server from Windows 2003 32 bit running SQL Server 2005 32 bit 4gb of ram to....Windows 2008 64 bit with the same SQL Server 2005 32 bit and 16 gb of ram.
Everything seems to be working except our reports. We use software called "Jeff-Net" to run these reports. All of the reports are working except the large ones. When I run one of the large reports I get the following error.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Creatomg pr a;teromg tab;e 'FakeWorkTable' failed because the minimum row size would be 8214, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8094 by
These reports were working before using the same database. The only thing that changed was the installation of server 08. Is there a setting in SQL Server I am missing?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Weird, looks like an error you could get from a SQL Server 2000 database.

What compatibility level is your DB?

-- Gianluca Sartori

dale.schwabjr SSC-Addicted Points: 442 More actions · Answer 2

Sorry. I am very new to SQL. What do you mean compatibility level?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

It's a standard error and SQL Server works that way.

'The minimum row size would be 8214, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8094 bytes'.

FYI... 'FakeWorkTable' is a temporary table that SQL Server is creating for report processing. Please offload few of the columns from the report. The issue will disappear.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Ah, my bad, I didn't check things properly.

Maybe this thread will help you:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/824140a5-0f2b-4254-a69a-6ecdbffe2bb5

-- Gianluca Sartori

dale.schwabjr SSC-Addicted Points: 442 More actions · Answer 5

The same report worked prior to the upgrade. Why am I just now getting this error?

Also, the only other difference is..I am running SQL 2005 SP4 instead of SP2. Could this be the issue?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

dale.schwabjr (12/5/2011)
The same report worked prior to the upgrade. Why am I just now getting this error?
Also, the only other difference is..I am running SQL 2005 SP4 instead of SP2. Could this be the issue?

It's Data Driven Error. During this run you might be pulling more data in VARCHAR columns.

dale.schwabjr SSC-Addicted Points: 442 More actions · Answer 7

So, should I use the "Robust Plan"? If so, is this just something you add to the query..

like

XXXXXXXXX

XXXXXXXXXXXXX

XXXXXXXXX

OPTION (ROBUST PLAN)

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

That's what Pinal Dave seems to suggest.

I've never used it before.

You can enforce the hint using OPTION (ROBUST PLAN) the way you tried.

-- Gianluca Sartori

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

Gianluca Sartori (12/5/2011)
That's what Pinal Dave seems to suggest.
I've never used it before.
You can enforce the hint using OPTION (ROBUST PLAN) the way you tried.

Will it not compile the SQL everytime to get ROBUST PLAN? However not a major concern (over this error) but needs a thought.

dale.schwabjr SSC-Addicted Points: 442 More actions · Answer 10

Adding

Option Robust Plan to the end of the query did not work

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

ROBUST PLAN

Forces the query optimizer to try a plan that works for the maximum potential row size, possibly at the expense of performance. When the query is processed, intermediate tables and operators may have to store and process rows that are wider than any one of the input rows. The rows may be so wide that, sometimes, the particular operator cannot process the row. If this occurs, the Database Engine produces an error during query execution. By using ROBUST PLAN, you instruct the query optimizer not to consider any query plans that may encounter this problem.

If such a plan is not possible, the query optimizer returns an error instead of deferring error detection to query execution. Rows may contain variable-length columns; the Database Engine allows for rows to be defined that have a maximum potential size beyond the ability of the Database Engine to process them. Generally, despite the maximum potential size, an application stores rows that have actual sizes within the limits that the Database Engine can process. If the Database Engine encounters a row that is too long, an execution error is returned.

For More:

http://msdn.microsoft.com/en-us/library/ms181714.aspx

dale.schwabjr SSC-Addicted Points: 442 More actions · Answer 12

Update. Problem solved. We needed to update the service packs for Crystal reports. Recreate and save the query and it works. Thanks all for the help.