You may not be able to identify or modify rows after you retrieve the rows from SQL Server 2000 by using browse mode (886834)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)

SYMPTOMS

If you retrieve rows from a Microsoft SQL Server database by using browse mode in a client application and the rows contain null values, you may not be able to identify the appropriate rows and perform the correct manipulations to the rows in the result set. Additionally, when you try to update the rows by using the additional key column metadata that is returned by the SQL Server program, the rows are not correctly updated in the SQL Server database.

This problem occurs when all the following conditions are true:
  • The SELECT query that you try to use to retrieve the rows from the SQL Server database involves an outer join operation.
  • A unique index is defined on the table on the inner side of the outer join statement.
  • The unique index key columns of the table can accept null values.

WORKAROUND

To work around this problem, create the table on the inner side of an outer join statement of the query so that the unique index key columns of the table cannot accept null values.

MORE INFORMATION

Steps to reproduce the problem

  1. Start SQL Query Analyzer, and then connect to an instance of SQL Server 2000.
  2. Create two tables that each have one column, and name the column IndexColumn.
  3. Name the tables LeftTable and RightTable.
  4. In the LeftTable table, define a unique index on the IndexColumn column.
  5. Make sure that the column can accept null values. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    CREATE TABLE LeftTable(IndexColumn INT NULL UNIQUE)
GO
CREATE TABLE RightTable(IndexColumn INT NOT NULL)
GO
  6. Insert several values in the LeftTable table and in the RightTable table. Make sure that you insert a null value in the LeftTable table. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    INSERT INTO LeftTable VALUES(2)
INSERT INTO LeftTable VALUES(NULL)
INSERT INTO RightTable VALUES(1)
INSERT INTO RightTable VALUES(3)
GO
  7. Turn on the NO_BROWSETABLE option. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    SET NO_BROWSETABLE ON
GO
  8. Access the data in the LeftTable table and in the RightTable table by using an outer join operation in a SELECT query so that the LeftTable table is on the inner side of the outer join statement. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    SELECT
   LeftTable.IndexColumn
FROM
   LeftTable RIGHT JOIN RightTable
ON
   LeftTable.IndexColumn = RightTable.IndexColumn
    The following output is listed in the Results pane:
    IndexColumn 
----------- 
NULL
NULL
    After you run the SELECT query to access the tables in browse mode, the result set of the SELECT query contains two null values for the IndexColumn column in the LeftTable table because of the definition of the right outer join statement.

    When SQL Server 2000 returns the result set to the client, the client cannot distinguish between the null values that came from the table and the null values that the right outer join statement introduced.

REFERENCES

For more information about browse mode, see the "SELECT" topic and the "BrowseMode" topic in SQL Server Books Online.
For additional information about the same problem in Microsoft SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:

885146 Additional information about the FOR BROWSE option and the NO_BROWSETABLE option in SQL Server 2005

Modification Type:MajorLast Reviewed:10/19/2004
Keywords:kbupdateissue kbtable kbTSQL kbtshoot kbprb KB886834 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.