Microsoft KB Archive/290413

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

Article ID: 290413

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q290413



BUG #: 352148 (SHILOH_BUGS)



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 &quot;More Information&quot; 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, &quot;curTblTest&quot;, &quot;'a'&quot;);



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 &quot;API Server Cursors&quot; 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.   Create the following table by using SQL Server Query Analyzer: create table curTblTest ( col1 char(10))   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,                         &quot;select col1 from [curTblTest]&quot;,                            CUR_DYNAMIC, CUR_LOCKCC, 5, pstat); dbcursorbind(hcursor, 1, NTBSTRINGBIND, 11, NULL, (LPBYTE )col1); dbcursor(hcursor, CRS_INSERT, 1, &quot;[curTblTest]&quot;, &quot;'a'&quot;); 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</li></ul> </li></ol>

The following sample Profiler trace was captured:

<pre class="fixed_text">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 &quot;@table = '[curTblTest]       '&quot; correctly.

Additional query words: dblib server-side sp1 cursor

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB290413

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.