current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Why do I get an error when writing my select statement like this - Note the space/tab/newline next to my table name "Personnel Number" (I know that naming convention is bad, but I had to do it, since that is the way a excel spreadsheet looks like that I have to import regularly to the table)

SELECT md.[Personnel Number]
FROM    MainDump md
    LEFT JOIN   EthicsManagement em
    on em.[Personnel Number] = md.[Personnel Number]

And I have to write it like this to not give me an error

SELECT md.[Personnel Number
]
FROM    MainDump md
    LEFT JOIN   EthicsManagement em
    on em.[Personnel Number] = md.[Personnel Number
]
share|improve this question
    
May be you have omitted a space between Personal Number and FROM clause in first query –  Santhosh May 6 '13 at 6:15
1  
The error message would help. –  Jordan Arseno May 6 '13 at 6:29
    
Invalid column name 'Personnel Number'. –  Ruan May 6 '13 at 6:35
    
@Santhosh Checked, and no spaces have been omited –  Ruan May 6 '13 at 6:36
1  
It is generally a nightmare to use spaces in your column names. I suggest sending the Excel CSV through a server-side script that maps the column names into something less prone to these errors. –  Jordan Arseno May 6 '13 at 7:41

3 Answers 3

up vote 1 down vote accepted

It's fairly obvious that your column name contains a trailing line feed. If you're absolutely sure that it's not possible to rename columns, I'm not aware of any other way to type a line feed in a column literal than the one you use:

SELECT md.[Personnel Number
]

In any case, I have a strong feeling that there's a bug in your import code. I suspect that the column is the last one in the line, you've set \n as line separator but your file actually uses \r\n.

share|improve this answer
up vote 1 down vote 
SELECT md.`Personnel Number`
FROM    MainDump md
    LEFT JOIN   EthicsManagement em
    on em.`Personnel Number` = md.`Personnel Number`

try to use [`] key behind Esc on top left in the keyboard

share|improve this answer
1  
Does SQL Server really accept MySQL-style quote characters? –  Álvaro G. Vicario May 6 '13 at 9:23
    
it doesnt work? sorry then.. –  kayla May 6 '13 at 12:36
up vote 0 down vote

I see that if you have column names with spaces in, after you set one of those columns as a Primary key, this happens.

It adds this to your column name [Column Name]]

Note the double bracket

So Main point. To avoid having random errors like this, Don't use spaces in your columns names.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.