KB Issue

Using SQL Server Management Studio to generate scripts with comments cannot be transferred over into a single line of code.

Issue Details

When creating your SQL Queries inside SQL Server Management Studio (SSMS) or other programs, you might add notes to yourself or to others explaining what a certain part does. These notes, called 'Comments', are surrounded by double dashes.

These comments are fine in a multi-line query, whether in SSMS or on your website.
However, they do not cross over on a single line of code.

Recreate Issue

The following example demonstrates code with notes (comments) in it.

[SQK Server - UPdate Statement in SQL Script with Comments]

CFFCS | CarrzSynEdit: | SQL Script

UPDATE TableOne
SET Col3 = CASE 
    WHEN Col3 = 3 THEN 4
    WHEN Col3 = 1 THEN 3
    WHEN Col1 = ? THEN 1 -- Parameter 1: strCol1

    ELSE Col3 
END
WHERE Col2 = ?              -- Parameter 2: strCol2

  AND (Col3 IN (1, 3) OR Col1 = ?); -- Parameter 3: strCol1

The above works as expected in SSMS.
Taking the same code above and condensing it into a single line for your query will look like this.

[Classic ASP - Badly formatted SQL String]

CFFCS | CarrzSynEdit: | ASP/VBScript

sql.commandtext="UPDATE TableOne SET Col3 = CASE WHEN Col3 = 3 THEN 4 WHEN Col3 = 1 THEN 3 WHEN col1 = ? THEN 1 -- Parameter 1: strcol1 ELSE Col3 END WHERE col2 = ? -- Parameter 2: strcol2 AND (Col3 IN (1, 3) OR col1 = ?); -- Parameter 3: strcol1"
sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)
sql.Parameters.Append sql.CreateParameter("@col2", adVarChar, adParamInput, 25, getCol2)
sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)

Resolve IssueWhat is happening with the single-line code above is that the comments are still present. Everything after the comment is now void
To fix this issue, remove the comments from your code.

[Classic ASP - Correct SQL String]

CFFCS | CarrzSynEdit: | ASP/VBScript

sql.commandtext="UUPDATE TableOne SET Col3 = CASE WHEN Col3 = 3 THEN 4 WHEN Col3 = 1 THEN 3 WHEN Col1 = ? THEN 1 ELSE Col3 END WHERE Col2 = ? AND (Col3 IN (1, 3) OR Col1 = ?)"
sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)
sql.Parameters.Append sql.CreateParameter("@col2", adVarChar, adParamInput, 25, getCol2)
sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)

Other Articles Related to this Entry.Microsoft OLE DB Provider for SQL Server (0x80040E21) Multiple-step OLE DB operation generated error«