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.
halfer
19.9k17 gold badges100 silver badges187 bronze badges
asked Nov 1, 2013 at 15:57
Joshua FrankJoshua Frank
13.1k11 gold badges46 silver badges95 bronze badges
7
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.
halfer
19.9k17 gold badges100 silver badges187 bronze badges
answered Nov 4, 2013 at 8:15
5
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)
answered May 21, 2019 at 12:50
2
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.
encoding
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)
answered Jan 15, 2021 at 18:32
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.
answered Sep 7, 2018 at 3:19
3
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
answered Jun 7, 2019 at 3:39
IamButtmanIamButtman
3173 silver badges15 bronze badges
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
answered Jul 2, 2019 at 13:35
For me I was miss single quote in the statement
Incorrect One : "INSERT INTO Customers (CustomerNo, FirstName, MobileNo1, RelatedPersonMobileNo) VALUES ('John123', John', '1111111111', '1111111111)"
missed quote in John’ and ‘1111111111
Correct One: "INSERT INTO Customers (CustomerNo, FirstName, MobileNo1, RelatedPersonMobileNo) VALUES ('John123', 'John', '1111111111', '1111111111')"
answered Feb 23, 2022 at 13:37
abdellaabdella
5105 silver badges11 bronze badges
I was able to run this by replacing the ‘Dot’; with and ‘Underscore’; for the [dbo][tablename].
EXAMPLE:
EXEC sp_columns INFORMATION_SCHEMA.COLUMNS
GO //**this will NOT work. But will intelliSence/autocomplete as if its correct.
EXEC sp_columns INFORMATION_SCHEMA_COLUMNS
GO //**This will run in Synapse. but funny enough will not autocomplete.
answered Aug 15, 2022 at 19:16
Incorrect syntax near is an error message in SQL Server because you’re using the wrong syntax for a SQL query or the query contains unprintable characters that are invincible to the naked eye.
This article will show you SQL statements that will lead to the “incorrect syntax near” error, and we’ll show you solutions that you can use to solve similar errors in your environment.
Before you proceed, know that we’re aware of how frustrating this error can get, and that’s why this article will analyze the “incorrect syntax near ‘ ‘ meaning” as a way to make it easy for you.
With that out of the way, launch your SQL Server, and let’s teach you “how to fix incorrect syntax near ‘ ‘ in SQL Server.
Contents
- Why Do Your SQL Statements Have an Incorrect Syntax?
- – Typographical Error in Your SQL Statement
- – Misusing a Keyword or Reserved Words
- – SQL Statements Have Misplaced Punctuation Marks
- – An Unprintable Characters in Your SQL Statement
- How To Fix the Syntax Errors in Your SQL Statements?
- – Check for Typographical Error in Your SQL Statement
- – Avoid Using Reserved Words as Your Column Names
- – Use Punctuation Marks the Right Way
- – Use a Text Editor To Show the Unprintable Characters
- Conclusion
Why Do Your SQL Statements Have an Incorrect Syntax?
Your SQL statements have an incorrect syntax because of the following:
- You have a typographical error in your SQL statement
- You’re misusing a keyword or reserved words
- Your SQL statements have misplaced punctuation marks
- You have unprintable characters in your SQL statement
– Typographical Error in Your SQL Statement
Typographical errors are a common cause of the “incorrect syntax near ‘-‘. SQL date” error in SQL Server. These errors occur when there is a mistake in the way that the SQL statement is written, such as a misspelled keyword or a missing punctuation mark.
For example, given the following SQL code that will create a “Customers” table:
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
Phone VARCHAR(20),
Address VARCHAR(200),
City VARCHAR(50),
State VARCHAR(50),
ZipCode VARCHAR(10)
);
INSERT INTO Customers (CustomerID, FirstName, LastName, Email, Phone, Address, City, State, ZipCode)
VALUES
(1, ‘John’, ‘Doe’, ‘johndoe@site.com’, ‘123-456-7890’, ‘123 Main St’, ‘New York’, ‘NY’, ‘10001’),
(2, ‘Jane’, ‘Doe’, ‘janedoe@site.com’, ‘123-456-7890’, ‘456 Elm St’, ‘Los Angeles’, ‘CA’, ‘90001’),
(3, ‘Bob’, ‘Smith’, ‘bobsmith@site.com’, ‘123-456-7890’, ‘789 Oak St’, ‘Chicago’, ‘IL’, ‘60601’),
(4, ‘Alice’, ‘Johnson’, ‘alicejohnson@site.com’, ‘123-456-7890’, ‘321 Pine St’, ‘Houston’, ‘TX’, ‘77001’);
The following SQL will result in a syntax error because there is a typographical error in the keyword “FORM” which should be “FROM”:
– Misusing a Keyword or Reserved Words
SQL Server has a list of reserved words that you cannot use as identifiers for tables, columns, or other objects. So, any attempt to use a reserved word as an identifier, can lead to the “incorrect syntax near ‘ ‘ in visual studio“.
For example, the following SQL statement will result in an error because “SELECT” and “TABLE” are reserved words that you cannot use as column names.
SELECT VARCHAR(15),
TABLE INT);
The following is another example, but this time, we typed the “FROM” keyword twice, and this will also cause an error.
SELECT FROM FROM Customers;
– SQL Statements Have Misplaced Punctuation Marks
A misplaced punctuation mark can cause the “incorrect syntax near ‘;’.” error in SQL Server, and this can happen when you’re trying to retrieve a record from a table.
For example, the following will result in a syntax error because the name that we’re retrieving contains quotes that cause the error. That’s because SQL will not understand how to parse the “SELECT” statement.
SELECT * FROM Customers WHERE Customers.FirstName = ‘O’Brien’
The same error will also occur in the following code and that’s because there we used a quote in the wrong way.
UPDATE Customers SET Lastname = ‘Doe’, ‘FirstName’ = ‘Jane’ WHERE CustomerID = 2;
– An Unprintable Characters in Your SQL Statement
In SQL Server, unprintable characters can cause the “incorrect syntax near ‘ ‘. in SQL Server 2019” error because they are mostly hidden, and they can alter the structure of an SQL statement. As a result, SQL Server cannot read and parse the statement correctly, so it returns a syntax error.
For example, in the following SQL, there is a Unicode character after the “e” in “Doe”, and it will cause the “incorrect syntax near” error. To confirm, copy the code, and run it against the “Customers” table that you created earlier.
WHERE LastName = ‘Doe’•
How To Fix the Syntax Errors in Your SQL Statements?
To fix the syntax errors in your SQL statements, you can:
- Check for a typographical error in your SQL statement
- Avoid using reserved words as your column names
- Use punctuation marks the right way
- Use a text editor to show the unprintable characters
– Check for Typographical Error in Your SQL Statement
Your first routine when you see the “incorrect syntax near” error is to check your SQL statements for typographical errors.
For example, the following code is a previous example that resulted in the error:
The next SQL is the correct version of the statement, and the fix is to change “FORM” to “FROM”. This will make it a valid SQL that the SQL server will parse without throwing a syntax error.
– Avoid Using Reserved Words as Your Column Names
Another way to prevent the incorrect syntax error in SQL Server is to avoid using reserved words as your column names. It’s recommended to use descriptive names that will make maintenance easy for you and other developers in the future.
However, if you need a reserved word as a column name, there is a way to do that.
For reference, the following is a previous example that’s trying to create the BIKE_TWO table in SQL Server:
SELECT VARCHAR(15),
TABLE INT);
The SQL code contains two reserved names; “SELECT” and “TABLE” that SQL Server will not allow. To fix this, you can place square brackets around them, and the code will run fine:
[SELECT] VARCHAR(15),
[TABLE] INT);
– Use Punctuation Marks the Right Way
Using punctuation the right way in SQL Server will prevent the “incorrect syntax near ‘.’. SQL Server stored procedure“. That’s because the server will understand your SQL queries and return a result (if applicable). For a start, have a look at the following SQL that we showed you earlier:
SELECT * FROM Customers WHERE Customers.FirstName = ‘O’Brien’
The customer’s first name has quotes that we used directly in the query, and this led to an error.
The fix is to use another punctuation mark before the “B” in Brien, and this will tell SQL Server that “O” and “Brien” are two separate words in quotes. This will allow it to parse the statement without throwing a syntax error.
SELECT * FROM Customers WHERE Customers.Name = ‘O”Brien’
Another example is the following SQL statement that we also showed you earlier, but we did not mention what caused the error. Observe the code for a second time, and you will see that we used quotes around a table name. This is wrong and that’s why the SQL Server shows a syntax error.
–This is wrong.
UPDATE Customers SET Lastname = ‘Doe’, ‘FirstName’ = ‘Janee’ WHERE CustomerID = 2;
The solution is to remove the quotes from the “FirstName” column, and this cements the concept of using punctuation marks the right way.
— This is correct
UPDATE Customers SET Lastname = ‘Gonzalez’, FirstName = ‘Jonathan’ WHERE CustomerID = 2;
– Use a Text Editor To Show the Unprintable Characters
Using a text editor is the ultimate tool to solve the “incorrect syntax near ‘ ‘. in C#” error when your code has no syntax error and yet, SQL Server says otherwise.
If you’re in such a situation, do the following:
- Download Notepad++ from their official website and install it on your computer.
- Open Notepad++, and click on “New file”
- Ensure the value of the “Encoding” menu is “Encode in UTF-8”. If it’s not there, set it.
- Copy your SQL statements that are failing in SQL Server, and paste them in Notepad++.
- Click the “Encoding” menu again, and change it to “Encode in ANSI”.
- Check your text again, and you’ll see the “invincible character”.
- Delete it, and run your code in SQL Server again. It should work.
Meanwhile, if you can’t get Notepad++, you can paste the SQL in an online tool like “SQLiteonline”, and you’ll see the invincible characters immediately.
Conclusion
This article explained the causes of the “incorrect syntax near” error in SQL Server, and five methods that can fix it for you, depending on your use case. From everything that we’ve talked about, and what you’ve learned so far, remember the following:
- Typographical errors and misuse of reserved keywords are the two major causes of the “incorrect syntax near” error in SQL Server.
- Misplaced punctuation marks and unprintable characters can also cause a syntax error in SQL Server.
- The top solution for the “incorrect syntax near” in SQL Server is to check for typographical errors, and use punctuation marks the right way.
- If all solutions fail to solve the incorrect syntax in your SQL statements, paste the SQL in Notepad++, and enable “Encoding in ANSI”.
Right now, the incorrect syntax should be resolved in your SQL code. Take care of yourself, and always run correct and valid SQL statements on your SQL server.
- Author
- Recent Posts
Your Go-To Resource for Learn & Build: CSS,JavaScript,HTML,PHP,C++ and MYSQL. Meet The Team
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.
halfer
19.9k17 gold badges100 silver badges187 bronze badges
asked Nov 1, 2013 at 15:57
Joshua FrankJoshua Frank
13.1k11 gold badges46 silver badges95 bronze badges
7
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.
halfer
19.9k17 gold badges100 silver badges187 bronze badges
answered Nov 4, 2013 at 8:15
5
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)
answered May 21, 2019 at 12:50
2
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.
encoding
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)
answered Jan 15, 2021 at 18:32
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.
answered Sep 7, 2018 at 3:19
3
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
answered Jun 7, 2019 at 3:39
IamButtmanIamButtman
3173 silver badges15 bronze badges
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
answered Jul 2, 2019 at 13:35
For me I was miss single quote in the statement
Incorrect One : "INSERT INTO Customers (CustomerNo, FirstName, MobileNo1, RelatedPersonMobileNo) VALUES ('John123', John', '1111111111', '1111111111)"
missed quote in John’ and ‘1111111111
Correct One: "INSERT INTO Customers (CustomerNo, FirstName, MobileNo1, RelatedPersonMobileNo) VALUES ('John123', 'John', '1111111111', '1111111111')"
answered Feb 23, 2022 at 13:37
abdellaabdella
5105 silver badges11 bronze badges
I was able to run this by replacing the ‘Dot’; with and ‘Underscore’; for the [dbo][tablename].
EXAMPLE:
EXEC sp_columns INFORMATION_SCHEMA.COLUMNS
GO //**this will NOT work. But will intelliSence/autocomplete as if its correct.
EXEC sp_columns INFORMATION_SCHEMA_COLUMNS
GO //**This will run in Synapse. but funny enough will not autocomplete.
answered Aug 15, 2022 at 19:16
When executing a query in SQL and the editor throws back this error:
Incorrect syntax near …''
That typically means you have used the wrong syntax for the query. This happens mostly when someone switched from one relational database to another relational database, from MySQL to MS SQL Server for example. Even though SQL is standard, each database management system has it own extensions thus the syntax might vary.
So the solution is to search for the right query and replace it.
But there are some cases where the syntax seems correct, but you still keep getting the incorrect syntax error above?
This one is tricky. Such unexpected syntax error sometimes happens when you copy and paste the query directly from another source, such as from a website or note-taking app. The text then contains invisible characters like individual CR or LF and non-breaking spaces.
Solution?
- Paste it to TextEdit or Notepad to identify the invisible characters and remove them.
- Use the Beautify button in TablePlus to reformat the code and TablePlus will automatically remove the unwanted non-standard characters.
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Download TablePlus for Mac.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
Background
I was using SQL Server Management Studio ( SSMS ) and experienced an unusual error.
Let us cover it succinctly.
Query
select a = 1 union select b = 2 union all select c = 3 union all select d = 4 union all select e = 5
Error Message
Image
Textual
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Line 13 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ' '.
Troubleshooting
Online Tools
Outline
Here are some online tools for detecting hidden characters
- soscisurvey.de
- View non-printable Unicode characters
Link
- View non-printable Unicode characters
Tools
soscisurvey.de
View non-printable Unicode characters
Image
Explanation
- Hard Space
- The hard space is represented as U+A0
- Here is the image representation
- Character Found
Desktop Tools
Outline
Here are some desktop tools for managing hidden characters
- notepad++
Tools
notepad++
Search specific character – Count
Steps
- Launch Notepad++
- Load File
- Access Find Dialog
- In Find Dialog
- Find What:-
- In the find what text box please enter “\xa0“
- Search Modes:-
- Normal
- ( Matches will not be found )
- Extended
- ( Matches are found )
- Regular Expression
- ( Matches are found )
- Normal
- Count Button
- Access the Count Button to initiate the counting
- Find What:-
- In Find Dialog
Image
Explanation
- We counted 5 matches
Search specific character – Find/Next
Steps
- Launch Notepad++
- Load File
- Access Find Dialog
- In Find Dialog
- Find What:-
- In the find what text box please enter “\xa0“
- Search Modes:-
- Normal
- ( Matches will not be found )
- Extended
- ( Matches are found )
- Regular Expression
- ( Matches are found )
- Normal
- “Find Next” Button
- Access the “Find Next ” Button to initiate the counting
- Find What:-
- In Find Dialog
Image
Explanation
- We were able to find the instances where the special character was in use
Search specific character – Replace
Steps
- Launch Notepad++
- Load File
- Access Find Dialog
- In the Find Dialog, please access the “Replace” tab
- Find What:-
- In the find what text box please enter “\xa0“
- Replace What:-
- In the replace with text box, please enter the a single space bar or multiple space bars
- Search Modes:-
- Normal
- ( Matches will not be found )
- Extended
- ( Matches are found )
- Regular Expression
- ( Matches are found )
- Normal
- “Replace All” Button
- Access the “Replace All” Button to replace all “hard spaces“
- Find What:-
- In the Find Dialog, please access the “Replace” tab
Image – Find Replace – Initial
Image – Find Replace – Post Changes
Explanation
- We were able to replace all the hard spaces
Test Environment
This is a constructed test.
Here are my pre-requisites.
- SQL Server Version
- Reproducible
- SQL Server v2005
- SQL Server v2008 R2
- Remediated
-
- Microsoft SQL Server 2012 (SP1)
-
- Reproducible
- File Encoding
- UTF-8
Source Code
GitHub
Gist
sqlServer – file – unicode – contains – nobreakspace
Link
GitLab
sqlServer – file – unicode – contains – nobreakspace
Link
Referenced Work
- La De Du
- How to Enter a No-Break Space on Windows
Link
- How to Enter a No-Break Space on Windows
- soscisurvey.de
- Tools
- View Characters
Link
- View Characters
- Tools
- FileFormatInfo
- Unicode Character ‘NO-BREAK SPACE’ (U+00A0)
Link
- Unicode Character ‘NO-BREAK SPACE’ (U+00A0)
- Notepad++
- GitHub
- “non-breaking spaces” are invisible even with “Show All Characters” #5783
Link
- “non-breaking spaces” are invisible even with “Show All Characters” #5783
- community.notepad-plus-plus
- Show No Break Space
Link - How to change all invalid characters to spaces
Link - Invisible Spaces
Link
- Show No Break Space
- GitHub
- Scintilla
- #974 Display of “special” whitespaces
Link
- #974 Display of “special” whitespaces
- David Klempfner
- The Zero Width Space
Link
- The Zero Width Space
- IT Support Guides
- Classic Editor / TinyMCE – How to automatically insert Non-Breaking Space using Ctrl + Shift + Space
Link
- Classic Editor / TinyMCE – How to automatically insert Non-Breaking Space using Ctrl + Shift + Space