I need some help with the SQL-DMO library. I'm developing a SQL-DMO application in which I need to verify the syntax of a SQL DDL query much the same way that Query Analyzer verifies syntax when you create stored procedures and views.We aren't using Query Analyzer, but want to check the syntax of stored procedures when we create them in Enterprise Manager. For example, I'm working on a project that uses Visual Basic (VB) and SQL-DMO and includes an interface through which I can alter objects such as tables and stored procedures. If I have a text statement such as
ALTER TABLE Test ADD Test_id int
I want to be able to validate the statement's syntax by using a call such as
CALL xObject.Databases.Item(i).Exec uteImmediate(Text1.Text)
in which xObject is an instance of SQLDMO.SqlServer2 and Text1.Text contains the statement I'm checking. Is there a method or function in the SQL-DMO library that will let me perform this check?
You can turn on SET PARSEONLY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setset_47eh.asp), which checks the syntax of each T-SQL statement and returns any error messages without compiling or executing the statement. In addition, the SQLDMO_EXEC_TYPE.SQLDMOExec_ParseOnly constant (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_cnst05_7ecl.asp) supports the ExecuteImmediate SQL-DMO method, altering the execution behavior or interpretation of the statement submitted for execution.
If you're using SQL-DMO as designed, you should code the DDL in terms of setting attributes to objects (in which case you'd be protected from passing badly formed SQL in the first place). For example, you'd create a new table object and call InsertColumn, passing a column object to add a column. The SQL-DMO classes will give you an exception if you break syntax rules.
—Gert Drapers
Development Manager
Visual Studio Team System
—Patrick Conlan
Platform Program Manager
Microsoft Project Team
