[AccessD] A2003: Deploying Stored Procs to SQL Server

Darren darren at activebilling.com.au
Sun Jun 1 20:34:22 CDT 2014


Hi David,
Many thanks for your reply.
I have been told (Haven't tested for myself) that the SQLCMD command line
tool works as advertised, but strips all the nice indenting etc., built into
any SPROC in the *.SQL file.
Thus the SPROC is very difficult to read from a human point of view. Hence
my attempt to perform this task for my colleague.
So 2 issues
1    Deploying it against many dbs across mostly one but sometimes a couple
of SQL Servers (I can do that bit no prob)
2    Have the SPROC inserted into the destination dB with formatting intact
(My passthrough fails on the first "GO" statement in any SPROC not sure why
and I am told the SQLCMD tool strips indenting etc.))
With the SQLCMD striping formatting (Indents etc.), has this been your
experience?
D

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Saturday, 31 May 2014 4:32 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
>>
>
>
--
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