[AccessD] A2003: Deploying Stored Procs to SQL Server

Darren, another way using a bat file:

Another approach would be to create a .BAT file with the following command:

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause

Place this .BAT file in the directory from which you want the .SQL files to
be executed, double click the .BAT file and you are done!



Source:

http://stackoverflow.com/questions/2583517/run-all-sql-files-in-a-directory




On Fri, May 30, 2014 at 11:24 AM, David McAfee <davidmcafee at gmail.com>
wrote:

> 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
>>
>
>