Release multiple SQL Server scripts to Production Server using batch file

I am writing this blog for one on my colleague Kallidile, Sujith who wants to do this task for his Production Deployment repeatedly. This is also dedicated to all DBA team who are also doing same.

To solve this problem, I have created a batch file which will read all SQL files from folder excluding child folder and use SQLCMD to execute scripts file to specified Data Source.

How to run batch file ?

1. Copy batch file “ReleaseSQLServerScripts.bat” to location from where you want to release SQL files.

2. Click on “ReleaseSQLServerScripts.bat” to execute batch file.

3. Batch file will ask Server Name where you want to deploy your scripts.

4. Batch file will ask you to click any key before executing each script.

5. Once click any key, It will execute this script to Target Server and display output.

6. Once all scripts are deployed, It will ask to click any key to close window.

Below is the batch file to download.

1. ReleaseSQLServerScripts.bat

Note:

1. We are using window authentication for deployment.

Linked Servers in SQL Server

I am always seeing that people are struggling while creating linked server in SQL Server based on their access on target server. I have tried to show some sample way to create linked server. Below are list of cases.
1) Linked server from SQL Server to another SQL Server using SQL Login
a) Go to SQL Server Management Studio –> Connect SQL Server –> Server Objects –> Linked Servers –> Right Click New Linked Server

b) Enter target server name in Linked Server. Select source type as SQL Server.

c) Go to Security and select “Be made using this security context”. Enter user name in Remote login and password in “With password”.

d) click Ok.

e) Execute below query to check if linked server is working.
Query: Select * From LinkedServerName.DatabaseName.sys.objects
2) Linked server from SQL Server to Self using SQL Login.
a) Go to SQL Server Management Studio –> Connect SQL Server –> Server Objects –> Linked Servers –> Right Click New Linked Server

b) Enter linked server name (e.g. SELF) in Linked Server. Select source type as Other data source. Select “Microsoft OLE DB Provider for SQL Server as Provider”. Enter target server name in Data Source. Enter database name in Catalog.

c) Go to Security and select “Be made using this security context”. Enter user name in Remote login and password in “With password”.

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

This is most well known error when we are trying to access excel from SQL Server. To fix this issue, we need to follow below steps.

1. Install Microsoft Access Database Engine 2010 Redistributable based on 32 bit or 64 bit version of Windows from below link.
   http://www.microsoft.com/en-in/download/details.aspx?id=13255
2. Registry changes. Navigate to <HKLM>\Software\Microsoft\Microsoft SQL Server\Providers\Microsoft.Jet.OLEDB.4.0 and Create a DWORD named DisallowAdhocAccess with the value 0. Just to check if you are in the right place there will be another DWORD entry under the name of AllowInProcess, which should be set to 1 already.
   
3. Restart agent and server services 
4. Enable OLEDB Driver in SQL Server 
    
   EXEC sp_configure 'show advanced options', 1; 
   GO 
   RECONFIGURE; 
   GO 
   EXEC sp_configure 'Ad Hoc Distributed Queries', 1; 
   GO 
   RECONFIGURE; 
   GO

Send Greetings to Multiple Mails from Excel in SQL Server

I am inspire to write this blog on the day of Ramzan when I want to send multiple greetings to my friends, family and others. I thought to create script which will read email IDs from excel and send Greeting to all of them.
We have to follow below steps to configure this process.

1. Create Error_Log Table (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)
2. Create Greeting table
   1. Columns
      1. GreetingName
         1. This is unique name to identify greeting which we will send to all email addresses.
      2. FromName
         1. This will be written as from clause in email  body.
      3. FromMail
         1. This will be use your from email address.
      4. ToMailListFilePath
         1. This is path of excel which will contain list of name with their email ids.
      5. Subject
         1. This is subject of your greeting.
      6. Message
         1. This is message for your greeting.
      7. GreetingImgUrl
         1. This is greeting image url hosted on some server.
3. Insert Greetings to dbo.Greeting table.
4. Create procedure dbo.cp_Send_Greeting with parameter GreetingName.
5. Execute procedure dbo.cp_Send_Greeting. Below is greeting mail.

Below are the scripts to download.

1. Send Greetings to multiple emails.sql

Below is sample document to use it.

1. GreetingMailList.xlsx

Unzip all *.zip files from folder including child folders

I am writing this blog for one on my colleague Ashish Gupta who wants to do this task for his Production Deployment repeatedly.

We had requirement in our company to unzip all files from folder including child folders to specified location. It was not only single file but we had lots of zip files and each was having lots file in there.

To solve this problem, we have created batch file which will read all zip files from folder including child folder and use winzip32.exe command to extract all files to specified location.

We also found that winzip32.exe command will open folder where files are extracted once unzip operation is completed. We had also provided solution for this here.

How to disable “Show unzipped file” option ?

1. Open C:\Program Files (x86)\WinZip\WINZIP32.EXE.

2. Click right bottom error at “Unzip Options”.

3. Uncheck “Show unzipped file”.

How to run batch file ?

1. Copy batch file “ExtractZipFiles.bat” to location from where you want to extract all zip files.

2. Click on “ExtractZipFiles.bat” to execute batch file.

3. Batch file will ask location where you want to extract all files and click enter. I have used “C:\GhanchiAsif” to extract my all files.

4. Batch will extract all files and extract to “C:\GhanchiAsif”.

Below is the batch file to download.

1. ExtractZipFiles.bat