Microsoft KB Archive/886834

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

Article ID: 886834

Article Last Modified on 10/19/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.



Steps to reproduce the problem
 Start SQL Query Analyzer, and then connect to an instance of SQL Server 2000. Create two tables that each have one column, and name the column IndexColumn . Name the tables LeftTable and RightTable . In the LeftTable table, define a unique index on the IndexColumn column.  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   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   Turn on the NO_BROWSETABLE option. To do this, run the following Transact-SQL statements in SQL Query Analyzer: SET NO_BROWSETABLE ON GO </li>  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:

<pre class="fixed_text">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. </li></ol>

<div class="references_section">