Darren
darren at activebilling.com.au
Thu May 29 21:18:45 CDT 2014
Hi Arthur, Thanks for your comments. We won't be using 3rd party tools for this. If it can be, it will need to be written in Access/VBA. And comparing the dBs is not an issue, we just want to push already tested Stored Procedures to an editable list of dBs. Many thanks again for your reply. Anyone else with thoughts on the matter? Many thanks in adavance Darren -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Thursday, 22 May 2014 7:50 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] A2003: Deploying Stored Procs to SQL Server 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com