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.

Improve this question
11
  • 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" Commented Aug 8, 2013 at 0: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. Commented Aug 8, 2013 at 1: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. Commented Aug 8, 2013 at 3:23
  • 1
    And even worse, GO itself is not persistent. You can e.g. configure your own batch terminator in Management Studio, so you can get your script generated with GOJOHNNYGO separator e.g. Commented Aug 8, 2013 at 4:00
  • 1
    possible duplicate of How to run a database script file from Delphi? Commented Aug 8, 2013 at 6:50

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hopefully the GO's are on a line by themselves...But only comments are allowed on the same line (see msdn.microsoft.com/en-us/library/ms188037.aspx). You would also have to handle GO within a /* block-comment */
@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.
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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.