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

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.

1. 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.
2. Set a Reference to the Microsoft ActiveX Data Objects Library.
3. 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 &amp; "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
   

4. 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 &amp; "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.