Records appearing and then disappearing

Hi,

We have a SQL server db backend for our ERP system. I have written a
Crystal Report to extract data from one particular table called
itemspecbomw (c.30000 records) which contains amongst other things
Bill of Materials costings. When I run the report I know that some
rows are missing as when I look at values through the ERP system
itself, the values are different.

What I have found is that when I run the equivalent ERP system report,
the Crystal Report I have written shows extra rows. SQL Query Analyser
behaves exactly the same as the report I have written even when using
a "select * from". I have tested opening up a bigger tables (c. 700000
records) which appears without a problem. If anyone knows why this
mystery is happening, I woould be grateful for your help.

Many thanks,

Tony.tkennedy@.blenders.ie (Tony Kennedy) wrote in message news:<f25d55e1.0402170538.4d2c54f4@.posting.google.com>...
> Hi,
> We have a SQL server db backend for our ERP system. I have written a
> Crystal Report to extract data from one particular table called
> itemspecbomw (c.30000 records) which contains amongst other things
> Bill of Materials costings. When I run the report I know that some
> rows are missing as when I look at values through the ERP system
> itself, the values are different.
> What I have found is that when I run the equivalent ERP system report,
> the Crystal Report I have written shows extra rows. SQL Query Analyser
> behaves exactly the same as the report I have written even when using
> a "select * from". I have tested opening up a bigger tables (c. 700000
> records) which appears without a problem. If anyone knows why this
> mystery is happening, I woould be grateful for your help.
> Many thanks,
> Tony.

I don't completely follow your explanation, but you seem to be saying
that QA and Crystal give the same results, but your ERP report gives
different ones? If two tools are giving different results, then the
most likely explanation is that they are submitting different queries.
I would suggest using Profiler to trace the SQL sent to the server by
your ERP application, and then run the same SQL in QA - you may find
that it is constructing a query which is different from what you
expect.

Simon|||Hi Simon,

I checked out the query used in the system ERP report, but I found
when I cut'n pasted the query into query analyser and ran it, it
returned the exact same number of records as the one I wrote. It seems
to be a problem with the way I access the db or that the ERP program
has some sort of lock on the table...

Thanks for your advice,

Tony.

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0402171042.2caa5e1c@.posting.google.com>...
> tkennedy@.blenders.ie (Tony Kennedy) wrote in message news:<f25d55e1.0402170538.4d2c54f4@.posting.google.com>...
> > Hi,
> > We have a SQL server db backend for our ERP system. I have written a
> > Crystal Report to extract data from one particular table called
> > itemspecbomw (c.30000 records) which contains amongst other things
> > Bill of Materials costings. When I run the report I know that some
> > rows are missing as when I look at values through the ERP system
> > itself, the values are different.
> > What I have found is that when I run the equivalent ERP system report,
> > the Crystal Report I have written shows extra rows. SQL Query Analyser
> > behaves exactly the same as the report I have written even when using
> > a "select * from". I have tested opening up a bigger tables (c. 700000
> > records) which appears without a problem. If anyone knows why this
> > mystery is happening, I woould be grateful for your help.
> > Many thanks,
> > Tony.
> I don't completely follow your explanation, but you seem to be saying
> that QA and Crystal give the same results, but your ERP report gives
> different ones? If two tools are giving different results, then the
> most likely explanation is that they are submitting different queries.
> I would suggest using Profiler to trace the SQL sent to the server by
> your ERP application, and then run the same SQL in QA - you may find
> that it is constructing a query which is different from what you
> expect.
> Simon