Automate SQL Server operations by batch file

Many times we need to perform many repetitive actions on our sql server . Many of those are literaaly repetitve or many of them we really do not do every day but whenever it is , it a collection of steps .

Today we will see how easily we can automate our repetitive work by batch file so that we always donot have to open SQL Server Management Studio and conect to our DB etc….

 

You can also have a look how to create a log file from batch file in my previous post

Create a batch file :

1. Open your favourite text edidor and write below lines :

 @echo off
SET SERVER=YOURSERVERNAME
SET DB=YOURDATABSENAME

SET Logfile=runscripts.log
SET InvokeSQL=sqlcmd -S %SERVER% -d %DB%  -E -b -i
echo %DATE% %TIME% %USERNAME% > %Logfile%
echo calling sql-scripts with: %InvokeSQL% %%scriptfile%% >> %Logfile%
echo. >> %Logfile%
REM START trace32.exe %Logfile%
echo.
CALL :RUNProgram 001.sql || GOTO :FAILED
CALL :RUNProgram 002.sql || GOTO :FAILED
echo no errors found – logfile = %Logfile%
GOTO :EOF
:RUNProgram
echo running %1%
echo.>> %Logfile%
echo ===================   running %1%     %     %TIME%  =======================%  %>> %Logfile%
%InvokeSQL% %1% %>> %Logfile% 2>&1
GOTO :EOF
:FAILED
echo errors found – logfile = %Logfile%

2. Save file as batch file “myFirstSQLBatch.cmd”

3. Run it !!!!

Thats it !!!

Create Pinging tool on network

Hi all ,

Today we are going to create a pinging too which will  help to ping all the IP’s on the network  one by one automatically .With the help of this tool we can check the correctness of the machine weather its alive on the network or not..

So here we go

 

(1)Open Notepad and paste the following few lies

@echo on
 cls > Run.txt
cls >Runhtml.html

echo ^<HTML^> >>Run.txt 

echo ^<HEAD^> >>Run.txt
echo ^</HEAD^> >>Run.txt

echo ^<BODY^> >>Run.txt
echo DATE:- >>Run.txt
date /T >>Run.txt
echo.  >>Run.txt
echo.  >>Run.txt

echo ^<pre^> >>Run.txt
echo ^<b^> >>Run.txt
echo ^<font color=”Blue”^> >>Run.txt
FOR /F “tokens=1*” %%G IN (IPAddressList.txt) DO ping %%G >>Run.txt
echo. >>Run.txt
echo. >>Run.txt
echo.  >>Run.txt
echo ^</font^> >>Run.txt
echo ^</b^> >>Run.txt
echo ^</pre^> >>Run.txt

 
echo ^</Body^> >>Run.txt
echo ^</HTML^> >>Run.txt
 copy Run.txt Runhtml.html
 
 

 

 

 

(2) Save this  file with .bat extention

(3) create another batch file to create html file for output. paste the following code

@echo on
REM cls > Run.txt
cls >Runhtml.html
REM Create the Output Directory
 IF EXIST %1 RMDIR %1 /s /q
MKDIR %1

echo ^<HTML^> >>Runhtml.html

echo ^<HEAD^> >>Runhtml.html
echo ^</HEAD^> >>Runhtml.html

echo ^<BODY^> >>Runhtml.html

DO date >> Runhtml.html
echo ^</br^> >>Runhtml.html
echo ^</br^> >>Runhtml.html

color 2
FOR /F “tokens=1*” %%G IN (IPAddressList.txt)
echo ^</br^> >>Runhtml.html
DO ping %%G >>Runhtml.html
echo ^</br^> >>Runhtml.html
echo ^</br^> >>Runhtml.html

 
echo ^</Body^> >>Runhtml.html
echo ^</HTML^> >>Runhtml.html
REM copy Run.txt Runhtml.html

 

(4)Create another text file on notepad with name IPAddressList add ip addresses you want to ping (one address in one line )  and save it in the same folder.

(4)Duble click on the batch file  we have created  in the first step.

Enjoy.