Surendra Sharma

Surendra Sharma

Search This Blog

Showing posts with label DOS. Show all posts
Showing posts with label DOS. Show all posts

Friday, November 4, 2016

Run SQL scripts from DOS batch file



This post is regarding executing SQL scripts from DOS batch file by using sqlcmd.

sqlcmd with batch script
sqlcmd with batch script


When you want to setup new environment, you might need to run lots of database scripts manually. But fortunately we can avoid this manual step by using sqlcmd command line utility with the help of DOS batch file. 

Create folders and keep SQL scripts inside those folders and sub folders as

Scripts in Folder
Scripts in Folder


Tip: - You should keep your script name start with number so that they will run in same order.

Below is a sample script which execute all scripts inside folders and its sub folders

@ECHO OFF
SET SQLLOG=%~dp0sql_log.txt
SET SQLSERVER=.\SQLEXPRESS
ECHO. Starting Script, Server: %SQLSERVER%, logging to : %SQLLOG%
ECHO.

CALL:runsql "1 Create Databases"
CALL:runsql "2 Create Schema"
CALL:runsql "3 Data\1 Insert"
CALL:runsql "3 Data\2 Insert"

EXIT /B %ERRORLEVEL%

::Function to run SQL script files in a Directory
:runsql
echo.
echo. Executing Scripts in Directory:
echo. %~1
echo.
PUSHD %~1
for %%G in (*.sql) do echo. sqlcmd -S"%SQLSERVER%" -E -e -i"%%G"
for %%G in (*.sql) do echo. sqlcmd -S"%SQLSERVER%" -E -e -i"%%G"
"%SQLLOG%"
for %%G in (*.sql) do sqlcmd -S"%SQLSERVER%" -E -e -i"%%G" >> "%SQLLOG%"
POPD


In current script, I am using below options
-S [protocol:]server[instance_name][,port]
-e (echo input)
-E (use trusted connection)
-i input_file

Pushd command and popd command in a batch program is used to change the current directory from the one in which the batch program was run and then change it back.

Note: - Your SQL server should be in mixed mode.

You can refer link to know more about sqlcmd command and its various available options at https://msdn.microsoft.com/en-us/library/ms162773.aspx

This batch file also log all SQL command that it run in “sql_log.txt” file.

After running this script, I am able create database, tables and insert some records in those tables as

Final DB
Final DB


I hope you like this SQL batch trick. Stay tuned for more articles.

Please leave your comments or share this article if it’s useful for you.

Tuesday, October 25, 2016

ROBOCOPY : Auto Push static files to hosting environments



Developers are working on their development machine during website development phase. Once they are done with their file changes, they are going to publish those changes. 
They can do publishing at any time without any efforts. But if solutions have lots of projects then this process take times. 

Can we avoid and minimize this time and automate this process at least for static files like 
images, HTML, views, aspx, css, js etc.?

Answer is YES. We can automate this process to sync development static files folders with Published folders.

Even we can push development change static files to QA and staging environment folders which are across the network.

Sync
Sync


How to push the changes?

I am using a batch file with ROBOCOPY command in my development machine for this.

ROBOCOPY stand for robust file copy and this command allows you to copy files, directories, and even drives from one location to another.

Robocopy monitor the source folder and if any change happened in that folder like new file created, file deleted, updated etc. then Robocopy takes all the files and replace those files on destination folder.

Syntax : ROBOCOPY source destination [options]

Create a batch file in solution root folder and put below script 

@ECHO OFF

REM this batch file uses robocopy to monitor certain folders in the web projects for changes
REM and copies the changes to the web directory to save time when editing non compiled
REM files such as javascript CSS and cshtml

SET SITEROOT=C:\inetpub\wwwroot\SitecoreLessons\Website
SET SLNROOT=%~dp0
SET OPTS=/MIR /MON:1 /MOT:1 /NJS /NDL /NP /NS /NC

START ROBOCOPY "%SLNROOT%\SitecoreLessons\Assets" "%SITEROOT%\Assets" %OPTS%

REM You can put multiple Robocopy command here like

REM START ROBOCOPY "%SLNROOT%\SitecoreLessons\Views" "%SITEROOT%\Views" %OPTS%


@EXIT

Here is the description of this batch file for those who don’t have any knowledge of batch files

ECHO -> Used to print on output device
REM -> Used to write comment in batch file
SET -> Used for declaring the variable like SITEROOT variable is used to store my destination folder path.
%~dp0 -> Current directory path from where this batch file is running

I have defined “OPTS” variable which stores ROBOCOPY command options


Options
Description
MIR
MIRror a directory tree
MON:1
MONitor source. Run again when more than n changes seen.
MOT:1
MOnitor source. Run again in m minutes Time, if changed.
NJS
No Job Summary.
NDL
No Directory List - don't log directory names.
NP
No Progress - don't display percentage copied.
NS
No Size - don't log file sizes.
NC
No Class - don't log file classes.



You can get more option from http://www.computerhope.com/robocopy.htm

Double click on this batch file to start monitor our source “Assets” folder as

Batch File
Batch File


I have added “Assets\Images” folder in my VS projects and paste some image files in this folder. 

Note: - I have not build or publish my VS solution till now. 

But if I wait for 1 min. I got new all image files in my “C:\inetpub\wwwroot\SitecoreLessons\Website\Assets\Images” folder automatically.

Sync Images
Sync Images


Wowww. This is cool. Now I can write more Robocopy command in same batch file for copying Views, ASPX, HTML files etc.

I hope this pointer helps you to improve your productivity in your development environment.

Please leave your comments and share this trick with other developers.