[AccessD] A2003: Deploying Stored Procs to SQL Server

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



More information about the AccessD mailing list