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