PRB: Double Quotation Mark Params Error w/ Quoted Identifiers

• Microsoft SQL Server, versions 6.0 & 6.5

SYMPTOMS

If you enclose a character string in double quotation marks that is over 30 bytes long, the application may receive the error:

   Msg 103, Level 15, State 2
   The identifier that starts with 'a' is too long. Maximum length is 30.

CAUSE

SQL Server version 6.0 does not always flag stored procedure parameters enclosed in double quotation marks if the SET QUOTED_IDENTIFIER ON option has been issued.

RESOLUTION

Change the procedure call to enclose the parameter values in single quotation marks:

   execute myproc 1, 'abcdefghijklmn'

   SQLExecDirect(hstmt, "execute myproc ?,?", SQL_NTS);

   SQLExecDirect(hstmt, "{ call myproc (?,?)}", SQL_NTS);

MORE INFORMATION

Microsoft SQL Server version 6.0 introduces the concept of quoted identifiers. As described in the "Transact-SQL Reference," when the QUOTED_IDENTIFIERS option is set on (SET QUOTED_IDENTIFIERS ON), SQL Server expects quoted identifiers to be enclosed in double quotation marks (") and data values to be enclosed in single quotation marks ('). In the case of character parameters of stored procedures, data values enclosed in double quotation marks are accepted by SQL Server if the character string is less than 30 bytes long. They should be considered syntax errors by SQL Server and generate an error.

You can also see this in ODBC applications using the Microsoft ODBC SQL Server driver version 2.50.0121. This driver sets QUOTED_IDENTIFIERS ON when it runs against a SQL Server version 6.0 server so that the driver's behavior more closely matches the ANSI and ODBC standards. ODBC applications which use double quotation marks for parameter values may see this behavior after you upgrade to SQL Server version 6.0 and the ODBC 2.50.0121 driver.

This behavior has been seen when using stored procedures that contain nested stored procedure execution with parameters delimited in double- quotes (such as xp_cmdshell) from ODBC applications such as Internet Information Server.

In these cases, you may be able to workaround the problem by placing a SET QUOTED_IDENTIFIER OFF statement at the beginning of the stored procedure. This setting will only be in effect for the scope of the stored procedure and not affect other statement execution outside of the stored procedure.

Example:

create procedure iisproc as begin declare @stmt varchar(255) set quoted_identifier off select @stmt = 'xp_cmdshell "c:\myprog xxxxxxxxxxxxxxxxxxxxxxxx"' exec (@stmt) end