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.
This question is related to
sql
sql-server
ssms
I got this error because I pasted alias columns into a DECLARE statement.
DECLARE @userdata TABLE(
f.TABLE_CATALOG nvarchar(100),
f.TABLE_NAME nvarchar(100),
f.COLUMN_NAME nvarchar(100),
p.COLUMN_NAME nvarchar(100)
)
SELECT * FROM @userdata
ERROR: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '.'.
DECLARE @userdata TABLE(
f_TABLE_CATALOG nvarchar(100),
f_TABLE_NAME nvarchar(100),
f_COLUMN_NAME nvarchar(100),
p_COLUMN_NAME nvarchar(100)
)
SELECT * FROM @userdata
NO ERROR
The error for me was that I read the SQL statement from a text file, and the text file was saved in the UTF-8 with BOM (byte order mark) format.
To solve this, I opened the file in Notepad++ and under Encoding, chose UTF-8. Alternatively you can remove the first three bytes of the file with a hex editor.
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++:
You should easily find the wrong character(s)
I was using ADO.NET and was using SQL Command as:
string query =
"SELECT * " +
"FROM table_name" +
"Where id=@id";
the thing was i missed a whitespace at the end of "FROM table_name"+
So basically it said
string query = "SELECT * FROM table_nameWHERE id=@id";
and this was causing the error.
Hope it helps
You can identify the encoding used for the file (in this case sql file) using an editor (I used Visual studio code). Once you open the file, it shows you the encoding of the file at the lower right corner on the editor.
I had this issue when I was trying to check-in a file that was encoded UTF-BOM (originating from a non-windows machine) that had special characters appended to individual string characters
You can change the encoding of your file as follows:
In the bottom bar of VSCode, you'll see the label UTF-8 With BOM. Click it. A popup opens. Click Save with encoding. You can now pick a new encoding for that file (UTF-8)
Source: Stackoverflow.com