I've got a script saved as a text file that builds approx 20 tables and 100 stored procedures. What I want to do is execute the script file as one rather than write each CREATE TABLE statement as an ado command.
My text file is about 4000 lines long and so I want to open the text file and execute the contents. Is it just a case of reading each line of the text file and executing them in turn?
All the web hosting sites allow you to create a database script and upload the text file. It then runs the statements in the script and builds the tables etc. Thats what I'm after.
Maybe you should post this question in the SQL Server forum. It seems to me perhaps you could put your textfile into a stored procedure, and then execute the stored procedure.... but that's just a guess.
You open and read the file using the file system object. You can then build sql statements based on what your have read and execute them using ADO.
The problem would be identifying the different indivudal statements. I suspect that within your sql script file you have go's between the various statements, if this is the case you can break you commands up using that...
Read the file, if the line does not say "go" then add it to the previous ADO Command text. If it does say "go" execute the command and upon successful execution wipe the contents of the command text and go back to the start.....