How do I execute an Ascii-formatted script from my code?
-
I want to create some database objects (tables, stored procedures etc) from a TSQL script. The script is stored in an ASCII txt file, and was generated using the MS SQL Manager's "Generate SQL script" option. This is my problem : the script file contains formatting (line breaks, tabs etc), TSQL comments and so forth. If I simply load the contents of the script file into a string variable, and execute this from my C# code the way I would any ad-hoc SQL command, the ASCII-text formatting breaks the SQL. For example, "SELECT * FROM MyTable;" now becomes "SELECT *\r\nFROM MyTable;" This is not valid SQL, and an exception is thrown. If I parse out all the ASCII formatting (and TSQL comments etc), I can execute a very large TSQL script file as an adhoc SQL command with no errors. This is great, except that my stored procedures now lose all their nice formatting and comments, making them difficult to read. So ... is there some way to execute an ASCII-formatted TSQL script from C# code without having to strip all the ASCII formatting out? Thanks in advance!
-
I want to create some database objects (tables, stored procedures etc) from a TSQL script. The script is stored in an ASCII txt file, and was generated using the MS SQL Manager's "Generate SQL script" option. This is my problem : the script file contains formatting (line breaks, tabs etc), TSQL comments and so forth. If I simply load the contents of the script file into a string variable, and execute this from my C# code the way I would any ad-hoc SQL command, the ASCII-text formatting breaks the SQL. For example, "SELECT * FROM MyTable;" now becomes "SELECT *\r\nFROM MyTable;" This is not valid SQL, and an exception is thrown. If I parse out all the ASCII formatting (and TSQL comments etc), I can execute a very large TSQL script file as an adhoc SQL command with no errors. This is great, except that my stored procedures now lose all their nice formatting and comments, making them difficult to read. So ... is there some way to execute an ASCII-formatted TSQL script from C# code without having to strip all the ASCII formatting out? Thanks in advance!
spazzman wrote:
This is not valid SQL, and an exception is thrown.
I doubt it is that because I have done that and it works perfectly. The only thing you need to parse out of the script are the GO statements as these are instructions to Query Analyzer and not SQL. The GO is a batch delimiter - In other words execute the bits between the GO statements as separate SQL commands from the .NET application.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
spazzman wrote:
This is not valid SQL, and an exception is thrown.
I doubt it is that because I have done that and it works perfectly. The only thing you need to parse out of the script are the GO statements as these are instructions to Query Analyzer and not SQL. The GO is a batch delimiter - In other words execute the bits between the GO statements as separate SQL commands from the .NET application.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog