PRB: Double Quotation Mark Params Error w/ Quoted Identifiers

   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