Running SQL script through VB windows installer

[Guest]

Hi all,

I have a SQL script(pretty big in size) generated by Enterprise manager.
I need to run that script through VB to install database schema on my
database.
It includes creation of tables, users and stored procedures.

I have written a VB program, which reads the script from a text file and
use Command.executeNonQuery.
I have the program ready, but with problems :

1. If I incluide the SQL script as it is,
It throws exception : Incorrect syntax near 'GO'

2. If I exlude all 'GO's from the script,
It creates the tables and users successfully but throws exception :
Stored Procedures have to be on top in batch query.

3. If I move the procedures to the top, It throws exception at second
porcedure saying : Invalid varable or invalid sytax near procedure .

4. If I move all stored procedures to individual txt files, it works.

Now, as the acript is huge and application is still under development, I
want to work with a single file which i just generate from enterprise
manager.

Is it possible ???

Thnx in advance

 

[John Wadie]

I had this situation before, the only solution I found was to replace any
occurence of 'GO' with a semicolon, and before executing the command, I
split the script on semicolons to generate a string array, then loop over
the array and execute a command for each string in the array.

Regards,
John Wadie