12

I'm using SQSH (version 2.1) on Ubuntu 10.04 to connect to a MSSQL database using a command like this:

sqsh -S server -U user -P password -D database

I have a table called My Table, but I cannot find a way to run a SELECT query on it. This is what I've tried so far:

SELECT * FROM 'My Table'
go

Output: Incorrect syntax near 'My Table'. (I get the same for double quotes)

\set t="My Table"
SELECT * FROM $t
go

Output: Invalid object name 'My'. (Which is weird because if I do \echo $t, I get the full table name)

SELECT * FROM My\\ Table
go

Output: Invalid object name 'My'.

SELECT * FROM [My Table]
go

Output: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

This last command works fine for table names without any spaces.

UPDATE: other commands work fine e.g. I can get the table description with:

SELECT column_name,data_type FROM information_schema.columns WHERE table_name = 'My Table'
go
jackocnr
  • 17,068
  • 10
  • 54
  • 63
  • You're using Sybase and you have a table with spaces on the name? – aF. Jan 19 '12 at 16:43
  • 1
    No, it's an MSSQL database. Sorry, maybe the tags were misleading. – jackocnr Jan 19 '12 at 17:25
  • Can you test what happens if you remove the space from the table name? – Phil Gan Jan 19 '12 at 17:28
  • As I mentioned at the end of the question: there are other tables without spaces in their names, and everything works fine for them i.e. I can use the name on it's own, or wrap it in square brackets, or assign it to a variable and use that, and it works fine. – jackocnr Jan 19 '12 at 17:44

4 Answers4

17

Putting the table name in quotes doesn't work in MS SQL Server.
The correct way is using [ ]:

SELECT * FROM [My Table]
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • Thanks, but as I said in the question, that didn't work for me. I was missing some configuration settings (see my answer), and once I had those, the SELECT query worked with either quotes or square brackets. – jackocnr Jan 20 '12 at 11:56
5

Im using SQL 2008R2, and the following works for me 

['table name']
AstroCB
  • 12,337
  • 20
  • 57
  • 73
Shane
  • 51
  • 1
  • 1
2

Finally found the solution. I had to add the following 2 lines to /etc/freetds/freetds.conf

tds version = 8.0
client charset = UTF-8
jackocnr
  • 17,068
  • 10
  • 54
  • 63
0

Try setting QUOTED_IDENTIFIER to ON when using SQL Server. For more info about QUOTED_IDENTIFIER see: http://msdn.microsoft.com/en-us/library/ms174393.aspx

Ɖiamond ǤeezeƦ
  • 3,223
  • 3
  • 28
  • 40