Problem in getting data from a Table
-
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
-
How do you know there are 113,896 records in that table?
What query gives you this number?
_____________
Code for TallyGenerator -
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.
-
My Enterprise Manager does not show record count.
-
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.
-
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
-
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?
-
Do you see
SET ROWCOUNT = 5000
in your SP?
-
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.
-
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.
-
...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? -
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply