Arthur Fuller
fuller.artful at gmail.com
Thu May 22 04:49:44 CDT 2014
Darren, I confess that I've never tried to "roll my own" for a task like this. There are several tools available from various vendors for just this sort of task. I'm a big fan of Red Gate's toolbelt, which includes a pair of related tools: SQL Compare and SQL Data Compare. Both take a source and target database and compare the two, optionally updating the target to match the source. The Red Gate stuff is available in a trial version. Another vendor offering similar tools is Apex, and there are others as well. Arthur On Thu, May 22, 2014 at 2:02 AM, Darren <darren at activebilling.com.au> wrote: > Hi Team > > A colleague asked if I know a way to deploy a 'saved' StoredProcedure, in > an > SQL file, to many SQL Server dbs (About 20 dBs) > > The MS command line tool for doing this (I am told) strips the nice > formatting & indents etc. and makes the resulting deployed SPROC had to > read. > > So I said Yep, I can do that in Access. > > So, via code, I thought I'd read in the contents of the SQL File as I would > a stream from a text file. > > Then I would loop through a predefined list of the destination Databases > and > their Servers and would create a huge Pass-through Update Query with a Use > DB at the beginning of each desired insert then simply run the Pass-through > query from Access. > > > > (FYI - I do this with something much simpler - IE build and deploy SPROCS > from VBA. Except I don't read in the SQL first - It's hardcoded in my > Access > App for my little job) > > > > Anyway I run into issues almost straight away with "GO" statements by > having > them in the Pass-through Update Query - Because for one reason or another > these cause the running of the Pass-through update to fail. > > So what I could do is open a query in Query Analyser to each of the > destination dBs and have him run 15-20 queries. > > Or I can put my huge big query into one Query window and let him run the > one > big query. > > > > What do you guys do for this kind of thing and what approaches might your > suggest for this scenario? > > Happy to explain it better if you want J > > > > Darren > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Arthur