Incorrect syntax near ''

Question

I'm trying to run the following fairly simple query in SQL Server Management Studio:

SELECT TOP 1000 *  FROM      master.sys.procedures as procs left join      master.sys.parameters as params on procs.object_id = params.object_id 

This seems totally correct, but I keep getting the following error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ''.

It works if I take out the join and only do a simple select:

SELECT TOP 1000 * FROM      master.sys.procedures as procs 

But I need the join to work. I don't even have the string '' in this query, so I can't figure out what it doesn't like.

Answer 1

Such unexpected problems can appear when you copy the code from a web page or email and the text contains unprintable characters like individual CR or LF and non-breaking spaces.

Answer 2

Panagiotis Kanavos is right, sometimes copy and paste T-SQL can make appear unwanted characters...

I finally found a simple and fast way (only Notepad++ needed) to detect which character is wrong, without having to manually rewrite the whole statement: there is no need to save any file to disk.

It's pretty quick, in Notepad++:

• Click "New file"
• Check under the menu "Encoding": the value should be "Encode in UTF-8"; set it if it's not
• Paste your text
• From Encoding menu, now click "Encode in ANSI" and check again your text

You should easily find the wrong character(s)