CODEWITHSUNDEEP
sqlsql-serverssms
Joshua Frank
Joshua Frank

Reputation: 13838

Incorrect syntax near ''

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.

Upvotes: 58

Views: 418280

Answers (7)

Michael
Michael

Reputation: 13

After upgrading my .net project to .net 9. I had to first update visual studio and for that "Incorrect syntax near '$'." i had to run a query like: ALTER DATABASE MyDb SET COMPATIBILITY_LEVEL = 140 GO (you have to check the apropriate compatibility level for your sqlserver version)

Upvotes: -1

user132992
user132992

Reputation: 21

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.

replace me with an underscore

enter image description here

using the dot, will autopoulate but after this go back and replace the dot with an underscore

Upvotes: 1

Fatema Hasta
Fatema Hasta

Reputation: 51

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)

Upvotes: 4

IamButtman
IamButtman

Reputation: 347

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

Upvotes: 1

MAXE
MAXE

Reputation: 5122

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 enter image description here
  • From Encoding menu, now click "Encode in ANSI" and check again your text enter image description here

You should easily find the wrong character(s)

Upvotes: 26

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131364

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.

Upvotes: 114

user6269864
user6269864

Reputation:

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.

Upvotes: 3

Related Questions