SA0145 : The EOL marker sequence is not the expected {CR}{LF}

The improper use of end-of-line (EOL) characters can lead to issues when managing SQL code across different platforms and tools, affecting interpretation and execution.

Description

In T-SQL code, consistency in using the correct end-of-line character sequence is crucial for maintaining code compatibility across various systems and editing tools. SQL Server typically expects lines to end with a carriage return followed by a line feed ({CR}{LF}). This standard helps ensure that SQL code is interpreted correctly, especially when moved between different environments or when used with various tools and editors.

For example:

SQL
Copy
1-- Query with inconsistent EOL characters
2SELECT * FROM Employees -- {LF} used instead of {CR}{LF}

Using an incorrect EOL character, like {LF} alone, might lead to parsing errors or unexpected behavior in certain SQL Server environments or tools that rely on the traditional {CR}{LF} sequence. It can also complicate version control and collaboration if team members use different editors with varying EOL defaults.

  • Potential for syntax errors due to incorrect code parsing in some SQL Server contexts.

  • Increased difficulty in maintaining and collaborating on SQL code due to varied behavior across different editors and platforms.

How to fix

Ensure SQL code compatibility by standardizing the end-of-line characters to the SQL Server expected {CR}{LF} sequence.

Follow these steps to address the issue:

  1. Open the SQL file in SQL Server Management Studio (SSMS) or Visual Studio.

  2. Navigate to the file menu and select File > Save As….

  3. In the Save File As dialog, look for the Save with Encoding… dropdown button next to the Save button.

  4. Select Windows (CR LF) for the file’s line endings from the available options.

  5. Save the file to apply the correct end-of-line characters.

For example:

SQL
Copy
1-- Example of corrected EOL characters in a query
2SELECT * FROM Employees;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1SELECT *
 2FROM Table1,
 3 Table2 b,
 4 (SELECT *
 5  FROM Table4c) c
 6INNER JOIN Table3 ON Table3.Table4_id = c.id
 7INNER JOIN Table5 -- The line ends with 'r'
 8INNER JOIN Table6 ON Table6.Table5_id = Table5.id -- The line ends with 'n'
 9ON Table3.Table4_id = c.id,
10 Table8 f, -- The line ends with 'r'
11 Table9,
12 (SELECT * FROM Table7) h;
Analysis Results

No violations found.

See Also

Other Resources

Analysis Rules