Expression Too Complex error - mdb

DataAdapter.update(dataSet) exception Error:
"Changes not saved to database. Expression Too Complex"
Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
Without diving into the details on the one-to-many 99 column Access (Main)
table , can someone tell me the reason for the error? Is it really the
complexity of the SQL statement? Would SQL server help me? Does SQL Server
allow update() of a queries. StevePlease send us the text of the query you are trying. Based on the little
data we have here, UPDATE() is likely not usable in your situation, since
this is only for use in triggers.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Steve B." <SteveB@.discussions.microsoft.com> wrote in message
news:6284FB15-F8CE-4BDA-9060-B4B09428F4B6@.microsoft.com...
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries. Steve|||hi steve
SQL Server allows updates. there might be an error in the sql string that u
were trying to pass.
please check the query and revert back.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:

> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL Serve
r
> allow update() of a queries. Steve|||> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries.
Just off the top of my head, each version of Access has a different maximum
limit on how long an SQL statement is allowed to be. I have actually gotten
that error. That's all I can think of though.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||I hope this is what your looking for (see below). In the mean time, I found
this (please see first few paragraphs):
http://support.microsoft.com/defaul...kb;en-us;192716
I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
thinking of dividing my Main table into 4 or 5 tables indexed on part number
(part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
workaround. What a mess..
The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexed
(dropdown) from their own table in the "Main" table but, every Characteristi
c
field is different (blueprint dimensions, note number, etc) . I really nee
d
30 characteristics not 21. Can't get there from here.
I don't know if a commercial database program like SQL server will help me.
Thats why I'm asking. Comments?
(copied from VS Dataadapter wizard)
SELECT
MainID,
PartNumber,
Nomenclature,
PartStatus,
CoverageBy,
CoverageByDate,
Engine,
Service,
Originator,
RequirementSourceCodes,
PartPointOfContact,
VerificationDate,
CharacteristicCodes1,
ProcessCodes1,
LocationCodes1,
Characteristic1,
CharacteristicCodes2,
LocationCodes2,
ProcessCodes2,
Characteristic2,
CharacteristicCodes3,
LocationCodes3,
ProcessCodes3,
Characteristic3,
CharacteristicCodes4,
LocationCodes4,
ProcessCodes4,
Characteristic4,
CharacteristicCodes5,
LocationCodes5,
ProcessCodes5,
Characteristic5,
CharacteristicCodes6,
LocationCodes6,
ProcessCodes6,
Characteristic6,
CharacteristicCodes7,
LocationCodes7,
ProcessCodes7,
Characteristic7,
CharacteristicCodes8,
LocationCodes8,
ProcessCodes8,
Characteristic8,
CharacteristicCodes9,
LocationCodes9,
ProcessCodes9,
Characteristic9,
CharacteristicCodes10,
LocationCodes10,
ProcessCodes10,
Characteristic10,
CharacteristicCodes11,
LocationCodes11,
ProcessCodes11,
Characteristic11,
CharacteristicCodes12,
LocationCodes12,
ProcessCodes12,
Characteristic12,
CharacteristicCodes13,
LocationCodes13,
ProcessCodes13,
Characteristic13,
CharacteristicCodes14,
LocationCodes14,
ProcessCodes14,
Characteristic14,
CharacteristicCodes15,
LocationCodes15,
ProcessCodes15,
Characteristic15,
CharacteristicCodes16,
LocationCodes16,
ProcessCodes16,
Characteristic16,
CharacteristicCodes17,
LocationCodes17,
ProcessCodes17,
Characteristic17,
CharacteristicCodes18,
LocationCodes18,
ProcessCodes18,
Characteristic18,
CharacteristicCodes19,
LocationCodes19,
ProcessCodes19,
Characteristic19,
CharacteristicCodes20,
LocationCodes20,
ProcessCodes20,
Characteristic20,
CharacteristicCodes21,
LocationCodes21,
ProcessCodes21,
Characteristic21,
Comments,
AnnualCSIConfirmation,
DataRowState
FROM
Main
WHERE
(AnnualCSIConfirmation = 'Yes') AND
(PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None On
Order (Inactive)') ORDER BY MainID
"Chandra" wrote:
> hi steve
> SQL Server allows updates. there might be an error in the sql string that
u
> were trying to pass.
> please check the query and revert back.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Yes!
i agree with you. The message mentions that, the query must be optimized.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Mike Labosh" wrote:

> Just off the top of my head, each version of Access has a different maximu
m
> limit on how long an SQL statement is allowed to be. I have actually gott
en
> that error. That's all I can think of though.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||hi
looks like u need to normalize your table:
something like:
MainID,
Charid,
CharacteristicCodes,
ProcessCodes,
LocationCodes,
Characteristic,
but, this is out of scope now:
just try to remove "ORDER BY MainID" and check, it might speed up the
process and u can escape from the error.
or
where part:
AnnualCSIConfirmation = 'Yes' AND
PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
(buy/make part)', 'None On Order (Inactive)' )
please revert back with the results
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> I hope this is what your looking for (see below). In the mean time, I fou
nd
> this (please see first few paragraphs):
> http://support.microsoft.com/defaul...kb;en-us;192716
> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
> thinking of dividing my Main table into 4 or 5 tables indexed on part numb
er
> (part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
> workaround. What a mess..
> The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexe
d
> (dropdown) from their own table in the "Main" table but, every Characteris
tic
> field is different (blueprint dimensions, note number, etc) . I really n
eed
> 30 characteristics not 21. Can't get there from here.
> I don't know if a commercial database program like SQL server will help me
.
> Thats why I'm asking. Comments?
> (copied from VS Dataadapter wizard)
> SELECT
> MainID,
> PartNumber,
> Nomenclature,
> PartStatus,
> CoverageBy,
> CoverageByDate,
> Engine,
> Service,
> Originator,
> RequirementSourceCodes,
> PartPointOfContact,
> VerificationDate,
> CharacteristicCodes1,
> ProcessCodes1,
> LocationCodes1,
> Characteristic1,
> CharacteristicCodes2,
> LocationCodes2,
> ProcessCodes2,
> Characteristic2,
> CharacteristicCodes3,
> LocationCodes3,
> ProcessCodes3,
> Characteristic3,
> CharacteristicCodes4,
> LocationCodes4,
> ProcessCodes4,
> Characteristic4,
> CharacteristicCodes5,
> LocationCodes5,
> ProcessCodes5,
> Characteristic5,
> CharacteristicCodes6,
> LocationCodes6,
> ProcessCodes6,
> Characteristic6,
> CharacteristicCodes7,
> LocationCodes7,
> ProcessCodes7,
> Characteristic7,
> CharacteristicCodes8,
> LocationCodes8,
> ProcessCodes8,
> Characteristic8,
> CharacteristicCodes9,
> LocationCodes9,
> ProcessCodes9,
> Characteristic9,
> CharacteristicCodes10,
> LocationCodes10,
> ProcessCodes10,
> Characteristic10,
> CharacteristicCodes11,
> LocationCodes11,
> ProcessCodes11,
> Characteristic11,
> CharacteristicCodes12,
> LocationCodes12,
> ProcessCodes12,
> Characteristic12,
> CharacteristicCodes13,
> LocationCodes13,
> ProcessCodes13,
> Characteristic13,
> CharacteristicCodes14,
> LocationCodes14,
> ProcessCodes14,
> Characteristic14,
> CharacteristicCodes15,
> LocationCodes15,
> ProcessCodes15,
> Characteristic15,
> CharacteristicCodes16,
> LocationCodes16,
> ProcessCodes16,
> Characteristic16,
> CharacteristicCodes17,
> LocationCodes17,
> ProcessCodes17,
> Characteristic17,
> CharacteristicCodes18,
> LocationCodes18,
> ProcessCodes18,
> Characteristic18,
> CharacteristicCodes19,
> LocationCodes19,
> ProcessCodes19,
> Characteristic19,
> CharacteristicCodes20,
> LocationCodes20,
> ProcessCodes20,
> Characteristic20,
> CharacteristicCodes21,
> LocationCodes21,
> ProcessCodes21,
> Characteristic21,
> Comments,
> AnnualCSIConfirmation,
> DataRowState
> FROM
> Main
> WHERE
> (AnnualCSIConfirmation = 'Yes') AND
> (PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
> part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None
On
> Order (Inactive)') ORDER BY MainID
>
> "Chandra" wrote:
>|||>> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
You have a poorly designed table. Any shuffling you do without a thorough
analysis of your business model and logic rules, might still leave the
schema unmaintainable and make the queries complex.
Instead of splitting up a single set of attribute into groups of {1st set of
cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the table
better. Understand and analyze the entities in question and identify each of
the attributes explicitly. Identify the dependencies among these attributes
and consider decomposing them into multiple tables appropriately, if
required.
Even, without considering any ill-effects of under normalized schemas, with
certain assumptions based on the query you posted, you might benefit from
having your table structured like:
CREATE TABLE Parts (
Part_nbr INT NOT NULL PRIMARY KEY,
.. ) ;
CREATE TABLE Characteristics (
Character_id INT NOT NULL ,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Code ...
Characteristic...
PRIMARY KEY ( Character_id, Part_nbr ) );
CREATE TABLE Processes (
Process_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Process_code ...
PRIMARY KEY ( Process_id, Part_nbr ) );
CREATE TABLE Locations (
Location_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Location_code ...
PRIMARY KEY ( Location_id, Part_nbr );
Now the query is as simple as:
SELECT p1.Part_nbr,...
c1.code, ...
p2.process_code,...
l1.location_code...
FROM Parts p1
INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
...
This could be your comparable resultset which you might consider using for a
variety of purposes. For the specific case you mentioned in your post, you
can simply pivot this data, either using your client programming language or
if done within the server, using the popular pivoting technique detailed in
MSKB ( support.microsoft.com ) : 175574
Anith|||Thank You Chandra. That was an easy fix but I regret it didn't work. It wa
s
definitly worth a try in order to escape. I agree. Thanks Steve
"Chandra" wrote:
> hi
> looks like u need to normalize your table:
> something like:
> MainID,
> Charid,
> CharacteristicCodes,
> ProcessCodes,
> LocationCodes,
> Characteristic,
> but, this is out of scope now:
> just try to remove "ORDER BY MainID" and check, it might speed up the
> process and u can escape from the error.
> or
> where part:
> AnnualCSIConfirmation = 'Yes' AND
> PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
> (buy/make part)', 'None On Order (Inactive)' )
> please revert back with the results
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Anith,
Thank You for your input. I've sent a copy of your post to a coworker who
understands database design more then I - such is the problem with using
wizards. A few questions so I (we) can better understand the design/context
.
Assumptions:
1. You appear to be dividing the fields into 4 tables – Parts,
CharacteristicsCode (identified in post as Characteristics), Process and
Locations. There is an additional field Characteristic that varies with eac
h
of the other three fields. So for each of the [possible] 30 Characteristic’s
each of them have an associated CharacteristicsCode , Process and Location.
I guess the structure of all 4 tables (except Parts) are exactly the same an
d
doesn’t matter. With that said, the CharacteristicsCode, Process, Locatio
ns
and Characteristic tables will have the exact same table structure and kind
of relationship.
2. It’s assumed the Parts table will contain the part numbers and
associated part data fields like t nomenclatures, etc
3. Does PRIMARY KEY (…) mean you somehow combine the both fields? Is thi
s
the actual relationship, for example, the join from Process_id to Part_nbr ?
4. SQL statement: what’s p1, c1, etc. and/or what’s p1.Part_nbr, c1.cod
e, etc
I think I’m going to have to create it this before I fully understand it.
However, I’m not sure the Visual Studio (VS) Query Builder inside the VS
environment will allow me to create such an Access SQL statement as
described. The DataAdapter might just expand the SQL statement again in the
DataSet so, I might be right back to the beginning again. I can’t use
external Access queries for SELECT, UPDATE, DELETE etc. Please keep the
post open. Thank You.
Steve
"Anith Sen" wrote:

> You have a poorly designed table. Any shuffling you do without a thorough
> analysis of your business model and logic rules, might still leave the
> schema unmaintainable and make the queries complex.
> Instead of splitting up a single set of attribute into groups of {1st set
of
> cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the tab
le
> better. Understand and analyze the entities in question and identify each
of
> the attributes explicitly. Identify the dependencies among these attribute
s
> and consider decomposing them into multiple tables appropriately, if
> required.
> Even, without considering any ill-effects of under normalized schemas, wit
h
> certain assumptions based on the query you posted, you might benefit from
> having your table structured like:
> CREATE TABLE Parts (
> Part_nbr INT NOT NULL PRIMARY KEY,
> ... ) ;
> CREATE TABLE Characteristics (
> Character_id INT NOT NULL ,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Code ...
> Characteristic...
> PRIMARY KEY ( Character_id, Part_nbr ) );
> CREATE TABLE Processes (
> Process_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Process_code ...
> PRIMARY KEY ( Process_id, Part_nbr ) );
> CREATE TABLE Locations (
> Location_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Location_code ...
> PRIMARY KEY ( Location_id, Part_nbr );
> Now the query is as simple as:
> SELECT p1.Part_nbr,...
> c1.code, ...
> p2.process_code,...
> l1.location_code...
> FROM Parts p1
> INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
> INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
> INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
> ...
> This could be your comparable resultset which you might consider using for
a
> variety of purposes. For the specific case you mentioned in your post, you
> can simply pivot this data, either using your client programming language
or
> if done within the server, using the popular pivoting technique detailed i
n
> MSKB ( support.microsoft.com ) : 175574
> --
> Anith
>
>

Exporting to Excel from Preview Tab

I designed a report and from Preview tab, i exported it to Excel format and saved. I sent the Xls file to others, but when they opened it, it is displaying the Metadata info of the file. any idea what is going on?

In my report i have a table, and above it i have a bunch of textboxes which serves as a heading for groups of columns in the table. when i preview it is fine. But Exported to excel, some of these textboxes are wrapping to the next line. any idea what is going on? (I tried aligning all the textboxes)

thanks.

Which version / release of the product are you using? The initial release of RS 2000 did not support older versions of Excel. You can install the second service pack or move to RS 2005.

As for the export, make sure that you are putting the textboxes inside the table header, not as textboxes above the table. This will help you align columns.

Exporting to Excel from Preview Tab

I designed a report and from Preview tab, i exported it to Excel format and
saved. I sent the Xls file to others, but when they opened it, it is
displaying the Metadata info of the file. any idea what is going on?
In my report i have a table, and above it i have a bunch of textboxes which
serves as a heading for groups of columns in the table. when i preview it is
fine. But Exported to excel, some of these textboxes are wrapping to the next
line. any idea what is going on? (I tried aligning all the textboxes)
thanks.Don't know about the second issue but the first issue sounds like you have
not installed either SP1 or SP2. RS initially did not support Excel 2000 or
Excel 97. Starting with SP1 they added support for those version. My guess
is the users you sent the file to are on Excel 2000 or earlier.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Giya" <Giya@.discussions.microsoft.com> wrote in message
news:8B2D6E73-9824-40CC-90DB-5BAEFEFCEA82@.microsoft.com...
>I designed a report and from Preview tab, i exported it to Excel format and
> saved. I sent the Xls file to others, but when they opened it, it is
> displaying the Metadata info of the file. any idea what is going on?
> In my report i have a table, and above it i have a bunch of textboxes
> which
> serves as a heading for groups of columns in the table. when i preview it
> is
> fine. But Exported to excel, some of these textboxes are wrapping to the
> next
> line. any idea what is going on? (I tried aligning all the textboxes)
> thanks.|||thanks.
"Bruce L-C [MVP]" wrote:
> Don't know about the second issue but the first issue sounds like you have
> not installed either SP1 or SP2. RS initially did not support Excel 2000 or
> Excel 97. Starting with SP1 they added support for those version. My guess
> is the users you sent the file to are on Excel 2000 or earlier.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Giya" <Giya@.discussions.microsoft.com> wrote in message
> news:8B2D6E73-9824-40CC-90DB-5BAEFEFCEA82@.microsoft.com...
> >I designed a report and from Preview tab, i exported it to Excel format and
> > saved. I sent the Xls file to others, but when they opened it, it is
> > displaying the Metadata info of the file. any idea what is going on?
> >
> > In my report i have a table, and above it i have a bunch of textboxes
> > which
> > serves as a heading for groups of columns in the table. when i preview it
> > is
> > fine. But Exported to excel, some of these textboxes are wrapping to the
> > next
> > line. any idea what is going on? (I tried aligning all the textboxes)
> >
> > thanks.
>
>

Exporting to Excel

We've noticed that reports exported to excel are much larger than they need to be. Example, report generated and exported (and saved in Excel) shows size of 14,111 KB. However, if same file is saved (using SaveAs in Excel) file is only 4,762KB. Any idea as to why there would be such a size difference? Does RS pass some overhead when exporting to Excel? We are hoping to eliminate any overhead since the time cost during export is rising.

(there are no images being passed, just ~33,000 rows of data)

Thanks.

What method are you using to create the excel files?

|||

We have an ASP front-end that handles some security (report access, restricting what data a user can pass in via parameters) and makes the actual call to the report server for the given report. For most reports, the report parameter is passed to force the report to be exported to Excel (instead of rendering in IE).

So, I run the report, Excel launches and displays the data, and I simply do File/SaveAs... (and rename the file)

Here are some tests I ran against other reports that auto-export to Excel:

ReportA

Test1

Original Spreadsheet: 852k

SaveAs with no changes: 390k

Test2

Original Spreadsheet: 99k

SaveAs with no changes: 52k

Test3

Original Spreadsheet: 365k

SaveAs with no changes: 169k

ReportB

Test1

Original Spreadsheet: 245k

SaveAs with no changes: 104k

Test2

Original Spreadsheet: 365k

SaveAs with no changes: 169k

ReportC

Test1

Original Spreadsheet: 35k

SaveAs with no changes: 34k

Other tests revealed similar results

ReportD

Test1

Original Spreadsheet: 53k

SaveAs with no changes: 49k

Other tests revealed similar results

ReportE

Test1

Original Spreadsheet: 432k

SaveAs with no changes: 293k

Test2

Original Spreadsheet: 14k

SaveAs with no changes: 17k

Test3

Original Spreadsheet: 15k

SaveAs with no changes: 18k

Test4

Original Spreadsheet: 42k

SaveAs with no changes: 38k

Test5

Original Spreadsheet: 51k

SaveAs with no changes: 44k

I find it interesting that the larger the report output size, the greater the difference in sizes when resaved. Also, for very small file sizes, resaved size is actually bigger. Any ideas?

|||

I did some additional research - looks like this is just how Report Server interacts with MS Excel. Report Server cannot take advantage of Excel's file compression process(es) when it exports. Work arounds include exporting first to CSV and then opening the file in Excel OR scheduling the report so that the users don't have to wait. (Another suggestion is to wait for SQL Server 2008 and hope the issue is addressed there.)

I will mark this issue as resolved, but if you have any additional information, please post it here. Thanks.