PRB: Call to SQL Stored Procedure Returns 'Invalid Column Name'

Article translations Article translations
Article ID: 222664 - View products that this article applies to.
This article was previously published under Q222664
Expand all | Collapse all

On This Page

SYMPTOMS

When calling a SQL Server stored procedure that uses Exec('string') syntax, you may get an error returned to the calling client if you are referencing values using double quotes. The error that is returned to the calling client is:
-2147217900 -- [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
column name 'Column'.
This error is being raised because the SQL Server driver is interpreting the value inside of the double quotes as a column name instead of a field value. The cause of this problem most likely is the SQL Server driver defaulting to setting the QUOTED_IDENTIFIER option to 'on'.

RESOLUTION

There are three ways to avoid this problem:
  • The best solution to this problem is to substitute two single quotes for each of the double quotes in the stored procedure. This tells SQL Server not to treat these quotes as the ending single quote for the EXEC('') statement. For example, the following code:
       DROP PROC sptest
       go 
       CREATE PROC sptest as 
    
       EXEC('SELECT au_id, au_lname,  au_fname = (
    	CASE
    		when au_lname = "Ringer" then "value1"
    		else "default"
    	END 
    	) 
    	FROM Authors')
    
    						


    changes to this code:
       DROP PROC sptest
       go 
       CREATE PROC sptest as 
    
       EXEC('SELECT au_id, au_lname,  au_fname = (
    	CASE
    		when au_lname = ''Ringer'' then ''value1''
    		else ''default''
    	END 
    	) 
    	FROM Authors')
    
    					
  • Execute the connection level option SET QUOTED_IDENTIFIER OFF immediately after establishing the connection with the database.
  • If you are using the Microsoft OLEDB Provider for ODBC Drivers, you can add the following to the end of you connection string: 'QuotedID=No'.
  • If you are using a DSN connection to SQL Server you can also set Quoted Identifiers off by deselecting the option 'Use ANSI Quoted Identifiers' in the ODBC Administrator when you are creating or configuring the DSN to SQL Server. This accomplishes the same thing as using the 'QuotedID=No' option in the connection string, as shown in the third work around.
The latter two of these work arounds are demonstrated in the code example in the MORE INFORMATION section.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

The following code reproduces this problem. This code assumes that you will be using the pubs database that ships with SQL Server. If you are using a different database you will need to make some modifications to the code.
  1. Using the ISQL utility establish a connection to your SQL Server and execute the following stored procedure:
       DROP PROC sptest
       go 
       CREATE PROC sptest as 
    
       EXEC('SELECT au_id, au_lname,  au_fname = (
    	CASE
    		when au_lname = "Ringer" then "value1"
    		else "default"
    	END 
    	) 
    	FROM Authors')
    
    					
  2. Create a new Visual Basic Standard EXE project and add a command button to the default form.
  3. From the Project menu, choose References and add a reference to the Microsoft ActiveX Data Objects library.
  4. Add the following code to the command button's click event. You may need to alter the connection string to point to your SQL Server. Make sure that you change UID=<username> to an appropriate username that has the permissions to perform the required operations on the specified database.
       Private Sub Command1_Click()
       
        Const CONNECTSTR = "Provider=MSDASQL.1;Persist Security Info=False;" _              
           & "Extended Properties=Driver={SQL Server}; " _ 
           & "SERVER=(local);UID=<username>;DATABASE=pubs"
       
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim cmd As ADODB.Command
            
        Set rs = New ADODB.Recordset
        Set cn = New ADODB.Connection
        With cn
            .ConnectionString = CONNECTSTR
            .CursorLocation = adUseServer
            .Open
            
        End With
       ' Uncomment this line to avoid error.
       ' cn.Execute "SET QUOTED_IDENTIFIER OFF"
    
        Set cmd = New ADODB.Command
        With cmd
            .CommandText = "sptest"
            .CommandType = adCmdStoredProc
            .ActiveConnection = cn
    
        End With
        Set rs = cmd.Execute
        
        Do Until rs.EOF
            sz = ""
            For i = 0 To rs.Fields.Count - 1
                sz = sz & rs(i).Value & "  |  "
            Next i
            Debug.Print sz
            rs.MoveNext
        Loop
    
       End Sub
    					
  5. Run the code and notice that the error occurs. You can either uncomment the line 'cn.Execute "SET QUOTED_IDENTIFIER OFF"' or you can add ';QuotedID=No' to the end of the connection string. Either one of these will help you to avoid this error.

REFERENCES

For additional information, see Microsoft SQL Server Books Online

For more information, please see the following article(s) in the Microsoft Knowledge Base:
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
156501 INF: QUOTED_IDENTIFIER and Strings with Single Quotation Marks
135531 PRB: Double Quotation Mark Params Error w/ Quoted Identifiers

Properties

Article ID: 222664 - Last Review: June 23, 2005 - Revision: 6.2
APPLIES TO
  • Microsoft Data Access Components 1.5
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • Microsoft SQL Server 6.5 Standard Edition
Keywords: 
kbdatabase kbprb kbstoredproc KB222664

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com