[freetds] SUMMARY: Msg 170 Incorrect Syntax - could this be caused by odd c haracters in the table name?

Andy Cranston a.cranston at selwaymoore.com
Thu Apr 1 10:03:57 EST 2004


My thanks to Bill Thompson for sending me these wise words of wisdom:

> Hi Andy,
> 
> Fairly simple this...
> 
> see SET QUOTED_IDENTIFIER In SQl server books online.
> 
> When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double
> quotation marks...
> When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted.... 
> ...
> The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server
> automatically set QUOTED_IDENTIFIER to ON when connecting
> SET QUOTED_IDENTIFIER defaults to OFF for connections from DB-Library
> applications.
> ...
> 
> we default to the ODBC behaviour, but this is turned off when you connect
> by db-library
> 
> if you want to persist with your table names, you'll have to explicitly
> issue a "SET QUOTED_IDENTIFIER ON" once you have connected to the
> server...
> 
> HTH,

Indeed it did Bill.  After some Googling I learnt that using [ and ] instead
of " in my query text was the way forward.  My query now looks like:

select [Description], [Manufacturer Code], [Item Category Code]
from [TEST SML$Item] where ([No_] = 'A4800A')

and is working.

Regards,

Andy Cranston.

> -----Original Message-----
> From: Andy Cranston [mailto:a.cranston at selwaymoore.com]
> Sent: 01 April 2004 12:04
> To: 'freetds at lists.ibiblio.org'
> Subject: [freetds] Msg 170 Incorrect Syntax - could this be caused by
> odd characters in the table name?
> 
> 
> Hello list,
> 
> After successfully compiling the dblib t001.c unit test 
> program I am now on
> the next stage of building my own C program to query a SQL Server 8.0
> database.
> 
> I am trying to select three columns from a table based on a 
> part identifier
> number.  Here is the output of the program:
> 
> $ ./csq A4800A
> select "Description","Manufacturer Code","Item Category Code"
> from "TEST SML$Item" where ("No_" = 'A4800A')
> Msg 170, Level 15, State 1
> Server 'BUSSYS', Line 1
> Line 1: Incorrect syntax near 'TEST SML$Item'.
> ./csq: part identifier lookup gave incorrect number of columns
> $
> 
> So my syntax is wrong?  Ok - so this is a tsql transcript to the same
> server:
> 
> $ tsql -S BUSSYS -U dbreadonly
> locale is "C"
> locale charset is "646"
> Password:
> Msg 5703, Level 0, State 1, Server BUSSYS, Line 0
> Changed language setting to us_english.
> 1> use Test
> 2> go
> 1> select "Description","Manufacturer Code","Item Category Code"
> 2> from "TEST SML$Item" where ("No_" = 'A4800A')
> 3> go
> Description     Manufacturer Code       Item Category Code
> PCI FWD SCSI2 Interface Card    HP      MISC
> 1> exit
> $
> 
> This works perfectly.
> 
> The only thing I can think of is that the table name:
> 
> TEST SML$Item
> 
> contains a space and a $ (dollar) character.  Could this be 
> the cause of the
> syntax error and, if so, why does running the same query via 
> tsql work when
> submiting the query via my C program does not?
> 
> Any help gratefully received.
> 
> I can post the C code to the list if necessary or email it to any kind
> individuals who can help me out.
> 
> Regards,
> 
> Andy Cranston.

________________________________________________________________________
This message has been checked for all known viruses by the Selway Moore
scanning service powered by MessageLabs. For further information and
statistics on current viruses visit http://www.messagelabs.com/stats.asp
________________________________________________________________________



More information about the FreeTDS mailing list