FIX: DB-Library sp_cursor Fails with Error Message 16933 if Bracketed Identifiers are Passed with the Tablename Parameter

Article translations Article translations
Article ID: 290413 - View products that this article applies to.
This article was previously published under Q290413
BUG #: 352148 (SHILOH_BUGS)
Expand all | Collapse all

On This Page

SYMPTOMS

When you use the DB-Library API server cursors, if the table name parameter that is passed to the sp_cursor stored procedure includes a bracketed identifier, the execution of sp_cursor fails with the following error message:
SQL Server message 16933, state 2, severity 16:
The cursor does not include the table being modified or the table is not updatable through the cursor.
Refer to the "More Information" section of this article for an example and additional symptoms.

CAUSE

SQL Server is unable to successfully parse the bracketed table name with spaces.

DB-Library binds the table name as a SQLCHAR data type, which has a fixed length of 255 characters (the maximum length of this data type). The buffer holds the table name, including the brackets, with the remaining bytes padded with spaces.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around the problem, remove the brackets from the table name in the dbcursor function; for example:
dbcursor(hcursor, CRS_INSERT, 1, "curTblTest", "'a'");
				

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

The Microsoft® SQL Server™ OLE DB provider, SQL Server ODBC driver, and DB-Library programming libraries have special cursor functions that are optimized for cursor operations between a client application and a computer that is running SQL Server. These cursor functions (or APIs) use server cursors that are implemented by the use of special system stored procedures within SQL Server of which sp_cursor is one. For more information, refer to the "API Server Cursors" topic in SQL Server Books Online.

The OLE DB provider, ODBC driver and DB-Library programming interface all have different ways of calling these system stored procedures. These system stored procedures appear in SQL Server Profiler traces of Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC, and DB-Library applications that use API server cursors. These system stored procedures are only intended for the internal use of the SQL Server Provider for OLE DB, the SQL Server ODBC driver, and the DB-Library DLL. The full functionality of these procedures is available to the applications through the use of the cursor functionality of the database APIs. Specifying the procedures directly in an application is not supported.

Steps to Reproduce Behavior

The example that follows uses the dbcursor DB-Library cursor function, which you can use to perform positional updates, deletes or inserts. You can find more information about the use of the dbcursor function in SQL Server Books Online. The parameters that are passed to the dbcursor function include the table name of the table that is being modifed. The actual implementation of the function consists of a call to sp_cursor with the table name passed as a parameter.
  1. Create the following table by using SQL Server Query Analyzer:
    create table curTblTest ( col1 char(10))
    					
  2. Use the dbcursor DB-Library API to insert a single row:
    /* 
     * For clarity purposes, DB connection and error handling steps 
     * were not included in the sample code. 
     */ 
    :
    DBCURSOR  *hcursor;
    DBINT	  pstat[5];
    char 	  col1[5][11];
    :
       hcursor = dbcursoropen(dbproc,
                              "select col1 from [curTblTest]", 
                               CUR_DYNAMIC, CUR_LOCKCC, 5, pstat);
       dbcursorbind(hcursor, 1, NTBSTRINGBIND, 11, NULL, (LPBYTE )col1);
       dbcursor(hcursor, CRS_INSERT, 1, "[curTblTest]", "'a'");
       dbcursorclose(hcursor);
    					
    The code fails with the 16933 error message. To observe what was sent to the computer running SQL Server, use the SQL Profiler utility and capture the following events with the appropiate Data columns:

    • Stored Procedures - RPC: Starting
    • Error and Warning - Exception
The following sample Profiler trace was captured:

RPC:Starting

exec sp_cursor @cursor = 180150000, @optype = 4, @rownumber = 1, 
@table = '[curTblTest]  ... padded with 244 spaces ...  ', @VALUES = 
'''a'''

Exception
    
Error: 16933, Severity: 16, State: 2

SQL Server was not able to parse "@table = '[curTblTest]        '" 
correctly.

				

Properties

Article ID: 290413 - Last Review: November 6, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB290413

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com