BUG:SQL Server 2000 Enterprise Manager????

Question

BUG:SQL Server 2000 Enterprise Manager????

John Dempsey

Hall of Fame

Points: 3225
More actions
April 18, 2006 at 1:42 pm

#114277

I am having a problem with SQL Server 2000 Enterprise Manager. I have a few tables that have similar names but Enterprise Manager is not handling them properly. The table names are FnlVldQTR_370Q1_A, FnlVldQTR_370Q1_D, FnlVldQTR_370Q10A, and FnlVldQTR_370Q12D.

I open up the Enterprise Manager application. I then navigate to my table FnlVldQTR_370Q1_A and right-click the mouse. The next thing I do is select Open Table --> Return all rows. The table is returned but there are 0 rows where there should be over 1 million. (It should be noted that FnlVldQTR_370Q10A has 0 rows in the table)

I then click the SQL pane button to display the SQL Code. The SQL code is: SELECT * FROM dbo.FnlVldQTR_370Q10A, this is WRONG!!!! The SQL code should be SELECT * FROM dbo.FnlVldQTR_370Q1_A. So, at this point, I go ahead and manually change the SQL statement to SELECT * FROM dbo.FnlVldQTR_370Q1_A and then click on the "!" to run the query. Upon clicking the "!", Enterprise Manager changes the SQL from dbo.FnlVldQTR_370Q1_A to dbo.FnlVldQTR_370Q10A.

This above problem happens on both the FnlVldQTR_370Q1_A and FnlVldQTR_370Q1_D tables when trying to query the with Open Table --> Return all rows. When you use Open Table --> Return Top.... or Open Table --> Query it will return rows from the proper table. But, if you make a change via the SQL code pane, the Diagram pane, or the Grid pane it reverts back to the dbo.FnlVldQTR_370Q10A from the dbo.FnlVldQTR_370Q1_A table and the same goes for Q1_D and Q12D tables.

Has anyone else experienced this problem w/ SQL Server 2000 Enterprise Manager? To further test the problem I created another table FnlVldQTR_370Q32D because I had a table FnlVldQTR_370Q3_D in the database. After creating the 32D table the same problem happened. It would "return all rows" on the 32D table even though I was right clicking and working on the 3_D table. Any thoughts?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

I was able to reproduce this behavior in Enterprise Manager, but not with Query Analyzer's Object Browser or in SQL 2005 Management Studio. I am guessing that the SQL-DMO code in Enterprise Manager is using a LIKE operator to verify the table name because "table_name LIKE 'FnlVldQTR_370Q1_A'" would return both names. I couldn't say whether this is in EM, in the SQL-DMO Tables.Items("Name") method, or somewhere else.

Possible solutions:

Don't use table names that only differ by an underscore.
Switch to SQL 2005 tools (does not require upgrading database version) (keep Enterprise Manager around for a few things like database diagrams).
Use "Open Table -> as Query" and manually check the problem names. Chuckle to yourself occasionally as you contemplate the confusion to be experienced by your successors.

John Dempsey Hall of Fame Points: 3225 More actions · Answer 2

Thanks for the reply Scott. I forgot to mention that it worked in QA and SQL 2005 tools. Unfortunately, my company has not switched to 2005 yet.

I love your last bullet point though.