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

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

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

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.

All About indexing an external database in Sitecore

This post is about indexing an External Database in Sitecore.

Flow of external indexing

So will index data of external database tables and use LUCENE search to get result from index files. If you are new to LUCENE search, please refer my article on Lucene search.

There is one great post on the same topic by Cameron Palmer.

I setup most of the things according to this post and its work like a charm.

However for Sitecore 8.0, I need to do below changes.

• You need to add reference of Sitecore.ContentSearch.dll, Sitecore.ContentSearch.Linq.dll, Sitecore.ContentSearch.Linq.Lucene.dll and Sitecore.Kernel.dll.

• I added my fields in "<fieldNames hint="raw:AddFieldByFieldName">" section in Sitecore.ContentSearch.Lucene.DefaultIndexConfiguration.config.

• I created new config file "People.Index.config" in "Include" folder as

<?xml version="1.0"?> <configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">   <sitecore>     <contentSearch>       <configuration type="Sitecore.ContentSearch.ContentSearchConfiguration, Sitecore.ContentSearch">         <indexes hint="list:AddIndex">           <index id="People_Index" type="Sitecore.ContentSearch.LuceneProvider.LuceneIndex, Sitecore.ContentSearch.LuceneProvider">             <param desc="name">$(id)</param>             <param desc="folder">$(id)</param>             <!-- This initializes index property store. Id has to be set to the index id -->             <param desc="propertyStore" ref="contentSearch/indexConfigurations/databasePropertyStore" param1="$(id)" />             <configuration ref="contentSearch/indexConfigurations/defaultLuceneIndexConfiguration" />             <strategies hint="list:AddStrategy">               <!-- NOTE: order of these is controls the execution order -->               <strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/onPublishEndAsync" />             </strategies>             <commitPolicyExecutor type="Sitecore.ContentSearch.CommitPolicyExecutor, Sitecore.ContentSearch">               <policies hint="list:AddCommitPolicy">                 <policy type="Sitecore.ContentSearch.ModificationCountCommitPolicy, Sitecore.ContentSearch">                   <Limit>300</Limit>                 </policy>               </policies>             </commitPolicyExecutor>             <!--<locations hint="list:AddCrawler">               <crawler type="Sitecore.ContentSearch.SitecoreItemCrawler, Sitecore.ContentSearch">                 <Database>web</Database>                 <Root>/sitecore</Root>               </crawler>             </locations>-->             <locations hint="list:AddCrawler">               <crawler type="Testsite.ASPNET.UI.PeopleCrawler, Testsite.ASPNET.UI">               </crawler>             </locations>             <enableItemLanguageFallback>true</enableItemLanguageFallback>             <enableFieldLanguageFallback>true</enableFieldLanguageFallback>           </index>         </indexes>       </configuration>     </contentSearch>   </sitecore> </configuration>

You need to change two yellow highlighted things in this "People.Index.config" file as

• <index id="People_Index" name
• Class type details with assmbly name as <crawler type="Testsite.ASPNET.UI.PeopleCrawler, Testsite.ASPNET.UI">

That's it from Sitecore point of view.

After this next challenge is how to rebuild/refresh index for any DML operation done on external database tables. For this you can refer my article "Rebuild Sitecore index on real time basis from external system".

Index rebuild process takes time on Sitecore side. So you must call index rebuild method in async function in C#.

I hope you like this Sitecore lesson. Stay tuned for more Sitecore related articles.

Till that happy sitecoring :)

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