[AccessD] A2003: Deploying Stored Procs to SQL Server

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