How can I programmatically check (analyze) the correctness of the TSQL expression?

Question

I am trying to make my integration tests more idempotent. One idea was to roll back after each test, another idea was how programmatic parsing of the text is similar to the green flag in Query Analyzer or SSMS.

How do I get SQL Server to parse my command without using it using ADO.NET?

UPDATE: This is what finally worked as desired:

using (DbCommand executeOnly = Factory.DbCommand()) { executeOnly.Connection = command.Connection; executeOnly.CommandType = CommandType.Text; executeOnly.CommandText = "SET NOEXEC ON;" + sqlCommand; executeOnly.Connection.Open(); executeOnly.ExecuteNonQuery(); } //set more properties of command. command.Execute(); 

For inexplicable reasons, " SET PARSEONLY ON " only worked in Query Analyzer. I could not install this on an ADO.NET connection. This is also good, because PARSEONLY seems to catch only syntax errors, which is not a common error. SET NOEXEC ON will cover wider error options, such as a view that refers to a missing table or column, or a missing parameter in a stored procedure.

Answer 1

I think the command you are looking for is SET NOEXEC ON . If you set this for your connection, the requests will be analyzed, but will not be executed. Another option would be SET PARSEONLY ON , but I'm honestly not sure what the difference between the two really is.

Answer 2

+1 Eric's answer. But I found that SET FMTONLY ON also useful as SET NOEXEC ON does not display all errors.

eg.

 SELECT * FROM ATableThatDoesNotExist 

Starting with SET NOEXEC ON says it was successful, even though the table does not exist in the database. Running it with SET FMTONLY ON will throw an "Invalid object name" error.

SET FMTONLY ON also returns metadata about the returned result set, which can be very convenient.

Answer 3

SQL Server 2012 can analyze your syntax, procedures, and tables with the following system procedures and functions:

• sp_describe_first_result_set (Transact-SQL)
• sp_describe_undeclared_parameters (Transact-SQL)
• sys.dm_exec_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)

They supposedly replace "SET FMTONLY".

I tested them and they work much better than "SET NOEXEC ON" and "SET PARSEONLY ON"

Examples:

There will be no error:

 sp_describe_undeclared_parameters @tsql = N'SELECT object_id, name, type_desc FROM sys.indexes;' 

It will correctly throw an error ("SET NOEXEC" and "SET PARSEONLY" in this case do not give an error):

 sp_describe_undeclared_parameters @tsql = N'SELECT object_id, name, type_desc FROM sys.indexes;SELECT object_id, name, type_desc FROM sys.NOTaTABLE;' 

Answer 4

Use the following query

 SET PARSEONLY ON --Your query here SET PARSEONLY OFF 

Answer 5

SET PARSEONLY : Considers the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement.

Answer 6

Indeed, it depends on the purpose of the tests.

The most reliable way is to use a rollback after each test if your statements lend themselves to this (not too heavy weight to make it viable).

I have done this in the past and was happy to receive notification of run-time problems that I would not have caught otherwise.

Answer 7

VSTSDBPro has a query analyzer that you can access programmatically: http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx