Which Language rules should be used for Microsoft SQL

SonarQube Server / Community Build
1

Which Language rules should be used for Microsoft SQL

2

Hi Chandramouli,

SonarQube supports either PL/SQL (Oracle) or T-SQL (Microsoft) analysis. As I assume you are programming for a Microsoft SQL Server, then the T-SQL Analyser should make it. Its rules are all described here. This is supported in SonarQube Developer Edition and above.

Best regards,
Daniel

3

Thank you @Daniel_Meppiel for the confirmation.

But while analyzing the Stored Procedures using the T-SQL Rules I’m getting a few errors when we use the keywords like TRAN, PARTITION, UNION ALL but these keywords are supported by MS SQL.

Please suggest to me how to fix this issue.

Note: I’m using the Enterprise Edition Version 8.4.2

Example of Issue:

[20:01:00] [Step 5/5] WARN: Unable to parse file ‘/C:/TeamCity/BuildAgent/work/f3322e4c0be517cd/SQL/Stored Procedures/dbo.{SP_NAME}.sql’ : Parse error at line 30 column 9:
@PARAMETER1 DATETIME = NULL,*
@PARAMETER2 INT = ‘1’,*
@PARAMETER3 INT = ‘10’*
AS*
BEGIN*
SET NOCOUNT ON;*
SET TRAN ISOLATION LEVEL READ UNCOMMITTED;*
^*
DECLARE @DATETIME VARCHAR(256)=’’,@STR_QUERY VARCHAR(MAX) = ‘’;*

4

Hi,

As stated in the documentation I shared:

Important Note

With the default configuration, only files with the .tsql are analyzed as T-SQL, and files with the .sql file extension are analyzed as PL/SQL. This behavior is defined in Administration > General Settings > T-SQL > File Suffixes and Administration > General Settings > PL/SQL > File Suffixes . You can override these properties either at server level or at project level.

Therefore the parsing error is because your .sql files are picked up by the PL/SQL analyser instead of the T-SQL analyser. You will need to get this sorted as described above. For example, set the extensions of PL/SQL to .plsql and the extension of T-SQL to .sql.

Best regards,
Daniel

5

Hi @Daniel_Meppiel, I have already overridden the file suffixes for the T-SQL language and PL/SQL languages so that the T-SQL analyzer only will pic my MS SQL files.

Is there any reason for this issue?

6

Hi,

Please can you share the full debug logs of your analysis so that I can see the full context and error? Make sure to pass the “-X” flag to the scanner.

Thanks, regards,
Daniel

7

Thank you @Daniel_Meppiel for the reply.

Please find the attached Build log and help me to fix this issue.

Note: First I converted the log file to .rar file and then to .zip file as we have large(470MB) log file.

Branch_Analysis_GuidingCare_12.zip (2.5 MB)

8

Hi @cpuvvadi,

I believe there may be syntax errors in your files, not conforming to T-SQL standards.

For example, if I take the first parse error:

|[12:33:39] :| [Step 7/7] 12:33:39.469 WARN: Unable to parse file '/C:/TeamCity/BuildAgent/work/cce25063e747ed8d/AHS.GC.Sql/MainDatabase/Stored Procedures/dbo.USP_AHS_CM_PHARMACY_CARD_PRINT.sql' : Parse error at line 135 column 9:|
|---|---|
|[12:33:39] :| [Step 7/7] |
|[12:33:39] :| [Step 7/7] 125:                AND PP.CARE_TEAM_ID = 2|
|[12:33:39] :| [Step 7/7] 126:                AND PP.DELETED_BY IS NULL;|
|[12:33:39] :| [Step 7/7] 127:     WITH CTE (PATIENT_PHYSICIAN_ID)|
|[12:33:39] :| [Step 7/7] 128:     AS (SELECT TOP 1|
|[12:33:39] :| [Step 7/7] 129:             PATIENT_PHYSICIAN_ID|
|[12:33:39] :| [Step 7/7] 130:         FROM @TMP_PATIENT_PHYSICIAN|
|[12:33:39] :| [Step 7/7] 131:         WHERE ISNULL(PROVIDER_TYPE, '') <> 'PCP'|
|[12:33:39] :| [Step 7/7] 132:               AND PRIMARY_BEHAVIORAL = 1|
|[12:33:39] :| [Step 7/7] 133:               AND PATIENT_ID = @PATIENT_ID|
|[12:33:39] :| [Step 7/7] 134:         ORDER BY UPDATED_ON DESC|
|[12:33:39] :| [Step 7/7] 135:         UNION ALL|
|[12:33:39] :| [Step 7/7]              ^|
|[12:33:39] :| [Step 7/7] 136:         SELECT TOP 1|
|[12:33:39] :| [Step 7/7] 137:             PATIENT_PHYSICIAN_ID|
|[12:33:39] :| [Step 7/7] 138:         FROM @TMP_PATIENT_PHYSICIAN VPP|
|[12:33:39] :| [Step 7/7] 139:         WHERE VPP.PROVIDER_TYPE = 'PCP'|
|[12:33:39] :| [Step 7/7] 140:               AND PATIENT_ID = @PATIENT_ID|
|[12:33:39] :| [Step 7/7] 141:         ORDER BY EFFECTIVE_FROM DESC,|
|[12:33:39] :| [Step 7/7] 142:                  ISNULL(EFFECTIVE_TO, '2099/12/31') DESC,|
|[12:33:39] :| [Step 7/7] 143:                  UPDATED_ON DESC,|
|[12:33:39] :| [Step 7/7] 144:                  PATIENT_PHYSICIAN_ID DESC|
|[12:33:39] :| [Step 7/7] 145:        )|

Note that you are using UNION ALL right after an ORDER BY, and this seems to me an incorrect syntax that should trigger parse error as per Microsoft’s reference:

/* INCORRECT */  
-- Uses AdventureWorks  
  
SELECT ProductModelID, Name  
FROM Production.ProductModel  
WHERE ProductModelID NOT IN (3, 4)  
ORDER BY Name  
UNION  
SELECT ProductModelID, Name  
FROM dbo.Gloves;  
GO  
  
/* CORRECT */  
-- Uses AdventureWorks  
  
SELECT ProductModelID, Name  
FROM Production.ProductModel  
WHERE ProductModelID NOT IN (3, 4)  
UNION  
SELECT ProductModelID, Name  
FROM dbo.Gloves  
ORDER BY Name;  
GO

The ORDER BY should be used at the end of the UNION or UNION ALL.

In the second example “SET TRAN ISOLATION LEVEL”, the syntax seems to be TRANSACTION and not TRAN, as per MS Reference:

– Syntax for SQL Server and Azure SQL Database

SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}

I would encourage you to double check your syntax VS the language reference for each parsing error that you get. Please let me know if I have overseen any important detail in my analysis above.

Best regards,
Daniel