[AccessD] A2003: Deploying Stored Procs to SQL Server

Darren, if you can copy the .sql files to the various servers, you might be
able to create a sproc on each of the servers that takes input parameters:

EXEC xp_cmdshell 'sqlcmd -S ' + @YourServerName + ' -d ' + @YourDataBaseName
+ ' -i ' + @YourFilePathAndName


Modify your Passthrough QueryDef then call the sproc from Access/VBA in
each of the servers


On Thu, May 29, 2014 at 7:18 PM, Darren <darren at activebilling.com.au> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>