"too many fields defined" error when previewing xls

Anyone know why I am getting this?

===================================

There was an error displaying the preview. (Microsoft Visual Studio)

===================================

Too many fields defined. (Microsoft JET Database Engine)

Program Location:

at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.ExecuteStatement(Int32 resultType, Boolean isStoredProc, UInt32 dwTimeOut)
at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection90 externalColumns)
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)

A wild stab in the dark: You have to many fields? How many do you have? A clue would help determine if it is a problem of indeed a limitation..|||31 columns on the sheet that I am importing from.|||Obviously 31 is not to many. You have 31 columns with data in. Do you perhaps have any columns with a space or some other text in it right at the end of the document. Like at column ZZ?

I can create a sheet with 32 columns in it and it is fine. Adding spaces to columns after that causes the same error.

I suggest copying the columns you want out, pasting them in a new sheet and try that.|||the sheet has autofilter set on, could that be the cause?|||I don't know. Remove it and see.

"string data right truncation"

Hello everyone,

I'm using CRecordset in order to write into the SQL Server, i have a problem that

everytime i'm trying to write a varchar fields with size X and i put in the CString larger

string then X i get CDBException.

My question is if there is anyway to to tell the CRecordset to truncate automatialy the strings

instead of doing it manually for every record i write.

Thanks

check this

create table #tt(a varchar(5))

insert #tt select '1234567' --fails

insert #tt select left('1234567',5) -- succeeds

insert #tt select substring('1234567',1,5) -- succeeds

Madhu

|||

10x for the answer, but as far as i know this solution can't help me whilst using CRecordset

This is an example code(c++) of how i use the recordset:

---

class CJREventsArchiveRec : public CRecordset {

....

CString m_Bla;

...

}

The table in this example include a single nvarchar field(Bla) with size 5;

m_eventRec.AddNew();

m_Bla = "123456";

m_eventRec.Update(); // This row cause CDBException (string data right truncation).

---

of course in this sample its easy to manually avoid this problem,but its no that easy at my app.

The Solution i'm looking for is somehow to tell the Recordset to automatically truncate the string as much as he need, and stop throwing on me Exceptions

|||

That would mean silent data loss and I don't think the ODBC driver would silently allow data loss. Note that on the way out, it might truncate silently but when data is going to the server, it won't.

Thanks

Waseem

"Parameters" approach to fill report header with source data doesn't work

It's well known issue, that one can't use any dataset fields in a

report header/footer directly. One of the approach is to create

query-based parameter that basically equals

=First(Fields!@.FieldName@..Value, "@.DataSetName@.") and use that

parameter value instead. But it doesn't work in my case!

My report displays some entity description and is parametrized with

EntityID param. Its header contains entity name that, according to the

approach, is queried from the data source through the EntityName

report parameter. There's important issue: the report is displayed in

ReportViewer control, that is embedded into my application and entity

ID parameter isn't ser by user in ReportViewer parameters area. Its

default value is changed by the application with SetReportParameters()

web method every time a user wants to view the report according to the

entity the user is exploring in the application. But after the report

has been rendered, its header always contains not actual (outdated)

entity name. Nevertheless, the report body contains actual data

(including entity name). If I alter entity ID parameter in ReportViewer

or in web-based Report Manager and refresh report, header displays

correct entity name.

What's wrong in the workflow described?Up! SSRS can't deal with data in hearders, can it?|||

There is a way:

You can make the header fields refere to databound fields which are location within the body of the reports- to get your results ie You can put a hidden text field in the body and reference that field!

|||

Hi Viral,

If we put the hidden textbox in the body and use those textbox value it works perfectly here the problem is if our result in 5 pages when we export this result to PDF , the problem here is for the first page only the textbox value in the header is displaying from next page onwards it is displaying null values eventhough we made the property of textbox "Repeat With" :table1(result set) set.

Thanks

#Error in total fields

I am getting a #error in my subtotal and total fields,
I have data in a field (on a table) as follows which displays 0.00 if there is a zero in the divide by field, otherwise it performs the divide and gives me a percentage difference from the two fields.
This works fine

=iif(Fields!REPCYINC.Value=0,0,(Fields!REPCYINC.Value) / iif(Fields!REPCYPROD.Value=0,1,Fields!REPCYPROD.Value)) * 100

Yet when i try to get this in a total field by doing an AVG it fails, and gives #error, please help, no matter how many iifs a wrap aroun dit it doesnt help.

This is the statement that #error's

=round(avg(iif(Fields!REPCYINC.Value=0,0,(Fields!REPCYINC.Value) / iif(Fields!REPCYPROD.Value=0,1,Fields!REPCYPROD.Value)) * 100) ,2)

Brian Weckler's blog has a posting related to this.

http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

cheers,

Andrew

|||

Andy,

I had a similar issue recently when I used this statement:

Format(Sum(IIf(Fields!Product.Value="HEALTH",Fields!ID.Value,0)),"C")

Report Service kept giving me the #error, I located the detail error message and then I change my statement to this

Format(Sum(IIf(Fields!Product.Value="HEALTH",Fields!ID.Value,nothing)),"C") and everything worked okay. I remember the error message about mixing different types or something.

I hope this works for you.

Ham

|||

Unfortunately this post just explains what i have already posted, the issue is not the divide by zero, I have iif's to work around that and my inline divide give 0 result not #error,

I experience the #error in the group totals when i sum (As described in my original post)

If the main iif works and gives 0 then why not the sum?

These things should be straightforward!!

|||

That doesnt help, the inline iif *, 0, 0 works fine, I did try changing it to nothing although i still get the same issue, the group totals when i do a sum doesnt work, it #error's!!!

|||

sorted, regardless of how many iif's you throw around the numbers within the SSRS you MUST format the field as number otherwise #error occurs, seems like Microsft still have some work to do here.....

At LEAST make some kind of error handling reporting so we know where the error is coming from, it could be anything....

#error in table fields

Hello NG,
i have a table with some data from a dataset.
Some fields are calculationresults from other fields.
Now there is some fields which has the conent "#error".
Can i catch this? I want to have an empty field, when "#error" occurs.
Thanks!Sure, you can probably put some check in an IIF() to make sure that you get
this result. But what check or test you have to put there will depend on
the type of error you are getting -- what do your expressions look like?
>L<
"Petra Grumbach" <grumbach@.ib-guenther.de> wrote in message
news:%23cVp0B5mHHA.3704@.TK2MSFTNGP02.phx.gbl...
> Hello NG,
> i have a table with some data from a dataset.
> Some fields are calculationresults from other fields.
> Now there is some fields which has the conent "#error".
> Can i catch this? I want to have an empty field, when "#error" occurs.
> Thanks!
>

#error deploying a report.

Hello,
I created a report, where one of the fields is using an embedded code:
strReturn = System.Web.HttpUtility.UrlDecode(strURL). I added a
reference to System.Web.dll and it works fine in the RS designer. But
when I deploy it to the report server it shows #Error in the field.
>From the report server config file it seems that all Microsoft signed
assemblies have full trust. I checked the versions and public token
keys of the referenced assembly on my machine and one on the server
where the report server is and they match. What can be wrong?
Thanks,
SteveIt looks like code access permissions problem.
Your expression may call some function that requires special CAS
persmission.
It does not look like System.Web.HttpUtility.UrlDecode() has the
requirement. Do you also call some other function?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<sgelfmann@.yahoo.com> wrote in message
news:1116944339.636538.109880@.z14g2000cwz.googlegroups.com...
> Hello,
> I created a report, where one of the fields is using an embedded code:
> strReturn = System.Web.HttpUtility.UrlDecode(strURL). I added a
> reference to System.Web.dll and it works fine in the RS designer. But
> when I deploy it to the report server it shows #Error in the field.
> >From the report server config file it seems that all Microsoft signed
> assemblies have full trust. I checked the versions and public token
> keys of the referenced assembly on my machine and one on the server
> where the report server is and they match. What can be wrong?
> Thanks,
> Steve
>|||More specifically, you will find the AutoRefresh property in the VS menu:
Report -> Report Properties -> General tab
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alexandre Mineev" <almineev@.microsoft.com> wrote in message
news:Ozx4JdYYFHA.1868@.TK2MSFTNGP14.phx.gbl...
> It looks like code access permissions problem.
> Your expression may call some function that requires special CAS
> persmission.
> It does not look like System.Web.HttpUtility.UrlDecode() has the
> requirement. Do you also call some other function?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> <sgelfmann@.yahoo.com> wrote in message
> news:1116944339.636538.109880@.z14g2000cwz.googlegroups.com...
>> Hello,
>> I created a report, where one of the fields is using an embedded code:
>> strReturn = System.Web.HttpUtility.UrlDecode(strURL). I added a
>> reference to System.Web.dll and it works fine in the RS designer. But
>> when I deploy it to the report server it shows #Error in the field.
>> >From the report server config file it seems that all Microsoft signed
>> assemblies have full trust. I checked the versions and public token
>> keys of the referenced assembly on my machine and one on the server
>> where the report server is and they match. What can be wrong?
>> Thanks,
>> Steve
>

#DELETED in Linked SQL

Hi,
Running Access 2002 frontend, with SQL2000 backend. When I link the
tables, 3 tables appear in Access with all fields loaded with #DELETED.
The data is fine in SQL. I can *import* them back into Access out of
SQL and they are OK, but cannot link. (This data is originally being
imported into SQL from Access).
At one point, I re-created the tables using a make table, then imported
that and it was OK. But on subsequent imports (while cleaning the
data), it has gone back to #DELETED again.
These tables are large, all over 250,000 records, but surely that's not
a problem for SQL.
Any ideas?
Eric
Eric,
It's not a SQL Server issue...you can run into that when
linking tables to other data sources as well. When it's an
entire table, It can be caused by several things such as
using a float as the index or as part of the index or
having nulls as values in part of the index. There was in
issue similar to this when using Bigints with some data
sources as well which wouldn't map correctly but was
corrected in one of the Jet service packs.
ODBC is key-set driven and fetches are generally done in two
steps based upon the unique index of the table where first
it grabs the index and then it goes back, looks for the
index and gets the rest of the row based on the index. If
it can't find the index or gets 'confused' on the index in
the second step, it will assume the record has been deleted.
So it could be a few different things. Make sure you are
using the latest Jet service packs and check what is being
used for indexes. There used to be some info on the issue in
the Access help file but I wouldn't have any idea where to
find it in there since they Answer Wizarded the help files
and made things harder to find. I usually just do a google
search with microsoft.com as the domain to find help
articles on office issues. Other than that, you may want to
post to one of the Access newsgroups.
-Sue
On Tue, 15 Feb 2005 12:09:32 -0500, elf
<eric@.northstarcc.com> wrote:

>Hi,
>Running Access 2002 frontend, with SQL2000 backend. When I link the
>tables, 3 tables appear in Access with all fields loaded with #DELETED.
> The data is fine in SQL. I can *import* them back into Access out of
>SQL and they are OK, but cannot link. (This data is originally being
>imported into SQL from Access).
>At one point, I re-created the tables using a make table, then imported
>that and it was OK. But on subsequent imports (while cleaning the
>data), it has gone back to #DELETED again.
>These tables are large, all over 250,000 records, but surely that's not
>a problem for SQL.
>Any ideas?
>Eric
|||Thanks. I *am* using bigint in the identity fields of these tables,
because I've had problems in the past using just int with imported
autonumbers. (We've been upgrading several clients, some of whom were
using Access Replication (ugh!...at least prior to 2000), which can
generate some really big autonumbers) I've been careful about nulls in
keys, so bigint is where I'll look first.
BTW, we have developed in Access for years, only fairly recently started
using SQL as a backend. Access is not quite as picky about prime keys,
so when SQL complains about our Access prime, we just move it to an
alternate, and use an identity as prime.
Sue Hoegemeier wrote:

> Eric,
> It's not a SQL Server issue...you can run into that when
> linking tables to other data sources as well. When it's an
> entire table, It can be caused by several things such as
> using a float as the index or as part of the index or
> having nulls as values in part of the index. There was in
> issue similar to this when using Bigints with some data
> sources as well which wouldn't map correctly but was
> corrected in one of the Jet service packs.
> ODBC is key-set driven and fetches are generally done in two
> steps based upon the unique index of the table where first
> it grabs the index and then it goes back, looks for the
> index and gets the rest of the row based on the index. If
> it can't find the index or gets 'confused' on the index in
> the second step, it will assume the record has been deleted.
> So it could be a few different things. Make sure you are
> using the latest Jet service packs and check what is being
> used for indexes. There used to be some info on the issue in
> the Access help file but I wouldn't have any idea where to
> find it in there since they Answer Wizarded the help files
> and made things harder to find. I usually just do a google
> search with microsoft.com as the domain to find help
> articles on office issues. Other than that, you may want to
> post to one of the Access newsgroups.
> -Sue
> On Tue, 15 Feb 2005 12:09:32 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>
|||Your welcome and yeah...I'd look at the bigint first.
You will find that Access is not as strict about some things
compared to SQL Server. It's just the nature of things and
happens with all different database vendors. Even SQL Server
has some things it lets you get away with that really isn't
allowed by standards. And every vendor has their own
extensions of SQL which further complicates things a bit.
-Sue
On Tue, 15 Feb 2005 14:28:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks. I *am* using bigint in the identity fields of these tables,
>because I've had problems in the past using just int with imported
>autonumbers. (We've been upgrading several clients, some of whom were
>using Access Replication (ugh!...at least prior to 2000), which can
>generate some really big autonumbers) I've been careful about nulls in
>keys, so bigint is where I'll look first.
>BTW, we have developed in Access for years, only fairly recently started
>using SQL as a backend. Access is not quite as picky about prime keys,
>so when SQL complains about our Access prime, we just move it to an
>alternate, and use an identity as prime.
>
>Sue Hoegemeier wrote:
|||FYI, everyone. Changing the Bigint to int solved my problem immediately.
This may cause me some problems when I go to upsize my next client, who
was using replication id's...guess I may just have to re-number the
lookup tables and their associated tables.
That's the life, I guess.
Thanks, Sue.
Eric
Sue Hoegemeier wrote:
> Your welcome and yeah...I'd look at the bigint first.
> You will find that Access is not as strict about some things
> compared to SQL Server. It's just the nature of things and
> happens with all different database vendors. Even SQL Server
> has some things it lets you get away with that really isn't
> allowed by standards. And every vendor has their own
> extensions of SQL which further complicates things a bit.
> -Sue
> On Tue, 15 Feb 2005 14:28:42 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>
|||Glad to hear it's resolved...thanks for posting back Eric.
Another thing...I'd work at testing with the latest Jet
service pack. As I posted earlier, there were issues with
mapping Bigint data types that were corrected in one of the
Jet service packs. If you can resolve that and still use
Bigints then you don't have to worry about the next client
and how to address the issue.
-Sue
On Tue, 15 Feb 2005 21:39:19 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>FYI, everyone. Changing the Bigint to int solved my problem immediately.
>This may cause me some problems when I go to upsize my next client, who
>was using replication id's...guess I may just have to re-number the
>lookup tables and their associated tables.
>That's the life, I guess.
>Thanks, Sue.
>Eric
>Sue Hoegemeier wrote:
|||Yeah, I'm going to look into that, Sue. My immediate concern was to get
the conversion done and do my testing before my (tight) deadline. It
had already cost me a couple days of bloody forehead<g>.
Eric
Sue Hoegemeier wrote:
> Glad to hear it's resolved...thanks for posting back Eric.
> Another thing...I'd work at testing with the latest Jet
> service pack. As I posted earlier, there were issues with
> mapping Bigint data types that were corrected in one of the
> Jet service packs. If you can resolve that and still use
> Bigints then you don't have to worry about the next client
> and how to address the issue.
> -Sue
> On Tue, 15 Feb 2005 21:39:19 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>

#DELETED in Linked SQL

Hi,
Running Access 2002 frontend, with SQL2000 backend. When I link the
tables, 3 tables appear in Access with all fields loaded with #DELETED.
The data is fine in SQL. I can *import* them back into Access out of
SQL and they are OK, but cannot link. (This data is originally being
imported into SQL from Access).
At one point, I re-created the tables using a make table, then imported
that and it was OK. But on subsequent imports (while cleaning the
data), it has gone back to #DELETED again.
These tables are large, all over 250,000 records, but surely that's not
a problem for SQL.
Any ideas?
EricEric,
It's not a SQL Server issue...you can run into that when
linking tables to other data sources as well. When it's an
entire table, It can be caused by several things such as
using a float as the index or as part of the index or
having nulls as values in part of the index. There was in
issue similar to this when using Bigints with some data
sources as well which wouldn't map correctly but was
corrected in one of the Jet service packs.
ODBC is key-set driven and fetches are generally done in two
steps based upon the unique index of the table where first
it grabs the index and then it goes back, looks for the
index and gets the rest of the row based on the index. If
it can't find the index or gets 'confused' on the index in
the second step, it will assume the record has been deleted.
So it could be a few different things. Make sure you are
using the latest Jet service packs and check what is being
used for indexes. There used to be some info on the issue in
the Access help file but I wouldn't have any idea where to
find it in there since they Answer Wizarded the help files
and made things harder to find. I usually just do a google
search with microsoft.com as the domain to find help
articles on office issues. Other than that, you may want to
post to one of the Access newsgroups.
-Sue
On Tue, 15 Feb 2005 12:09:32 -0500, elf
<eric@.northstarcc.com> wrote:

>Hi,
>Running Access 2002 frontend, with SQL2000 backend. When I link the
>tables, 3 tables appear in Access with all fields loaded with #DELETED.
> The data is fine in SQL. I can *import* them back into Access out of
>SQL and they are OK, but cannot link. (This data is originally being
>imported into SQL from Access).
>At one point, I re-created the tables using a make table, then imported
>that and it was OK. But on subsequent imports (while cleaning the
>data), it has gone back to #DELETED again.
>These tables are large, all over 250,000 records, but surely that's not
>a problem for SQL.
>Any ideas?
>Eric|||Thanks. I *am* using bigint in the identity fields of these tables,
because I've had problems in the past using just int with imported
autonumbers. (We've been upgrading several clients, some of whom were
using Access Replication (ugh!...at least prior to 2000), which can
generate some really big autonumbers) I've been careful about nulls in
keys, so bigint is where I'll look first.
BTW, we have developed in Access for years, only fairly recently started
using SQL as a backend. Access is not quite as picky about prime keys,
so when SQL complains about our Access prime, we just move it to an
alternate, and use an identity as prime.
Sue Hoegemeier wrote:

> Eric,
> It's not a SQL Server issue...you can run into that when
> linking tables to other data sources as well. When it's an
> entire table, It can be caused by several things such as
> using a float as the index or as part of the index or
> having nulls as values in part of the index. There was in
> issue similar to this when using Bigints with some data
> sources as well which wouldn't map correctly but was
> corrected in one of the Jet service packs.
> ODBC is key-set driven and fetches are generally done in two
> steps based upon the unique index of the table where first
> it grabs the index and then it goes back, looks for the
> index and gets the rest of the row based on the index. If
> it can't find the index or gets 'confused' on the index in
> the second step, it will assume the record has been deleted.
> So it could be a few different things. Make sure you are
> using the latest Jet service packs and check what is being
> used for indexes. There used to be some info on the issue in
> the Access help file but I wouldn't have any idea where to
> find it in there since they Answer Wizarded the help files
> and made things harder to find. I usually just do a google
> search with microsoft.com as the domain to find help
> articles on office issues. Other than that, you may want to
> post to one of the Access newsgroups.
> -Sue
> On Tue, 15 Feb 2005 12:09:32 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>|||Your welcome and yeah...I'd look at the bigint first.
You will find that Access is not as strict about some things
compared to SQL Server. It's just the nature of things and
happens with all different database vendors. Even SQL Server
has some things it lets you get away with that really isn't
allowed by standards. And every vendor has their own
extensions of SQL which further complicates things a bit.
-Sue
On Tue, 15 Feb 2005 14:28:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks. I *am* using bigint in the identity fields of these tables,
>because I've had problems in the past using just int with imported
>autonumbers. (We've been upgrading several clients, some of whom were
>using Access Replication (ugh!...at least prior to 2000), which can
>generate some really big autonumbers) I've been careful about nulls in
>keys, so bigint is where I'll look first.
>BTW, we have developed in Access for years, only fairly recently started
>using SQL as a backend. Access is not quite as picky about prime keys,
>so when SQL complains about our Access prime, we just move it to an
>alternate, and use an identity as prime.
>
>Sue Hoegemeier wrote:
>|||FYI, everyone. Changing the Bigint to int solved my problem immediately.
This may cause me some problems when I go to upsize my next client, who
was using replication id's...guess I may just have to re-number the
lookup tables and their associated tables.
That's the life, I guess.
Thanks, Sue.
Eric
Sue Hoegemeier wrote:
> Your welcome and yeah...I'd look at the bigint first.
> You will find that Access is not as strict about some things
> compared to SQL Server. It's just the nature of things and
> happens with all different database vendors. Even SQL Server
> has some things it lets you get away with that really isn't
> allowed by standards. And every vendor has their own
> extensions of SQL which further complicates things a bit.
> -Sue
> On Tue, 15 Feb 2005 14:28:42 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>|||Glad to hear it's resolved...thanks for posting back Eric.
Another thing...I'd work at testing with the latest Jet
service pack. As I posted earlier, there were issues with
mapping Bigint data types that were corrected in one of the
Jet service packs. If you can resolve that and still use
Bigints then you don't have to worry about the next client
and how to address the issue.
-Sue
On Tue, 15 Feb 2005 21:39:19 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>FYI, everyone. Changing the Bigint to int solved my problem immediately.
>This may cause me some problems when I go to upsize my next client, who
>was using replication id's...guess I may just have to re-number the
>lookup tables and their associated tables.
>That's the life, I guess.
>Thanks, Sue.
>Eric
>Sue Hoegemeier wrote:|||Yeah, I'm going to look into that, Sue. My immediate concern was to get
the conversion done and do my testing before my (tight) deadline. It
had already cost me a couple days of bloody forehead<g>.
Eric
Sue Hoegemeier wrote:
> Glad to hear it's resolved...thanks for posting back Eric.
> Another thing...I'd work at testing with the latest Jet
> service pack. As I posted earlier, there were issues with
> mapping Bigint data types that were corrected in one of the
> Jet service packs. If you can resolve that and still use
> Bigints then you don't have to worry about the next client
> and how to address the issue.
> -Sue
> On Tue, 15 Feb 2005 21:39:19 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>