How to Validate SQL Syntax Before Execution
Posted on 2004-09-25
I have a table of data that contains many different "where clauses". I run those "where clauses" in a DTS package each night. The problem is that for some reason a few of the "where clauses" are corrupt and causing my step in the DTS to fail when it tries to execute that statement after it has been dynamically built. My question is "how do I validate the SQL syntax in the statement before the execution. I would like to pass a string (sql statement) to some object and get back some type of "valid/not valid" check before I actually execute each statement. I found something that looks close, but I am not using it properly.
objDatabase.ExecuteImmediate(Script, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_ParseOnly, Script.Length)
For testing purpose lets just say I should be able to send it one of two strings:
SELECT TOP 100 FROM tblUsers where LName = ('anderson')
SELECT TOP 100 FROM tblUsers where LName = (anderson)
Any help would be greatly appreciated.