How can I programmatically check (parse) the validity of a TSQL statement?

Question

I'm trying to make my integration tests more idempotent. One idea was to execute rollback after every test, the other idea was to some how programatically parse the text, similar to the green check box in Query Analyzer or SSMS.

How do I get SQL Server to parse my command without running 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;";
    executeOnly.Connection.Open();
    executeOnly.ExecuteNonQuery();
}
//set more properties of command.
command.Execute();

For inexplicable reasons, "SET PARSEONLY ON" only worked in Query Analyzer. I couldn't set this on an ADO.NET connection. It is just as well because PARSEONLY seems to catch only syntax errors, which isn't a common error. SET NOEXEC ON will catch a wider varieties of errors, such as a view that references 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 queries will be parsed 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

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

Answer 3

Use the following query

SET PARSEONLY ON
--Your query here
SET PARSEONLY OFF

Answer 4

+1 to Eric's answer. But I've found SET FMTONLY ON to also be useful as SET NOEXEC ON doesn't appear to throw up all errors.

e.g.

SELECT * FROM ATableThatDoesNotExist

Running that with SET NOEXEC ON says it was successful, despite the table not existing in the database. Running it with SET FMTONLY ON instead, will throw the "Invalid object name" error.

SET FMTONLY ON also returns metadata about the resultset that would be returned, which can come in very handy

Answer 5

Really it depends upon the purpose of the tests.

The most reliable way would be to use the rollback after every test if your statements lend themselves to that (aren't too heavy weight to make it viable).

I have done this in the past and have been glad to be notified of runtime issues that I wouldn't have caught any other way.

Answer 6

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