Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. FIX: Double Quotes in Remote View Filter Expression Cause Error

FIX: Double Quotes in Remote View Filter Expression Cause Error

View products that this article applies to.

This article was previously published under Q157628

↑ Back to the top

Symptoms

If the Filter expression of a remote view uses double quotes, then running or modifying the view causes the following error message:
Connectivity Error........invalid column name....

↑ Back to the top

Cause

Microsoft SQL Server version 6.0 and up uses single quotes (') for string constants and double quotes (") for identifiers with spaces like "my table," "my column," and so forth. You must convert double quotes to single quotes in the View Designer, before the example constant is included in the SQL string.

↑ Back to the top

Resolution

Use Single Quotes (' ') instead of Double Quotes (" ").

To make the example under "Steps To Reproduce Behavior" section work, change the Example Expression in the Filter tab to use Single Quotes ('B') instead of Double Quotes ("B"). If you are creating the Remote View programmatically then change the WHERE clause to use single quotes as follows: 
   CREATE SQL VIEW test;
     REMOTE CONNECTION "myConn";
     AS SELECT Authors.au_id, Authors.au_lname, Authors.au_fname;
       FROM dbo.authors Authors;
       WHERE Authors.au_lname = 'B'

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

More information

Steps to Reproduce Behavior

  1. Use the 32-Bit ODBC Manager to create a DataSource to the PUBS database on a Microsoft SQL Server.
  2. Open a Database or create a new one.
  3. Create a Remote View based on the DataSource created in step 1.
  4. Add the Authors Table to the View
  5. Select three to four fields from the table
  6. Click the Filter tab, create and select a character type field such as "Authors.au_lname," and select any criteria, for example, "=".
  7. Add A with the double quotes (" ") as the expression.
  8. Right-click the Query Designer and click Run Query from the shortcut menu.
The following error appears:
Connectivity Error [Microsoft][ODBC SQL SERVER DRIVER]
[SQL SERVER]invalid column name 'B'
The same behavior occurs if you create a remote view programmatically. So, instead of the above steps, you can issue the following commands to reproduce this behavior: 
   CREATE SQL VIEW test;
     REMOTE CONNECTION "myConn";
     AS SELECT Authors.au_id, Authors.au_lname, Authors.au_fname;
       FROM dbo.authors Authors;
       WHERE Authors.au_lname = "B"

↑ Back to the top

Keywords: KB157628, kbfix, kbbug

↑ Back to the top

Article Info
Article ID : 157628
Revision : 3
Created on : 11/17/2003
Published on : 11/17/2003
Exists online : False
Views : 374