📋

Description

SQL Server error 1035 indicates a syntax error in your SQL query. This error arises when the parser encounters an unexpected token or a missing required element at a particular location in the code.

💬

Error Message

Incorrect syntax near '%.*ls', expected '%.*ls'.

🔍

Known Causes

4 known causes

⚠️

Typographical Error

A simple typo in a keyword, table name, or column name can lead to syntax errors. Double-check all spellings and capitalization.

⚠️

Missing Keyword

Forgetting a required keyword like 'FROM', 'WHERE', 'ORDER BY', or 'JOIN' will result in a syntax error. Review your query structure.

⚠️

Incorrect Operator

Using the wrong operator (e.g., '=' instead of 'LIKE', or '+' instead of '||' for string concatenation) can trigger this error. Verify operator usage.

⚠️

Mismatched Parentheses

Unbalanced or incorrectly placed parentheses can confuse the SQL parser. Ensure that all parentheses are properly matched and nested.

🛠️

Solutions

3 solutions available

1. Identify and Correct Typographical Errors easy

The most common cause of SQL Error 1035 is a simple typo in the SQL statement.

1

Carefully review the SQL statement that is causing the error. Pay close attention to keywords, identifiers (table names, column names, variable names), and punctuation.

2

Look for missing or extra characters, misplaced commas, incorrect parentheses, or misspelled keywords (e.g., 'SELECT' instead of 'SELECT', 'WHERE' instead of 'WHERE').

3

Compare the syntax with known correct SQL Server syntax for the operation you are trying to perform. If you are using dynamic SQL, ensure that the generated string is syntactically correct before execution.

4

If you are using variables in your query, ensure they are declared correctly and that their values are being substituted as expected. For example, if you are using `%.*ls` format specifiers in dynamic SQL, ensure you are providing the correct data types and values.

-- Example of incorrect dynamic SQL causing syntax error
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM MyTable WHERE ColumnName LIKE ''%'' + @SearchTerm + ''%''';
-- EXEC(@sql); -- This might cause issues if @SearchTerm is not properly handled

-- Corrected example (assuming @SearchTerm is a string)
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM MyTable WHERE ColumnName LIKE ''%'' + @SearchTerm + ''%''';
DECLARE @SearchTerm NVARCHAR(100) = 'somevalue';
EXEC sp_executesql @sql, N'@SearchTerm NVARCHAR(100)', @SearchTerm = @SearchTerm;

2. Validate Dynamic SQL Statement Construction medium

Ensure that dynamically generated SQL strings are correctly formed and properly escaped.

1

If the error occurs within dynamic SQL (e.g., using `EXEC` or `sp_executesql`), print or log the generated SQL string before execution.

PRINT @YourDynamicSqlVariable;

2

Examine the printed SQL string for any syntax errors, especially around string concatenation, quoting, and special characters. Pay close attention to how variables are being embedded.

3

Use `sp_executesql` for executing dynamic SQL. This stored procedure allows you to pass parameters, which helps prevent SQL injection and can simplify string construction. Ensure that the parameter definitions in `sp_executesql` match the data types of the variables being passed.

DECLARE @SQL NVARCHAR(MAX);
DECLARE @ParamDefinition NVARCHAR(500);
DECLARE @ParamValue INT = 10;

SET @SQL = N'SELECT * FROM YourTable WHERE YourColumn = @ParamValue';
SET @ParamDefinition = N'@ParamValue INT';

EXEC sp_executesql @SQL, @ParamDefinition, @ParamValue = @ParamValue;

4

When concatenating strings for dynamic SQL, be mindful of single quotes. If a literal single quote is needed within the string, it must be escaped by doubling it (e.g., `''`).

-- Incorrect: Trying to include a literal single quote
-- DECLARE @Name NVARCHAR(100) = 'O''Malley';
-- DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Customers WHERE Name = ''' + @Name + '''';

-- Correct: Doubling single quotes for literal single quotes
DECLARE @Name NVARCHAR(100) = 'O''Malley';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Customers WHERE Name = ''' + @Name + ''''; -- This is still potentially problematic if @Name contains single quotes

-- Best practice using sp_executesql for safety and clarity:
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ParamDefinition NVARCHAR(500);
DECLARE @Name NVARCHAR(100) = 'O''Malley';

SET @SQL = N'SELECT * FROM Customers WHERE Name = @CustomerName';
SET @ParamDefinition = N'@CustomerName NVARCHAR(100)';

EXEC sp_executesql @SQL, @ParamDefinition, @CustomerName = @Name;

3. Review Object Names and Data Types easy

Ensure that table names, column names, and data types used in the query are correct.

1

Verify that all table and column names referenced in the SQL statement exist and are spelled correctly in the database schema.

SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName';

2

Check that the data types of the columns being compared or manipulated are compatible. For example, attempting to compare a string to a numeric column without proper conversion can lead to syntax errors.

SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName';

3

If you are using functions that expect specific data types (e.g., `CONVERT`, `CAST`), ensure that the source data type and the target data type are correctly specified.

-- Example of a potential data type mismatch leading to syntax error
-- SELECT CONVERT(INT, 'abc'); -- This will cause a conversion error, not necessarily 1035, but illustrates data type issues.

-- Ensure correct usage of CONVERT/CAST
SELECT CONVERT(VARCHAR(50), YourNumericColumn) FROM YourTable;

SQL Error 1035: Syntax Error