Cannot sort a row of size 9966, which is greater than the allowable maximum of 8094.

I understand I received this error because of the SQL Server row-size
limit, but I'm not sure how to fix it. The report query joins three
tables, and brings data from those tables. The problem is each table
has a Notes varchar(7800) field. If I comment out all notes field then
the query runs fine. Any ideas on how report needs to designed so that
all data can be shown?
Query to pull data from each table runs fine, I was wondering if I
should split the query into three queries, and then create a report
with two nested sub reports pulling data from each table. Before I go
down this path, if you have any suggestions or have implemented similar
solutions please share your thoughts.
Thanks
YogeshYou could use a convert and convert the the field to a smaller varchar.
Perhaps enough for them to see some of the note and then use drill through
to allow them to pull up another report that shows the notes for the current
record (search BOL for drill through).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Yogesh" <yogeshprabhu@.hotmail.com> wrote in message
news:1124397578.652639.273140@.g49g2000cwa.googlegroups.com...
>I understand I received this error because of the SQL Server row-size
> limit, but I'm not sure how to fix it. The report query joins three
> tables, and brings data from those tables. The problem is each table
> has a Notes varchar(7800) field. If I comment out all notes field then
> the query runs fine. Any ideas on how report needs to designed so that
> all data can be shown?
> Query to pull data from each table runs fine, I was wondering if I
> should split the query into three queries, and then create a report
> with two nested sub reports pulling data from each table. Before I go
> down this path, if you have any suggestions or have implemented similar
> solutions please share your thoughts.
> Thanks
> Yogesh
>

Cannot sort a row of size 8095, which is greater than the allowable maximum of 8094

Hello,
I receive an error "Cannot sort a row of size 8095, which is greater than
the allowable maximum of 8094." when selecting values from a table in the
database. If I delete the existing data and try it, I'm not able to
reproduce it again. I wonder how could this error have possibly occured in
the first place. Any ideas?
Thanks,
Felix.JHi, Felix
Try running your query with "OPTION (ROBUST PLAN)".
Razvan|||Felix,
I also encountered this problem.
Have you tried the OPTION (ROBUST PLAN)? Does it work?
Julius

Cannot sort a row

i encounter this error..
Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?
why this error occur? can someone explain? how to avoid this? thanks!!i encounter this error..

Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?

why this error occur? can someone explain? how to avoid this? thanks!!

see this ...Link (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58825)

Cannot set maximum database size by DBPROP_SSCE_MAX_DATABASE_SIZE;

I try to limit the database size to 2MB by the following code, but it doesn't work, Could somebody help me on it?

Thanks a lot!

Part of my code is:

VariantInit(&dbprop[0].vValue);

VariantInit(&dbprop[1].vValue);

VariantInit(&dbprop[2].vValue);

VariantInit(&dbprop[3].vValue);

// Create an instance of the OLE DB Provider

//

hr = CoCreateInstance( CLSID_SQLSERVERCE_3_0,

0,

CLSCTX_INPROC_SERVER,

IID_IDBInitialize,

(void**)&pIDBInitialize);

if(FAILED(hr))

{

goto Exit;

}

// Initialize a property with name of database

//

dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;

dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;

dbprop[0].vValue.vt = VT_BSTR;

dbprop[0].vValue.bstrVal = SysAllocString( DATABASE_LOG );

if(NULL == dbprop[0].vValue.bstrVal)

{

hr = E_OUTOFMEMORY;

goto Exit;

}

// Initialize property with open mode for database

dbprop[1].dwPropertyID = DBPROP_INIT_MODE;

dbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;

dbprop[1].vValue.vt = VT_I4;

dbprop[1].vValue.lVal = DB_MODE_READ | DB_MODE_WRITE;

// Set max database size

dbprop[2].dwPropertyID = DBPROP_SSCE_MAX_DATABASE_SIZE;

dbprop[2].dwOptions = DBPROPOPTIONS_REQUIRED;

dbprop[2].vValue.vt = VT_I4;

dbprop[2].vValue.lVal = 2; // 2MB

// set max size of temp. database file to 2MB

dbprop[3].dwPropertyID = DBPROP_SSCE_TEMPFILE_MAX_SIZE;

dbprop[3].dwOptions = DBPROPOPTIONS_REQUIRED;

dbprop[3].vValue.vt = VT_I4;

dbprop[3].vValue.lVal = 2; // 2MB

// Initialize the property set

//

dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;

dbpropset[0].rgProperties = dbprop;

dbpropset[0].cProperties = sizeof(dbprop)/sizeof(dbprop[0]);

// Get IDBDataSourceAdmin interface

//

hr = pIDBInitialize->QueryInterface(IID_IDBDataSourceAdmin, (void **) &pIDBDataSourceAdmin);

if(FAILED(hr))

{

goto Exit;

}

// Create and initialize data store

//

hr = pIDBDataSourceAdmin->CreateDataSource( 1, dbpropset, NULL, IID_IUnknown, &pIUnknownSession);

if(FAILED(hr))

{

goto Exit;

}

"Max database size" does not set a limit on the physical size of the sdf file. See http://blogs.msdn.com/sqlservercompact/archive/2007/06/13/the-story-of-max-database-size-connection-string-parameter.aspx

I think there may be a minimum value, which is probably more than 2.

|||Your SSCE props need to be in a seperate propset as follows:

Code Block

DBPROPSET dbpropset[2]; // Property Set used to initialize provider
DBPROP dbprop[1]; // property array set to initialize provider
DBPROP sscedbprop[4]; // Property array for SSCE properties

//Initialize variants
VariantInit(&dbprop[0].vValue);
VariantInit(&sscedbprop[0].vValue);
VariantInit(&sscedbprop[1].vValue);
VariantInit(&sscedbprop[2].vValue);
VariantInit(&sscedbprop[3].vValue);

// Initialize a property with name of database
dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt = VT_BSTR;
dbprop[0].vValue.bstrVal = SysAllocString(a_pwszName);
if(NULL == dbprop[0].vValue.bstrVal)
{
hr = E_OUTOFMEMORY;
goto _ExitCreate;
}

// Initialize the first property set.
dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
dbpropset[0].rgProperties = dbprop;
dbpropset[0].cProperties = sizeof(dbprop)/sizeof(dbprop[0]);

sscedbprop[0].dwPropertyID = DBPROP_SSCE_AUTO_SHRINK_THRESHOLD;
sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[0].vValue.vt = VT_I4;
sscedbprop[0].vValue.intVal = 100;

sscedbprop[1].dwPropertyID = DBPROP_SSCE_MAX_DATABASE_SIZE;
sscedbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[1].vValue.vt = VT_I4;
sscedbprop[1].vValue.intVal = 4091;

if(a_pwszPass!=NULL)
{
// Specify the property for encryption.
sscedbprop[2].dwPropertyID = DBPROP_SSCE_ENCRYPTDATABASE;
sscedbprop[2].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[2].vValue.vt = VT_BOOL;
sscedbprop[2].vValue.boolVal = (a_bEncrypted==TRUE)?VARIANT_TRUE:VARIANT_FALSE;

// Specify the password.
sscedbprop[3].dwPropertyID = DBPROP_SSCE_DBPASSWORD;
sscedbprop[3].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[3].vValue.vt = VT_BSTR;
sscedbprop[3].vValue.bstrVal = SysAllocString(a_pwszPass);
if(NULL == sscedbprop[3].vValue.bstrVal)
{
hr = E_OUTOFMEMORY;
goto _ExitCreate;
}
}
else if(a_bEncrypted)
{
hr = E_INVALIDARG;
CHKHR(hr, L"Encrypted databases require a password", _ExitCreate);
}

dbpropset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT ;
dbpropset[1].rgProperties = sscedbprop;
dbpropset[1].cProperties = sizeof(sscedbprop)/sizeof(sscedbprop[0]) - ((a_pwszPass==NULL)?2:0);

// Get IDBDataSourceAdmin interface
hr = m_pIDBInitialize->QueryInterface(IID_IDBDataSourceAdmin, (void **) &m_pIDBDataSourceAdmin);
CHKHR(hr, L"Failed to obtain IDBDataSourceAdmin interface", _ExitCreate);

// Create and initialize data store
hr = m_pIDBDataSourceAdmin->CreateDataSource(sizeof(dbpropset)/sizeof(dbpropset[0]), dbpropset, NULL, IID_IUnknown, &m_pIUnknownSession);
CHKHR(hr, L"Failed to create data source", _ExitCreate);

// Clear Variants
VariantClear(&dbprop[0].vValue);
VariantClear(&sscedbprop[0].vValue);
VariantClear(&sscedbprop[1].vValue);
VariantClear(&sscedbprop[2].vValue);
VariantClear(&sscedbprop[3].vValue);

Notice how dbpropset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT instead of DBPROPSET_DBINIT

|||Thanks a lot Erik! the link is very helpful, and after testing, i found the minimum size for SQL CE is 16MB.|||Thanks a lot J Shaddix! after using DBPROPSET_SSCE_DBINIT instead of DBPROPSET_DBINIT

, the max size limitation works! and i found the minimum size for SQL CE is 16MB.

|||Good to know about the min size, thanks!