November 27, 2015 at 10:12 am
We have 100+ sql files, while deploying we want to create one master.sql and inside this master.sql we want to call all these .sql files
what is the good approach to do that in SQl Server?
Please advise. Thanks in advance.
Regards,
SQLisAwe5oMe.
November 27, 2015 at 10:43 am
Keep the files separate. It's an advantage that you'll realize later. Just make a batch file that executes the scripts in the order that you want them.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2015 at 10:56 am
Hi Jeff,
If you don't mind, can you please elaborate on it, or provide steps...
I am not that good with .bat files or scripts.
Regards,
SQLisAwe5oMe.
November 27, 2015 at 11:21 am
A batch file is nothing more than a series of commands you can execute at a command prompt. Similarly, a stored procedure is a series of T-SQL statements that are executed. Both can get involved, but the basics are the same. Without getting too fancy, batch files work at the system level and procedures work at the database level.
There are many sites out there that deal with batch files, but what you want to do it pretty simple. If you want to execute the T-SQL scripts, first determine the order and then write commands to execute them in the appropriate sequence.
The sqlcmd and osql utilities are the two options I know of to to this, but osql is targeted for termination in a future version.
November 27, 2015 at 12:04 pm
Okay Thanks Jeff, appreciates it.
Regards,
SQLisAwe5oMe.
November 27, 2015 at 2:21 pm
Is the schema same for all the databases that you would be deploying on?
November 27, 2015 at 2:25 pm
Yes, its the same database.
Regards,
SQLisAwe5oMe.
November 27, 2015 at 5:05 pm
I have used Red Gate's Multiscript[/url] , awesome tool if you want to deploy a single or set of scripts across databases which have same schema. You can also control if you want these to execute in parallel or serial order. Worth every penny, you can do a 14 day trial just to see if this works for you.
November 27, 2015 at 9:52 pm
batch file is capable of executing all .sql files in a folder which includes sub directory
@echo off
setlocal enabledelayedexpansion
set /p servername=Enter DB Servername :
set /p dbname=Enter Database Name :
set /p spath=Enter Script Path :
set hr=%time:~0,2%
if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%
set logfilepath= %spath%\output_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log
set cmd='dir %spath%\*.sql /b/s'
FOR /f %%G IN (%cmd%) DO (
echo ******PROCESSING %%G FILE******
echo ******PROCESSING %%G FILE****** >> %logfilepath%
SQLCMD -S%servername% -E -d%dbname% -b -i%%G >> %logfilepath%
IF !ERRORLEVEL! NEQ 0 GOTO :OnError
)
GOTO :Success
:OnError
echo ERROR ERROR ERROR
echo One\more script(s) failed to execute, terminating bath.
echo Check output.log file for more details
EXIT /b
:Success
echo ALL the scripts deployed successfully!!
EXIT /b
November 27, 2015 at 9:55 pm
Thanks John for providing the script.
Regards,
SQLisAwe5oMe.
November 27, 2015 at 10:07 pm
I am glad you resolve your problem :smooooth:
November 28, 2015 at 12:42 pm
SQLisAwE5OmE (11/27/2015)
Thanks John for providing the script.
Do you understand how it works and how to control the execution order of the scripts using John's method?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2015 at 2:14 pm
Hi Jeff,
I did not test the script yet, but i kind of got an idea...i need to enter the server name, db name and the path details.
In regards to the execution order, i am not sure where it specify that.
If you dont mind explain it, please do so. Thnx.
Regards,
SQLisAwe5oMe.
November 29, 2015 at 9:47 am
SQLisAwE5OmE (11/28/2015)
Hi Jeff,I did not test the script yet, but i kind of got an idea...i need to enter the server name, db name and the path details.
In regards to the execution order, i am not sure where it specify that.
If you dont mind explain it, please do so. Thnx.
John's batch file is written to process each and every files with the .sql extension in a given path, including subdirectories. The command to generate the list of files to process is this one:
set cmd='dir %spath%\*.sql /b/s'
If you want to control the order in which they're executed (and you probably need to so) then a simple DOS sort on the dir command is the way to go.
I use a simple naming convention for my script files. I number them, but any ascending sort order will work. For example, the first one is named 001_tables.sql and the second is named 002_procedurea.sql and so on. The descriptive part of the filename is really for your benefit; the numbers are what you'll focus on in the script.
The DOS sort for the name is /on. There are others, but this is probably the one you want here. Your new command would then become:
set cmd = 'dir %spath%\*.sql /b /s /on';
I also keep my script files in a single directory, which means I don't need the /s from the command line. If you happen to have any directories in your path that contain ".sql" in the name, then you'll want to exclude them like this:
set cmd = 'dir %spath%\*.sql /a-d /b /s /on';
You're probably getting the idea that there are more than a couple of command line switches for the dir command and you're right. You can open a DOS window and fire the following to get a list of them.
dir /?
November 29, 2015 at 1:15 pm
SQLisAwE5OmE (11/28/2015)
Hi Jeff,I did not test the script yet, but i kind of got an idea...i need to enter the server name, db name and the path details.
In regards to the execution order, i am not sure where it specify that.
If you dont mind explain it, please do so. Thnx.
The best thing to do here would be for me to encourage a little self study because this stuff is going to come up a whole lot over your career. Ed Wagner started to touch on that with typing a command followed by /? to get the options for the command but there's a whole world of help available to you for these various DOS commands. To survive, Google is definitely your friend but you can also click on the Windows Start Button (or however the hell they do it in "Metro" (2012+)), click on "Help and Support", and then do a search for "command reference overview" for some tips and a whole array of very useful command line commands. Spend some time reading up on them because, even with Powershell coming about, these commands can be very powerful and they're not going away any time soon.
There's also another "system" known as WMIC that you might want to take a dive into that can do some incredible things without have to write even a single loop like that which is frequently required in PowerShell.
... and it's all FREE! Comes with Windows!
SQLCMD is how you run SQL Server commands/scripts from the Command Line. That would be in "Books Online" for SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply