Validating SQL statement before execution

Hi all,

I'm new to C# and SQL and I have a question. Is there a way to validate a SQL statement before actually executing it. I have searched for various tools online, but have not found one that enables me to do that programatically.

Example: statement 1 - "Select * From Table1"

statement2 - "Selec * From Table1",

I want to be able to know ahead of time that statement 2 is invalid before I execute that statement(don't want to use the try and catch). I was hoping I don't have to write my owner validator to do that. Thanks ahead for any help.

David

You can use SQL-DMO for that:

// Add a reference to sqldmo.dll (in the MS SQL Server X.0 Binn directory).

SQLDMO.SQLServerClass sqlServer = new SQLDMO.SQLServerClass();
try
{
String strSQLToParse = "SELECT Ouch Invalid SQL!";
sqlServer.Connect("MYSERVERNAME", "sa", "password");
SQLDMO._Database db =
sqlServer.Databases.Item("MyDatabase", "dbo");
try
{
db.ExecuteImmediate(strSQLToParse, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_ParseOnly, null);
}
catch (Exception e)
{
// Parse error...
}
}
catch (Exception err)
{

}
finally
{
sqlServer.Close();
sqlServer = null;
}

Thank you for the info, I google some info on that, but the thing is, how do I do it programatically. I can verify the statement inside the SQL Server Management program using the Parse method, but I need to do it at run time inside my c# program. I also looked on MSDN for SQL-DMO. Can you tell me any particular categories I should look for within SQL-DMO? Thank you again for your response
The preceding code is runtime code. It will do what you want to: parse T-SQL statements on the fly. The preceding code is programmatic code. It's C# code that runs inside of a runtime environment.

Hi,

Thanks for your help. I haven't had a chance to test that out. But I will definitely try that.

Another option is to set NOEXEC ON directly on T-SQL.

Ex:

cmd.CommandText = "SET NOEXEC ON;SELECT * FROM SomeTableThatDoesNotExist";

--VV [MS]