"Simon Hayes" <sq*@hayes.ch> wrote in message
news:42**********@news.bluewin.ch...
<mu************@hotmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...I have a custom application that on occasion requires thousands of TSQL
files (on the file system) to be compiled to the database.
What is the quickest way to accomplish this?
We currently have a small vbs script that gets a list of all the files,
the loops around a call to "osql". each call to osql opens/closes a
connection to the destination database (currently across the network).
Since text files compress well, you could zip them up, FTP or copy them to
the server, then unzip them and run your vbs script on the server side
(using xp_cmdshell, a scheduled job, DTS etc.).
Also, are you able to reduce the number of files you run? Do you change
thousands of procedures at a time, or are you able to use your source
control system to identify only the objects which have been modified?
Simon
Ditto on the
moving operation to the server and
seeing if you trim the number of objects down.
If there are no object dependencies on order of execution,
I would look into multi-threading this operation as well.
I would write out a series of CMD file scripts calling OSQL with your
existing vb script.
And then call a master CMD script that uses START to run eacho of the
sub-CMD scripts in its own process.*
Also, make sure that you are using integrated security with OSQL as I recall
it runs faster than SQL security.
* Don't use the START before each call to OSQL, or you will end up like
mickey did in that movie with all those brooms).