Problem in getting data from a Table

Question

Post reply

Problem in getting data from a Table

Maria Carmen Vilbar

Ten Centuries

Points: 1037
More actions
September 4, 2007 at 11:20 pm

#118386

Hello,
I would like to know if you encounter already this kind of scenario:
I have table contains 113,896 records in our Database. When I execute a simple query towards the table it returns 5000 records only. I'm just confuse or something mad on this because when I execute my stored procedure getting data from this table it turns that the data that I needed for reports are not there. There was no where clause just get all the data. Is there a setting in table to get the data? or somewhat?
Hope that you'll help me on this.
Thanks.
Regards,
Carmen

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

How do you know there are 113,896 records in that table?

What query gives you this number?

_____________
Code for TallyGenerator

Maria Carmen Vilbar Ten Centuries Points: 1037 More actions · Answer 2

When I go to the Enterprise Manager and right click the table and select "open". It will display the total number of records in the table.

Thanks.

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 3

Koji Matsumura

SSCrazy

Points: 2241

September 5, 2007 at 12:25 am

#731395

My Enterprise Manager does not show record count.

Maria Carmen Vilbar Ten Centuries Points: 1037 More actions · Answer 4

Ok. Fine. But to make it detailed: It will display all the data and under the display area you will find the total number of records I think when the current cursor points to record 1 it display 1 of <total no of records>. Hope that you got it.

Thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The count in Enterprise Manager is only an estimate based on the statistics on your table. To get a more accurate figure, you should update statistics on your table; to get a precise figure use SELECT COUNT(*) FROM MyTable.

John

Maria Carmen Vilbar Ten Centuries Points: 1037 More actions · Answer 6

I already update statistics and then Select count(*) FROM mytable. Then the result is still the same 11,386. But when I get the whole 11,386 records by using select * from mytable it returns only 5,000 records. What's the problem?

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 7

Koji Matsumura

SSCrazy

Points: 2241

September 5, 2007 at 2:22 am

#731419

Do you see

SET ROWCOUNT = 5000

in your SP?

Maria Carmen Vilbar Ten Centuries Points: 1037 More actions · Answer 8

I think that's not fair to give that kind of reply...I need the whole records from the table...so why will I set the rowcount = 5000.

That's it.

Thanks a lot.

Vladan SSC-Insane Points: 22072 More actions · Answer 9

You misunderstood Koji. He was asking whether you have checked the procedure (or the query you tested) to make sure that it does NOT contain this command. In case you are getting precisely 5000 rows when executing the procedure/query, SET ROWCOUNT is quite likely to be the reason. If you are running the query in QA, also test whether the connection does not have ROWCOUNT set from some previous action you performed - do SELECT * against some table with more than 5000 rows. Or even better, run SET ROWCOUNT 0 and then the query. You have to remember that SET ROWCOUNT affects the connection, i.e. all queries you run from this QA window once you have set it.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

...or in the same way - if the query has something like a TOP statement (like, select TOP x ....) it could cause the same type of limit.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Vladan SSC-Insane Points: 22072 More actions · Answer 11

Oh, and another possibility. Check that the query/sp is working with the correct database. Just today my colleague was completely perplexed how it is possible that a view returns different data than the SQL copied out of the view and ran separately. In the end he found out that he was doing SELECT * FROM view against production database, and running the SQL against testing environment.