This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 352148 (SHILOH_BUGS)
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.
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.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
To work around the problem, remove the brackets from the table name in the dbcursor function; for example:
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
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.
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;char col1;: 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:Startingexec sp_cursor @cursor = 180150000, @optype = 4, @rownumber = 1, @table = '[curTblTest] ... padded with 244 spaces ... ', @VALUES = '''a'''Exception Error: 16933, Severity: 16, State: 2SQL Server was not able to parse "@table = '[curTblTest] '" correctly.