📋

Description

SQL Server Error 105 indicates an unclosed quotation mark within a SQL statement. This typically occurs when a string literal is not properly terminated with a matching single quote (').

💬

Error Message

Unclosed quotation mark after the character string '%.*ls'.

🔍

Known Causes

4 known causes

⚠️

Missing Closing Quote

The most common cause is simply forgetting to close a string literal with a single quote.

⚠️

Escaped Quotes

Incorrectly escaping single quotes within a string can prevent the parser from recognizing the end of the string.

⚠️

Dynamic SQL Issues

When building SQL statements dynamically, it's easy to introduce errors in quote placement.

⚠️

Copy/Paste Errors

Copying and pasting SQL statements from external sources can sometimes introduce hidden characters or incomplete strings.

🛠️

Solutions

4 solutions available

1. Locate and Correct the Missing Quotation Mark easy

Manually inspect the SQL query for an unclosed single quote.

1

Identify the SQL statement that is causing the error. This is usually the statement that was most recently executed or is part of a stored procedure or batch.

2

Carefully examine the text of your SQL query, paying close attention to string literals enclosed in single quotes ('). Look for any single quote that does not have a corresponding closing single quote.

SELECT * FROM MyTable WHERE Name = 'John Doe

3

Add the missing closing single quote to the end of the string literal. For the example above, it would become: `SELECT * FROM MyTable WHERE Name = 'John Doe';`

SELECT * FROM MyTable WHERE Name = 'John Doe';

4

Re-execute the corrected SQL statement.

2. Escape Single Quotes Within String Literals easy

Use a second single quote to represent a literal single quote within a string.

1

When a string literal needs to contain a single quote character itself (e.g., 'O'Malley'), you must escape it by doubling it.

SELECT * FROM MyTable WHERE Description = 'It''s a good day';

2

Review your query for any string literals that contain single quotes. If a single quote is intended to be part of the string, replace it with two single quotes ('').

3

Re-execute the corrected SQL statement.

3. Verify Dynamic SQL Generation medium

Inspect the dynamically generated SQL string before execution.

1

If you are constructing SQL statements dynamically (e.g., using `EXEC` or `sp_executesql`), ensure that the string being built correctly handles all quotation marks and escape characters.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM MyTable WHERE Name = ''' + @UserName + '''';
EXEC sp_executesql @SQL;

2

Use `PRINT` statements or a debugger to view the final generated SQL string before it is executed. This will help you spot any unclosed or incorrectly escaped quotation marks.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM MyTable WHERE Name = ''' + @UserName + '''';
PRINT @SQL; -- Inspect this output
EXEC sp_executesql @SQL;

3

Adjust the string concatenation or formatting within your dynamic SQL generation to ensure all string literals are properly terminated and escaped.

4. Review Application Code for String Formatting medium

Check how your application code is building and sending SQL queries to the database.

1

If the SQL statement is being generated by an application (e.g., C#, Java, Python), examine the code responsible for constructing the query. Look for errors in string manipulation or concatenation that might lead to unclosed quotation marks.

2

Pay particular attention to how user-provided input is being incorporated into SQL queries. Using parameterized queries is the most robust way to prevent this type of error and SQL injection vulnerabilities.

SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE Username = @Username", connection);
cmd.Parameters.AddWithValue("@Username", userNameFromInput);

3

If not using parameterized queries, ensure that any single quotes within input values are properly escaped (e.g., by doubling them) before being concatenated into the SQL string.