3

We have an updater application which executes SQL scripts of over 30,000 lines of code on a SQL Server database. In this script is a large number of GO statements. The ADO components which I'm used to using do not support the GO statement. We've always used the OSQL command-line when updating the server, which is tricky to catch the problems (outputs a text file which we then have to parse to look for errors and results). It would be much more ideal if something in Delphi was able to find those GO statements and execute each block at a time.

I do understand that the GO statement is specific to MS tools which are able to execute them. Again the script is over 30,000 lines of code, with at least 500 GO statements. We use the same script in SQL Management Studio and OSQL for the updater. But OSQL just isn't giving us the results we need - it's a sloppy way of executing such a script.

Is there any existing SQL Server compatible tool for Delphi which recognizes GO statements? I'm not talking necessarily about a parser (although I'm sure parsing would be involved), I'm talking about a ready-made utility to execute SQL Scripts which contain GO statements and return the results of each script block.

For example, one block might create a table, and then GO, and then create a stored procedure for that table and another GO. The code is so complex that implementing it in the code like this question is quite out of the question.

UPDATE

I have successfully written a small bit of working code that does this, however it does not take into consideration a GO statement within a comment block.

Code found here

I'm not asking anything further in this question, just thought I'd share where I am with this.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
Jerry Dodge
  • 26,858
  • 31
  • 155
  • 327
  • And simply looking for the string `GO` wouldn't work either, because there's likely a string in there which is inserting/updating some text with the word "go" – Jerry Dodge Aug 08 '13 at 00:55
  • The answer, I don't know, but I would suggest putting that whole script into a single stored procedure and modifying it slightly so that the results of each operation are written to a set of dedicated temporary tables that are rewritten upon each run, such that the results of the script can be discerned by specific queries to those temporary tables. My data warehousing days taught me the value of temporary tables in lengthy and complex database operations, especially when a lot of processing is being done by the DBMS itself. Just a suggestion. HTH. – Sam Aug 08 '13 at 01:00
  • So the answer is it's up to me to decide how to execute SQL scripts. Sweet! Now I can write my own component for this. – Jerry Dodge Aug 08 '13 at 03:23
  • 1
    And even worse, `GO` itself is not persistent. You can e.g. [`configure your own batch terminator`](http://stackoverflow.com/a/2299280/960757) in Management Studio, so you can get your script generated with `GOJOHNNYGO` separator e.g. – TLama Aug 08 '13 at 04:00
  • But I still believe that proper components should have such option configurable, like e.g. AnyDAC (or FireDAC) has for its [`TADScript`](http://www.da-soft.com/anydac/docu/uADCompScript_TADScriptOptions_CommandSeparator.html) or e.g. UniDAC for its [`TDAScript`](http://www.devart.com/unidac/docs/index.html?devart_dac_tdascript_delimiter.htm). So I think that your question also depends on which components you can use. – TLama Aug 08 '13 at 04:05
  • As a follow up to TLama's comment, I'm pretty sure Unidac from Devart will allow you to execute the script. You can get a free trial version at Devart (http://www.devart.com/unidac/) to try it out. – Sam M Aug 08 '13 at 04:28
  • 1
    possible duplicate of [How to run a database script file from Delphi?](http://stackoverflow.com/questions/5985486/how-to-run-a-database-script-file-from-delphi) – Sir Rufo Aug 08 '13 at 06:50
  • @SirRufo, the accepted answer of that duplicate as well as Ken's answer from here doesn't take into account what Gerry mentioned [`here`](http://stackoverflow.com/questions/18116148/how-do-i-execute-a-large-sql-script-with-many-go-statements-from-within-delphi#comment26527106_18116725) in comment; the use of SQL comment like `/*GO*/` written on 3 lines, where just that `GO` will be the only word on a single line, but inside the script comment. – TLama Aug 08 '13 at 07:28
  • Another thing I realized along the same lines is the `USE` statement... – Jerry Dodge Aug 08 '13 at 12:04
  • The `USE` command is a common statement, `GO` is not. Anyway, just to add about those script executive components; they're also parsing script manually by themselves. – TLama Aug 08 '13 at 12:48
  • Jerry: This may help:http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-_2D00_-the-easy-way.aspx – MartynA Aug 09 '13 at 13:34

2 Answers2

4

Loading the script into a TStringList would work. looking for the word GO on a line by itself (which avoids embedded text containing GO) as the end of each block or the script itself.

You use the GO line's index - 1 to mark the end of the prior block, and the GO line index + 1 as the start of the next block. If the GO index is equal to the StringList.Count - 1, you've reached then end of the script. You can start a transaction before processing the blocks, and roll that transaction back if an exception is raised.

Ken White
  • 123,280
  • 14
  • 225
  • 444
  • 2
    Hopefully the GO's are on a line by themselves...But only comments are allowed on the same line (see http://msdn.microsoft.com/en-us/library/ms188037.aspx). You would also have to handle GO within a /* block-comment */ – Gerry Coll Aug 08 '13 at 03:52
  • @Gerry: Both good points. The first can be handled by looking at the first three characters for `GO;` specifically. The second can be handled with an `InComment` boolean flag. I never said you wouldn't have to do a little work, whether it be in setting up the script or in the code that processes it. IMO, it's still the best solution, as there are none that I've found "out of the box" that do things correctly. – Ken White Aug 08 '13 at 10:56
2

This is actually a good question, with no easy answer.

It's worth emphasizing this is a Microsoft issue, not SQL, and no Delphi.

This has been discussed many times on SO, but there's no "one size fits all" answer:

My two main suggestions:

  • Read the script file, parse it a line at a time, and run an "execute" after every line containing only "go"

  • Do a ShellExecute() (or equivalent) of SqlCmd.exe with your script and logon parameters.

Community
  • 1
  • 1
paulsm4
  • 114,292
  • 17
  • 138
  • 190