Q: I've downloaded and installed SQL Code Guard. Now what?
A: Now start SSMS and you'll see new top-level menu, 3 new buttons in toolbar and new submenu in the popup menu on Object Explorer.
Also you can start VS2010 and see 2 new menu items under Tools menu
Q: After installing of SQL Code Guard SSMS starts too slow. Can I solve this problem?
A: To be short: you should add <generatePublisherEvidence enabled="false" /> to SSMS config file. Sorry. But this is how .Net works.
<?xml version="1.0" encoding="utf-8"?> <configuration> <runtime> <generatePublisherEvidence enabled="false" /> </runtime> </configuration>
You can read more at Mark Russinovich's blog
SQL Server 2005: %PROGRAMFILES%\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe.config
SQL Server 2008r2: %PROGRAMFILES%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe.config
SQL Server 2012: %PROGRAMFILES%\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe.config
SQL Server 2014: %PROGRAMFILES%\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe.config
%PROGRAMFILES% usually "C:\Program Files" for x86 systems and "C:\Program Files (x86)" for x64 systems
Q: I've installed SQL Code Guard but I'm unable to find SQL Code Guard Checkin Policy and have no idea how to use it
For more information please read Using SQL Code Guard TFS Checkin Policy with VS2013
Q: Where I can find settings file used by SSMS?
Q: How to use SQL Code Guard with Visual Studio - either as Add In or as Checkin Policy?
A: Place SQL Code Guard settings file (sqlcodeguard.settings - preffered name or sqlcodeguard.xml - old-style name) in root folder of your project.
If you use TFS Checkin Policy then do not forget to add settings file to your TFS project.
You can have multiple settings files - one per each folder. The nearest one will be used. If no settings file is found in the same directory as the script file being analyzed then directory structure will be traversed up to the root of path. If settings file is still not found then default settings will be used.
Q: How to create or edit SQL Code Guard settings file used with TFS Checkin Policy or VS addin?
A: Use Settings Editor (SqlCodeGuard.SettingsEditor.exe) - it can be found in SQL Code Guard installation folder
Usage: SqlCodeGuard.SettingsEditor.exe <path to settings file> If you specify file that does not exist then new one will be created.
Q: What is "Default settings"?
Q: How to use SQL Code Guard with msbuild?
A: Sample msbuild project can be found in SQL Code Guard installation folder (SampleProject.msbuild)
SourcePath: directory that contains scripts to check. Directory will be processed recursively, all *.sql files will be checked.
IncludeIssue: semocolon-separated list of issues to find.
ExcludeIssue: semicolon-separated list of issues to exclude from analyze.
OutFile: name of output file. If specified then xml file with all reported issues will be created.
Quiet: boolean. If "true" then only total count of issues per each file will be printed otherwise each issue will be reported.
TreatWarningsAsErrors: boolean. If "true" all found issues will be marker as "error" otherwise as "warning".
TreatIssueAsError: string. Semicolon-separated list of issue codes which will be always marked as "error".
TreatIssueAsWarning:string. Semicolon-separated list of issue codes which will be always marked as "warning".
ExcludeFolder:string. Semicolon-separated list of subfolders to be exclude from analyse. E.g. "SSDT;Debug;Misc\trash;"
If msbuild works too slow you may need to add <generatePublisherEvidence enabled="false" /> to msbuild.config as described above
Q: What are the magic pink numbers you can see in Outline window?
A: These are Code Complexity numbers. Read more at Complexity. How it is calculated.
Q: [ST009] "Avoid using GOTO to improve readability". But we have corporate template for error handling which uses labels with standard names ("Finish", "ExitWithError", "Cleanup" and so on). How can we skip check for these labels?
A: You can include allowed label names to ST009AllowedLabels list.
Q: What other lists of exceptions exist?
A: BP017AllowedTables, BP018AllowedTables, ST011AllowedTables, ST012AllowedTables for "[BP017] DELETE statement without WHERE clause", "[BP018] UPDATE statement without WHERE clause", "[ST011] Consider using table variable instead of temporary table" and "[ST012] Consider using temporary table instead of table variable" respectivelyAttention please! These names should be provided in form of regular expression supported by Regexp class. So if you want to exclude table with name that starts with "z" you should write "^z.*" (without quotes, of course). Here is Regular Expression Language - Quick Reference
Q: I've got error [CGUNP] "Unparsed SQL". What happened?
A: Well, nobody's perfect. Sometime you can write SQL code that we unable to parse.
There are two cases:
1. You used some reserved keyword as identifier's name, something like select * from Sales where sum> $1000
Simply quote such identifier: select * from Sales where [sum]> $1000. Also let me know about this word so I'll take care about it.
2. You used syntax about which we've forgot/never knew/handled incorrectly. Worst case scenario. You cannot correct this error by yourself, sorry
But if you send us script/code which produce "Unparsed SQL" we'll include fix in the nearest possible build.
Btw, use "Copy unparsed objects" menu command from popup menu, Luke!
Q: how to use command line utility?
A: Command line utility (SqlCodeGuard.Cmd.exe) can analyze both existing database and folder with scripts files and supports following parameters:
-server, -s server name to connect
-database, -d database name to analyze
-user, -u user name. If no user specified then windows authentication will be used.
-password, -p user password
-outfile, -out file name to store result of analyze, xml.
-source <path> path to file or folder with scripts (*.sql) to analyze
-config, -c settings file to use
-quiet, -q silent mode, minimal messages
-log <file name>log file name
-include <semicolon separated list of issues code>
-exclude <semicolon separated list of issues code>
Pay attentiion: do not forget to specify issues to include to check, by default no issues will be detected. Simply add "-include all" command line switch.
SqlCodeGuard.cmd.exe -s "localhost" -d AdventureWorks2014 -out "result.xml" -include all
checks database AdventureWorks2014 on localhost instance for ALL issues writing found issues to result.xml
Q: How to use scgallow and scgignore commands?
A: You can allow or suppress any issue in your t-sql script
Simply use "--# scgignore(
Of course you cannot allow enable issues that are not selected in options dialog.
Please take a note - you can use only single line comment with #. Not simple single line comment, not multiline comment, only "--#".
-- asterisk in select list (BP005) will be ignored
select * from sys.objects
-- asterisk in select list (BP005) will be raised
select * from sys.objects
Q: I've got an error!
A: We apologise for inconvience. Please send us a bugreport.
When sending bugreport please include:
1. Version of SQL Code Guard (can be found at About window)
2. Version of used SSMS and version of each installed SSMS (if you have more than one SSMS)
3. Version of used SQL Server
4. Version of used OS
5. User class under which SQL Code Guard crashed - regular user or admin.
6. Content of log files. Log files can be found in %APPDATA%\SqlCodeGuard\logs folder
7. Steps to reproduce bug
8. Expected behavior - what should happen if the bug was fixed.
9. Actual behavior - what currently happens when the bug is present.
10. Content of file/query being analyzed (if any)No information from bug report will be disclosed or used in any other purposes than for fixing bug.