Programmatically parse sql script in sqlcmd mode

Question

Hi,

I need to parser sql script containing both t–sql and SqlCmd syntax.

Currently,I’m using TSql100Parser class ( Microsoft.Data.Schema.ScriptDom.Sql)  to parse the file content. But the parser raise an error whenever it found sqlcmd instruction (ie:  :setvar, :on error etc…).

My question is: is there a way to programmatically parse script file in sqlcmd mode in the same way SSMS does (Menu Query>SqlCmd mode) , and still have as output a script DOM  as TSql100Parser returns?

Alternative solution are also welcome.

Thanks

Answer 1

Hi Hamza Essounni,

Regarding to your requirement, the SQL Server Management Studio Query Editor allows editing and running SQLCMD scripts.
For the solution step by step, please refer to Blog: Using the SSMS Query Editor in SQLCMD Mode

Additional , you can read this Blog: How it Works: SQL Command Mode Parsing 

---

Regards, Amber zhang

Answer 2

Hi Amber,

My question is not how to use SSMS with sqlcmd mode,but my question is about programmatically parsing sql script containing sqlcmd syntax in the same manner SSMS does.

However, thank you for your response

Answer 3

hi check out this for your reference.

http://www.bigresource.com/MS_SQL-Programmatically-Generate-SQL-Scripts-UxVThxJN.html

Answer 4

Sorry but the link you have sent has no relation with my question (it 's about script generation not script parsing!)

Thanks

Answer 5

Finally I have the answer of my own question:

There is no built-in function that does this,we have to implement our own pre-processing logic and pass a valid t-sql file to the TSql100Parser.

That is it!