Error running report: records

Showing posts with label records. Show all posts

Monday, March 26, 2012

Error when RowNumber is 0

Or is it when the dataset is null? Either way, my report is reporting a
Javasript error when my stored procedure returns no records. I isolated the
expression that is causing the problem. It is:
=iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")
which I had in the table row Background property to alternate my background
color of the rows. It works fine, except when there are no records.
Can someone tell me how to fix it without removing the expression, which is
what I did to get around it for now. I would like to have the alternating row
colors back if I can. Basically I need to be able to determine that
RowNumber is 0 or that DataRecords, which is my dataset, is null.
BTW, the error did not show up while I was testing under Visual Studio 2005
Pro in Debug mode. Everything appeared fine until I deployed to the server.
If anyone knows how I can catch this type of problem BEFORE I deploy I would
like to know also.
TIA
URW
PS: I had accidentally posted this in the SQL Server Replication Group this
morning. I hope I have the right place (Reporting Services) this time. :-)I found the answer in another post, so the problem has been solved. I should
have searched first and I apologze for not doing that. I guess after I
realized I had posted to the wrong group earlier, I was a bit flustered
Thanks
"URW" wrote:
> Or is it when the dataset is null? Either way, my report is reporting a
> Javasript error when my stored procedure returns no records. I isolated the
> expression that is causing the problem. It is:
> =iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")
> which I had in the table row Background property to alternate my background
> color of the rows. It works fine, except when there are no records.
> Can someone tell me how to fix it without removing the expression, which is
> what I did to get around it for now. I would like to have the alternating row
> colors back if I can. Basically I need to be able to determine that
> RowNumber is 0 or that DataRecords, which is my dataset, is null.
> BTW, the error did not show up while I was testing under Visual Studio 2005
> Pro in Debug mode. Everything appeared fine until I deployed to the server.
> If anyone knows how I can catch this type of problem BEFORE I deploy I would
> like to know also.
> TIA
> URW
> PS: I had accidentally posted this in the SQL Server Replication Group this
> morning. I hope I have the right place (Reporting Services) this time. :-)|||Could you tell us the solution ;-)?
"URW" <URW@.discussions.microsoft.com> escribió en el mensaje
news:B817A72E-446F-43BF-9B7E-C0A4D2553C15@.microsoft.com...
>I found the answer in another post, so the problem has been solved. I
>should
> have searched first and I apologze for not doing that. I guess after I
> realized I had posted to the wrong group earlier, I was a bit flustered
> Thanks
> "URW" wrote:
>> Or is it when the dataset is null? Either way, my report is reporting a
>> Javasript error when my stored procedure returns no records. I isolated
>> the
>> expression that is causing the problem. It is:
>> =iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")
>> which I had in the table row Background property to alternate my
>> background
>> color of the rows. It works fine, except when there are no records.
>> Can someone tell me how to fix it without removing the expression, which
>> is
>> what I did to get around it for now. I would like to have the alternating
>> row
>> colors back if I can. Basically I need to be able to determine that
>> RowNumber is 0 or that DataRecords, which is my dataset, is null.
>> BTW, the error did not show up while I was testing under Visual Studio
>> 2005
>> Pro in Debug mode. Everything appeared fine until I deployed to the
>> server.
>> If anyone knows how I can catch this type of problem BEFORE I deploy I
>> would
>> like to know also.
>> TIA
>> URW
>> PS: I had accidentally posted this in the SQL Server Replication Group
>> this
>> morning. I hope I have the right place (Reporting Services) this time.
>> :-)|||Sure.
I just change the expression to
=iif( RowNumber(Nothing)Mod 2, "WhiteSmoke", "White")
I think by defaulting the scope of RowNumber, I am actually counting the
number of rows in the table, rather than the number of rows in the Dataset,
like I was before. Since the dataset can be null, I had problems when I tried
to get the row count for a null object. But the table is never null, and thus
the row count is always save to access and thus no crash. I am not sure if my
explanaition is correct, but that is how I could explain to myself why
getting the row number of a null object worked.
If you search for RowNumber in this group you will find several posts that
refer to this problem. They all show the same solution, but no explanaition
why it works. I hope mine helps but if not at least you have the answer for
my problem.
"MÃ³nica" wrote:
> Could you tell us the solution ;-)?
>
> "URW" <URW@.discussions.microsoft.com> escribiÃ³ en el mensaje
> news:B817A72E-446F-43BF-9B7E-C0A4D2553C15@.microsoft.com...
> >I found the answer in another post, so the problem has been solved. I
> >should
> > have searched first and I apologze for not doing that. I guess after I
> > realized I had posted to the wrong group earlier, I was a bit flustered
> >
> > Thanks
> >
> > "URW" wrote:
> >
> >> Or is it when the dataset is null? Either way, my report is reporting a
> >> Javasript error when my stored procedure returns no records. I isolated
> >> the
> >> expression that is causing the problem. It is:
> >>
> >> =iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")
> >>
> >> which I had in the table row Background property to alternate my
> >> background
> >> color of the rows. It works fine, except when there are no records.
> >>
> >> Can someone tell me how to fix it without removing the expression, which
> >> is
> >> what I did to get around it for now. I would like to have the alternating
> >> row
> >> colors back if I can. Basically I need to be able to determine that
> >> RowNumber is 0 or that DataRecords, which is my dataset, is null.
> >>
> >> BTW, the error did not show up while I was testing under Visual Studio
> >> 2005
> >> Pro in Debug mode. Everything appeared fine until I deployed to the
> >> server.
> >> If anyone knows how I can catch this type of problem BEFORE I deploy I
> >> would
> >> like to know also.
> >>
> >> TIA
> >>
> >> URW
> >>
> >> PS: I had accidentally posted this in the SQL Server Replication Group
> >> this
> >> morning. I hope I have the right place (Reporting Services) this time.
> >> :-)
>
>sql

Wednesday, March 21, 2012

Error when doing fuzzy lookup

I am trying to run a SSIS package that contains a fuzzy lookup. I am using a flat file with about 7 million records as the input. The reference table has about 2000 records. The package fails after about 40,000 records with the following information:

Warning: 0x8007000E at Data Flow Task, Fuzzy Lookup [228]: Not enough storage is available to complete this operation.
Warning: 0x800470E9 at Data Flow Task, DTS.Pipeline: A call to the ProcessInput method for input 229 on component "Fuzzy Lookup" (228) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Fuzzy Lookup" (228) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x8007000E.
Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

I have tried many things - changing the BufferTempStoragePath path to a drive that has plenty space, changed the MaxInsertCommitSize to 5,000...

What else can I do?

Thanks!

Did you get this on a RTM build?

If yes, is it possible you apply SP1 to see whether that helps?

Thanks

wenyang

|||

Yes, it is on a RTM build.

The server does not have SP1 on it yet. Is there something else that I could try before that is installed?

Thanks!

|||

I know there was a memory issue got fixed in SP1 for FuzzyLookup, in which case the error looks very similar to yours, so I wanted you to try SP1.

For temp work arounds, you may also try one of the following two on your RTM

1) Increase your page file to >10GB

2) Mitigate the problem by splitting the input into smaller batches (e.g. 100k at a time) and tearing down the package in between each batch.

HTH

wenyang

|||

I have installed SP1, and am getting the same errors.

Any other suggestions other than the temp work arounds you mentioned? As I mentioned earlier, the process is stopping after only 40,000 records or so. This seems like a very small number and I'd hate to have to split the input up into smaller pieces than that.

Thanks!

|||

Does increase page file size help? If not, then we probably need to repro in house to investigate further.

Is it possible you log a bug with repro? (bug report place is http://lab.msdn.microsoft.com/productfeedback/default.aspx) We'll get back to you asap.

Thanks

Wenyang

|||

Sorry - I was told that SP1 had been installed before I wrote my previous message. It actually hadn't been installed at that point. Once that was successfully installed on Integration Services, the fuzzy lookup issues seem to have been resolved.

Thanks!

|||

Hi Wenyang,

I have installed SQL Server 2005 Service Pack 2 and unfortunately this error does not seem to have gone away.

Increasing my pagefile does not seemed to have helped the situation either ?

It seems to occur whenever I choose to perform a match on more than 10 columns within a Fuzzy Lookup ?

Can you please advise what I should do next/when this will be fixed ?

Many Thanks,

Gary

Error when doing fuzzy lookup

I have tried many things - changing the BufferTempStoragePath path to a drive that has plenty space, changed the MaxInsertCommitSize to 5,000...

What else can I do?

Thanks!

Did you get this on a RTM build?

If yes, is it possible you apply SP1 to see whether that helps?

Thanks

wenyang

|||

Yes, it is on a RTM build.

The server does not have SP1 on it yet. Is there something else that I could try before that is installed?

Thanks!

|||

I know there was a memory issue got fixed in SP1 for FuzzyLookup, in which case the error looks very similar to yours, so I wanted you to try SP1.

For temp work arounds, you may also try one of the following two on your RTM

1) Increase your page file to >10GB

2) Mitigate the problem by splitting the input into smaller batches (e.g. 100k at a time) and tearing down the package in between each batch.

HTH

wenyang

|||

I have installed SP1, and am getting the same errors.

Thanks!

|||

Does increase page file size help? If not, then we probably need to repro in house to investigate further.

Is it possible you log a bug with repro? (bug report place is http://lab.msdn.microsoft.com/productfeedback/default.aspx) We'll get back to you asap.

Thanks

Wenyang

|||

Thanks!

|||

Hi Wenyang,

I have installed SQL Server 2005 Service Pack 2 and unfortunately this error does not seem to have gone away.

Increasing my pagefile does not seemed to have helped the situation either ?

It seems to occur whenever I choose to perform a match on more than 10 columns within a Fuzzy Lookup ?

Can you please advise what I should do next/when this will be fixed ?

Many Thanks,

Gary

Monday, March 12, 2012

Error when cast Varchar to decimal

I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > I'm writing a script with an insert statment to insert records from one
> > database to another but I have to change the type first. How can I find
> out
> > more about what can be causing this or is it something wrong with my
> syntax?
> > The field [patient_weight] is a varchar(256) stripped of all spaces left
> and
> > right.
> > Thanks, Alpha
> >
> > cast([patient_weight] as decimal(10,2))
> >
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
> > What result do you get here?
> >
> > SELECT COUNT(*) FROM [source table name]
> > WHERE ISNUMERIC(patient_weight) = 0
> >
> > --
> > Please post DDL, sample data and desired results.
> > See http://www.aspfaq.com/5006 for info.
> >
> >
> >
> >
> > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > I'm writing a script with an insert statment to insert records from
one
> > > database to another but I have to change the type first. How can I
find
> > out
> > > more about what can be causing this or is it something wrong with my
> > syntax?
> > > The field [patient_weight] is a varchar(256) stripped of all spaces
left
> > and
> > > right.
> > > Thanks, Alpha
> > >
> > > cast([patient_weight] as decimal(10,2))
> > >
> >
> >
> >|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> > I got 4. I looked at the table and they're all '0'(zero) except these 4.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > What result do you get here?
> > >
> > > SELECT COUNT(*) FROM [source table name]
> > > WHERE ISNUMERIC(patient_weight) = 0
> > >
> > > --
> > > Please post DDL, sample data and desired results.
> > > See http://www.aspfaq.com/5006 for info.
> > >
> > >
> > >
> > >
> > > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > > I'm writing a script with an insert statment to insert records from
> one
> > > > database to another but I have to change the type first. How can I
> find
> > > out
> > > > more about what can be causing this or is it something wrong with my
> > > syntax?
> > > > The field [patient_weight] is a varchar(256) stripped of all spaces
> left
> > > and
> > > > right.
> > > > Thanks, Alpha
> > > >
> > > > cast([patient_weight] as decimal(10,2))
> > > >
> > >
> > >
> > >
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> > Thank you that works but I don't understadn what happend here. So these 4
> > rows has a '0'(zero) char in it with CR. So how does this query able to
> > select it? ISNUMERIC cast it to 0 (zero in numerica) right?
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>

Monday, March 26, 2012

Error when RowNumber is 0

Error when RowNumber is 0

Wednesday, March 21, 2012

Error when doing fuzzy lookup

Error when doing fuzzy lookup

Monday, March 12, 2012

Error when cast Varchar to decimal

Error when cast Varchar to decimal

Error when cast Varchar to decimal

Error running report

Blog Archive

About Me