FIX: Insert Error (Msg 213 ) with NO_BROWSETABLE and INSERT EXEC (275483)

SYMPTOMS

If both of the following conditions are true:

The NO_BROWSETABLE option is set on.

-and-
A stored procedure that runs a SELECT statement on tables with a primary key or a unique index (this is referenced as <stored procedure> in the command below) exists.

You will receive error 213 if you execute the following statement:

INSERT INTO <table> EXEC <stored procedure>

The error message you receive is:

Msg 213, Level 16, State 1
Insert error: column name or number of supplied values does not match table definition. Command has been aborted.

RESOLUTION

NOTE: The hotfix described in this section applies to SQL Server 6.5. If you have SQL Server 7.0, this hotfix does not apply; the fix has been included in SQL Server 7.0 Service Pack 2 (SP2). See the STATUS section of this article for information on how to obtain SP2.

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix should have the following file attributes or later:

   Date         Time      Version      Size         File name    Platform
   ----------------------------------------------------------------------

   11/08/99   07:40 AM   6.50.464   2,719,403 KB   SQL464i.exe   Intel
   11/18/99   12:50 PM   6.50.464   4,538,007 KB   SQL464a.exe   Alpha

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

WORKAROUND

To work around this problem, suppress one of the three conditions that causes it. That is, you can do any one of the following:

Do not set the NO_BROWSETABLE option on. To ensure this, use server-side cursors (for additional information, see the MORE INFORMATION section of this article).
Modify the INSERT INTO...EXEC statement so that it does not call a stored procedure.
Do no use primary keys or unique indexes for the tables that are referenced in the called stored procedure.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5.

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

SET NO_BROWSETABLE ON is an undocumented option performed for Remote Data Service (RDS) ActiveX Data Connector (ADC) connections to SQL Server. Enabling this option makes every SELECT statement act as though FOR BROWSE had been appended to the statement, but bypasses the temporary table that FOR BROWSE normally pipes the results through. The net effect is to add keys and timestamps to the query as hidden output columns so the client can update specific rows (updateable cursors) without separate trips to the server to pick up the metadata and using the query to get the appropriate columns.

The NO_BROWSETABLE option in enabled through RDS or ADC-based applications when they elect to use client-side cursors. Using server-side cursors may be a potential workaround for these cases as well.