PRB: Error "Syntax Error Near 'Tablename'" on Recordset Update

Symptoms

With SQL Server's quoted_identifier option set to Off, you may receive the following error:

Run-time error '-2147217900 (80040e14)':
Line 1: Syntax error near 'tablename'

This error occurs when you are using client-side cursors with the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). The error occurs on an ActiveX Data Objects (ADO) recordset's Update method and may occur on an AddNew method.

Cause

With ADO client-side cursors, when you invoke an ADO recordset's AddNew or Update method, the OLE DB Provider prepares a SQL statement to send to SQL Server.

The Microsoft OLE DB Provider for SQL Server automatically quotes identifiers on an ADO recordset's Update method and may quote identifiers on an AddNew method. Identifiers include table names and field names.

For example, updating the Titles table in the Pubs database with the following code:

MyADORecordet.Update

The preceding would be prepared similar to the following:

UPDATE "titles" SET "title"='Hello World' WHERE "title_id"='3'

Note that the table name is in quotes, "titles", and that each field name is in quotes, "title", "title_id", and so on.

If SQL Server's Quoted_Identifier option is set to Off, SQL Server will not recognize table names and field names enclosed in quotes.

The error "Syntax error near 'tablename'" occurs.

Resolution

If you do not have a reason to set quoted identifiers off, set them back on and the above error message will no longer be shown.

If it is necessary to have quoted identifiers set off, following are two workarounds that allow you to set the quoted_identifier option off and update records using ADO recordsets:

Use server-side cursors.

For example, before opening an ADO Connection object named cn, use the following syntax:
cn.CursorLocation = adUseServer
-or-
Use the Microsoft OLE DB Provider for ODBC Drivers with the Microsoft SQL Server ODBC Driver.

By default, the SQL Server ODBC driver quotes identifiers. However, you may configure the SQL Server ODBC driver to prepare SQL statements without quoted identifiers.

NOTE: You must use the SQL Server ODBC driver 2.65.0240 that ships with the SQL Server 6.5 Service Pack 2, or a later driver. Earlier SQL Server ODBC drivers do not have this capability.
1. Add "QuotedID=No" in the ODBC connection string.
  
  -or-
2. Clear Use ANSI Quoted Identifiers in the Microsoft SQL Server Data Source Name (DSN) Configuration.
  1. Open the ODBC Administrator.
  2. Open the User DSN or System DSN that you are using to connect to your SQL Server database.
  3. Click Next until you reach the dialog box with Use ANSI Quoted Identifiers.
  4. Clear the check box next to Use ANSI Quoted Identifiers.
  5. Click Finish.

NOTE: Examples of both workarounds are shown in the "More Information" section.

Status

This behavior is by design.

More Information

ADO and the underlying OLE DB Provider are not aware of the SQL Server setting for the quoted_identifier, as set by the Transact SQL (T-SQL) statements:

Set quoted_identifier Off

-or-
Set quoted_identifier On

ADO's client-side quoted identifier behavior is derived from the underlying OLE DB provider. Therefore, to use client-side cursors, you must configure the Provider to quote or not to quote identifiers, depending on the SQL Server setting for quoted_identifier.

The OLE DB Provider for SQL Server automatically quotes identifiers to ensure that if the identifier contains a special character, it will be quoted, as required by SQL Server. Note that the identifier does not have to actually contain a special character. It is only the possibility that causes the OLE DB Provider to quote the identifier. The OLE DB Provider for SQL Server does not have a property to explicitly specify that identifiers should or should not be quoted.

You can configure the OLE DB provider for ODBC to prepare SQL statements with or without quotes around identifiers. It uses the ODBC driver setting for QuotedID to determine whether to quote identifiers. For this reason, you may include the "QuotedID=Yes" or "QuotedID=No" option in the ODBC connect string, or select/deselect "Use ANSI Quoted Identifiers" in a DSN setup. Note that, by default, "QuotedID=Yes", instructing the ODBC to quote identifiers.

When you use ADO server-side cursors, cursors open on the server. The OLE DB Provider prepares the T-SQL sp_cursoropen, sp_cursorfetch, and related server-side cursor statements, instead of action queries.

The ADO Connection object's "Quoted Identifier Sensitivity" property shows the configuration a Provider uses to quote identifiers. The "Quoted Identifier Sensitivity" property is read-only, and only available at run- time after the Connection object has been opened. The "Quoted Identifier Sensitivity" property is only available for certain Providers, including the SQL Server and ODBC Providers. Since the property is read-only, you cannot use the "Quoted Identifier Sensitivity" property to configure a Provider to quote or not quote identifiers.

The ADO Connection object's read-only "Quoted Identifier Sensitivity" property will be as follows:
8 - When the Provider is configured to quote identifiers.
0 - When the Provider is configured not to quote identifiers.

Steps to Reproduce Behavior

NOTE: In the following code examples, substitute your server's name for servername in the connection strings.

This example uses the Pubs database that comes with SQL Server.

Create the user interface:
1. In Visual Basic, create a new Standard .exe project. Form1 is created by default.
2. Add a Command button to Form1.
Set a Reference to the Microsoft ActiveX Data Objects Library.

Copy and paste the following code into the Click event of Command1.

Note You must change User ID <username> and Password <strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Dim strcn As String
      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset

      strcn = "Provider=SQLOLEDB;User ID=<user name>;Password=<strong password>;Initial Catalog=Pubs;"
      strcn = strcn & "Data Source=servername"

      cn.ConnectionString = strcn

      'Error occurs with Client-side cursors.
      cn.CursorLocation = adUseClient

      cn.Open

      'Instruct SQL Server to turn off Quoted_Identifier.
      cn.Execute "set quoted_identifier off"

      rs.Open "select * from titles", cn, adOpenKeyset, adLockOptimistic
      rs(1).Value = "Hello World"

      'Error occurs on this line.
      rs.Update

      rs.Close
      Set rs = Nothing
      cn.Close
      Set cn = Nothing

Test the application with the following:
1. Optionally, start the SQL Server SQLTrace program on the server. SQLTrace is a standalone program in the SQL Server 6.5 group. SQLTrace allows you to view the SQL statements that arrive at the SQL Server.
2. In Visual Basic, clicking the Command button at run-time causes the following error:
  Run-time error '-2147217900 (80040e14)': Line 1: Syntax error near 'tablename'
3. If you are using SQLTrace, you may examine the T-SQL UPDATE statement that the OLE DB Provider for SQL Server created. Note that table names and field names appear in quotes.

Examples of Workarounds

Using the OLE DB Provider for ODBC Drivers (MSDASQL)

You must use the SQL Server ODBC driver 2.65.0240 that ships with SQL Server 6.5 Service Pack 2, or a later driver.

Modify the connection string in the preceding example to the following.

Note You must change UID <username> and PWD <strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.
strcn = "Provider=MSDASQL;driver=SQL Server;UID=<user name>;PWD=<strong password>;"
strcn = strcn & "DATABASE=pubs;SERVER=servername;QuotedId=No"

Using Server-Side Cursors

Modify the cn.CursorLocation in the preceding example to the following:
cn.CursorLocation = adUseServer
If you are using the SQL Server SQLTrace program, you may examine the T-SQL sp_cursor statements that the OLE DB Provider creates.

References

SQL Server Books Online; search on: "quoted_identifier."

For a discussion of configuring the SQL Server ODBC Driver with quoted_identifier on or off, please see the following article in the Microsoft Knowledge Base:

135533INF: Differences in SQL Behavior Between ODBC and ISQL