Enterprise Manager "Return all rows" problem

Question

Post reply

Enterprise Manager "Return all rows" problem

JFW

Mr or Mrs. 500

Points: 546
More actions
September 17, 2004 at 11:37 am

#59328

We are running SQL Server 2000, SP3, Standard Edition. From Enterprise Manager, when I right mouse click on the table PS_SCHOOLS_TBL, select “open table”, “return all rows”, no records are returned. If I click the SQL button, I see why. The name in the select statement has changed to PSYSCHOOLS_TBL. However, if I select “open table”, “query”, the correct table is shown and records return properly.

Both tables are in the database, and seem to behave normally otherwise. Query Analyzer displays and runs them properly.

I have run “update statistics” on each table. Does anybody have any suggestions about what might be wrong? Should I bother recreating the table?

Thanks,
JFW

Viewing 5 posts - 1 through 4 (of 4 total)

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

Carl Farmer SSCrazy Points: 2197 More actions · Answer 1

Out of curiousity, have a look to see what it says in sysobjects for that particular table.....

JFW Mr or Mrs. 500 Points: 546 More actions · Answer 2

I hope this aligns ok:

name	PSYSCHOOL_TBL	PS_SCHOOL_TBL
id	261680080	293680194
xtype	U	U
uid	1	1
info	8	8
status	1610612736	1610612737
base_schema_ver	0	18
replinfo	0	0
parent_obj	0	0
crdate	9/9/2004	9/9/2004
	50:40.2	55:45.9
ftcatid	0	0
schema_ver	0	16
stats_schema_ver	0	2
type	U	U
userstat	1	1
sysstat	67	83
indexdel	0	0
refdate	9/9/2004	9/9/2004
	50:40.2	55:45.9
version	0	0
deltrig	0	0
instrig	0	0
updtrig	0	0
seltrig	0	0
category	0	0
cache	0	0

robrador Grasshopper Points: 10 More actions · Answer 3

I'm having a similar issue, but in my case we are trying to open the table's records via the Object Browser. 2 different tables, both return all rows, but only 1 let's you edit the records on the fly. I compared the difference with several tables and have concluded that the sysstat field's value, whether 67 or 83, is the reason. But one cannot simply update this value since it's a computed sql field... I believe it's values are written in binary.

Thought this might help you since your 2 records also show a difference in value there.

JFW Mr or Mrs. 500 Points: 546 More actions · Answer 4

Thanks for your reply robrador. I since have learned that our problem was the result of People Soft scripts that use the Y in PSY as a way to generate a temporary table name during its alter process. Apparently the script failed midstream, leaving the temporary file in some kind of unstable status. We solved our problem by deleting the unnecessary temporary file. We have not seen a recurrence of this problem.

JFW