Incorrect syntax near sql ошибка

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.

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:

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.

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.

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:

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:

– 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:

1. Download Notepad++ from their official website and install it on your computer.
2. Open Notepad++, and click on “New file”
3. Ensure the value of the “Encoding” menu is “Encode in UTF-8”. If it’s not there, set it.
4. Copy your SQL statements that are failing in SQL Server, and paste them in Notepad++.
5. Click the “Encoding” menu again, and change it to “Encode in ANSI”.
6. Check your text again, and you’ll see the “invincible character”.
7. 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.

Hi friends, in this post, we will be discussing about how you can resolve the SQL Server CTE Error: Incorrect syntax near ‘)’.

What are Common Table Expressions (CTEs)?

In SQL Server and more specifically in T-SQL, a CTE is a temporary named result set, from which you can then select records. CTEs are temporary objects and are not stored on disk.

CTEs vs Views

Even though CTEs might sound similar to database views, there is one major difference: views are database objects that are stored physically on disk but CTEs, as mentioned above, are just temporary objects and thus they are not stored on disk.

Let’s Reproduce the Incorrect syntax near ‘)’ error

Now, about the Incorrect syntax near ‘)’ error, let’s see a relevant example, via which we can reproduce the error.

With the below T-SQL script, I’m trying to define a CTE in SQL Server:

WITH cte_Customers
AS (
   SELECT *
   FROM dbo.tblCustomers
   WHERE id>2
   )

However, when I try to execute the above script, I will get the error message:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘)’.

How to Resolve the Issue

Resolving the above issue, is very easy.

The key thing to remember when defining a CTE in SQL Server, is that in its definition, you must always include a SELECT, DELETE, INSERT or UPDATE statement, that references one or more columns returned by the CTE.

So, by continuing our example, in order to make the definition of the CTE valid, I can include for instance, a SELECT statement.

Let’s see the new example:

WITH cte_Customers
AS (
   SELECT *
   FROM dbo.tblCustomers
   WHERE id>2
   )
SELECT * FROM cte_Customers

Now, if I execute the above script, I can see that my CTE works without errors.

Learn more about SQL Server Development – Enroll to our Course!

Enroll to our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link) and sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Enroll from $12.99

Featured Online Courses:

• SQL Server 2022: What’s New – New and Enhanced Features
• Working with Python on Windows and SQL Server Databases
• Introduction to Azure Database for MySQL
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
  
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
  
• Data Management for Beginners – Main Principles
  
• A Guide on How to Start and Monetize a Successful Blog

Read Also:

• Essential SQL Server Development Tips for SQL Developers (Course Preview)
• System.IO.FileLoadException: could not load file or assembly…
• The TempDB System Database in SQL Server
• SQL Server Installation and Setup Best Practices
• The feature you are trying to use is on a network resource that is unavailable
• SQL Server 2016: TempDB Enhancements
• tempdb growth
• Introduction to SQL Server Machine Learning Services
• Essential SQL Server Administration Tips
• What are SQL Server Statistics and Where are they Stored?
• Tip of the Week No.1 – SQL Server Always Encrypted
• Tip of the Week No.3 – TempDB Settings During Installation
• Tip of the Week No.6 – About SQL Server Temporary Tables
• Tip of the Week No.19 – What is the Database First Workflow in Entity Framework?
• Tip of the Week No.20 – SQL Server Surface Area
• Within Which Context Does SQL Server Access Network Resources?
• Troubleshooting the File Activation Error in SQL Server
• SQL Server Error 262: CREATE DATABASE permission denied in database – How to Resolve
• Check all our Weekly Tips!

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

Rate this article: (2 votes, average: 5.00 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 4,605

I’m trying to execute the following stored procedure:

CREATE PROCEDURE  dbo.Compress_taille(@nom_table VARCHAR(64))                                            
AS                       
    PRINT @nom_table                                  
    declare @results table                 
    (                                                        
    TableName varchar(250),                           
    ColumnName varchar(250),                       
    DataType varchar(250),                               
    MaxLength varchar(250),                            
    Longest varchar(250),                   
    SQLText varchar(250),                  
    position float                    
    )                                   
    INSERT INTO @results(TableName,ColumnName,DataType,MaxLength,Longest,SQLText,position)                      
    SELECT                 
        Object_Name(c.object_id) as TableName,        
        c.name as ColumnName,            
        t.Name as DataType,     
        case       
        when t.Name not like  '%char%' Then 'NA'
        when c.max_length = -1 then 'Max' 
        else CAST(c.max_length as varchar)
        end as MaxLength,        
        'NA' as Longest,          
        'SELECT Max(Len([' + c.name + '])) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id) as SQLText,               
        column_id as position   
    FROM                                  
        sys.columns c    
    INNER JOIN                       
        sys.types t ON c.system_type_id = t.system_type_id  
    WHERE                 
        c.object_id = OBJECT_ID(@nom_table)               
        and    t.Name <> 'sysname'   
    order by column_id                       


    DECLARE @position varchar(36)                            
    DECLARE @sql varchar(200)                       
    declare @receiver table(theCount int)                 


    DECLARE cursor_script CURSOR                      
        FOR SELECT position, SQLText FROM @results WHERE MaxLength != 'NA'          
    OPEN cursor_script                        
    FETCH NEXT FROM cursor_script                             
    INTO @position, @sql                  
    WHILE @@FETCH_STATUS = 0                  
    BEGIN                  
        INSERT INTO @receiver (theCount)       
        exec(@sql)        

        UPDATE @results           
        SET Longest = (SELECT theCount FROM @receiver)        
        WHERE position = @position     

        DELETE FROM @receiver             

        FETCH NEXT FROM cursor_script             
        INTO @position, @sql   
    END                       
    CLOSE cursor_script                       
    DEALLOCATE cursor_script                         


    DECLARE @script_sql varchar(max)             


    set @script_sql=' create table [AQR_INF_2017T2].[dbo].'+ left(@nom_table, LEN(@nom_table)-LEN('39CR_201703')) +'39CR_201706('  

    DECLARE                             
            @TableName VARCHAR(80),
            @ColumnName VARCHAR(80),
            @DataType VARCHAR(80),
            @MaxLength VARCHAR(80),
            @Longest VARCHAR(80),
            @code_colonne VARCHAR(1000)


    DECLARE getemp_curs CURSOR                              
    FOR                  
        SELECT TableName, ColumnName, DataType,MaxLength,        
        coalesce(case when Longest='0' then '10' else Longest end ,'1') as Longest,
        position,             
        coalesce(
        case when DataType like '%numer%' then '[' + ColumnName + '] float,'
        when DataType like '%char%' then '[' + ColumnName + '] char(' + coalesce(case when Longest='0' then '10' else Longest end ,'1') + '), ' 
        else '[' +ColumnName + '] ' + DataType+ ',' end,'[' +ColumnName + '] nvarchar(1),')    
        AS code_colonne FROM @results order by position                                           


        OPEN getemp_curs      
        FETCH NEXT FROM getemp_curs into @TableName, @ColumnName, @DataType,@MaxLength,@Longest,@position,@code_colonne          
        WHILE @@FETCH_STATUS = 0 BEGIN   

        set  @script_sql=@script_sql + @code_colonne                                               
        FETCH NEXT FROM getemp_curs into @TableName, @ColumnName, @DataType,@MaxLength,@Longest,@position,@code_colonne          
        END      
    CLOSE getemp_curs                      
    DEALLOCATE getemp_curs                        

    set  @script_sql= case when  left(@script_sql,1)=',' then   left(@script_sql, LEN(@script_sql) -1) else @script_sql end  + ') '                             


    PRINT '@script_sql: ' + @script_sql                                      
    exec @script_sql

GO                         

But I when I execute this code:

DECLARE @table varchar(255)        

DECLARE cursor_test CURSOR                               
    FOR SELECT name FROM sysobjects WHERE type='U' and substring(name,1,3) not in ('T_P','T_Z','T_R','TEST_AQR')  order by name    -- SUPPRIME LES TABLES NON UTILES POUR L'INFOCENTRE AQR  
OPEN cursor_test                       
FETCH NEXT FROM cursor_test                            
INTO @table              
WHILE @@FETCH_STATUS = 0               
BEGIN
    EXEC AQR_INF_2017T2.dbo.Compress_taille @table
    FETCH NEXT FROM cursor_test          
    INTO @table    
END                       
CLOSE cursor_test                   
DEALLOCATE cursor_test

I get this error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’.

This code was working for one year and now it doesn’t. Our version control does not seem to help either, and, unfortunately, the logic does not seem straightforward to me.

One thought was about the version of SQL Server causing breaking changes, but I am not convinced.

How would I go about troubleshooting this issue? Are there any good industry practices for tracking down script issues when dynamic sql is involved?

I need to verify where the breaking code starts, not necessarily where the syntax error occurs.