Archive for the ‘SQL Server’ Category

SQL Server 2016 – Arrived

June 3, 2016 Leave a comment

This is a good news for the SQL Server community for this year. Microsoft has released SQL Server 2016 on June 1st  2016.

You need to sign-up to download the evaluation edition from this web page.

There are many exciting features in this version. Now, SQL Server has moved to the top of the database management servers ladder.



Categories: SQL Server

Insert or Update a File in Database – SQL Server

February 17, 2016 Leave a comment

Database is a repository for data of various formats. In the past, we use text files to store huge data and technology revolution has changed everything for what we store and How we store data. Big data is a good example. It happened in different forms as punch card, file system, relational database, graph db, NoSQL, NewSQL and etc.

But, in this blog post I am going to show a simple example for storing (insert) and altering (update) a binary image file stored in a table.


Create a Table

CREATE TABLE [dbo].[Images](

Sample Image file – SQL2016.jpg


Insert a record with a Image file

INSERT dbo.[Images]
(ImageDesc, ImageData)

Update an existing record with Image file

UPDATE dbo.[Images]
SET ImageDesc = ‘SQLServer’
,ImageData = BulkColumn from Openrowset( Bulk ‘C:\Test\SQLServer.jpg’, Single_Blob) as Img
WHERE ImageID = 1

This blog post is for code reference. You can use this code with not restriction.

Categories: SQL Server

How to debug T-SQL statement in SQL Server Management Studio

September 1, 2015 Leave a comment

Debugging is an important feature for every programming language with no exception SQL Server has this to help developer trace the their code and remove potential logical issue in the code before moving to production environment or simply to find the bug in the code.

SQL Server management Studio (SSMS) provide easy way to debug T-SQL statements.

Expectation: This blog post is to show how to use debug feature in SSMS with simple T-SQL statement.


declare @i as int, @n as int;
set @i = 0;
set @n = 5;

while @i < @n
set @i = @i + 1;
print @i;

Step 1: Open new query window and paste the above code

Step 2: Click Debug button on the toolbar


Step 3: Once debug starts, another debug related toolbar and windows (local variables, call stack) will show up. We can view and modify local variables and parameters, view global variables, as well as control and manage breakpoints while debugging.


Step 3: Click Step into (F11 – function key) to execute step by step.

This image is captured after 3 iterations. so, we can see the value of the variable @i = 3 and @n =5.


Try other debugging features in SSMS and keep  your code robust. Thanks.


Categories: SQL Server Tags:

Synchronize Table – SQL Server

September 1, 2015 2 comments

We have been doing table synchronize for many years using various techniques. Merge statement in SQL Server is not new for us. But, I liked this statement. So, I decided to write a simple note about it.

Expectation: This blog post is for beginners those who want to learn about this Merge statement using simple examples.

We can perform insert, update, or delete operations on a target table based using single statement based on the results of joining source and target table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. – MSDN reference

Creating working environment:

This is the main (target) table that we are going to use for synchronization.

declare @emp as table
EmployeeId int not null primary key,
DepartmentId int not null,
EmployeeName varchar(50) not null

This is the stage table to hold the data for sync.

declare @emp_stg as table
EmployeeId int not null,
DepartmentId int not null,
EmployeeName varchar(50) not null

Loading few rows to the stage table.

Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (1,1, ‘Robert’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (2,2, ‘Bobby’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (3,2, ‘Dan’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (4,3, ‘Johnson’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (5,4, ‘Monti’);

Now, using merge statement we are going to insert new rows to the main table @emp.

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
INSERT  (employeeid, departmentid, employeename)
VALUES (src.employeeid, src.departmentid, src.employeename)
OUTPUT $Action, inserted.*

$Action column will have the DML operation string value such as ‘INSERT’ or ‘UPDATE’ or ‘DELETE’. inserted.* will give us the news inserted row(s).

Now, We are now going to update the rows if there is a change in the incoming data.

This is a simple DML update statement for this experiment.

UPDATE @emp_stg set EmployeeName = ‘robert k’ where EmployeeId=1;

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
(src.departmentid <> tgt.departmentid or
src.employeename <> tgt.employeename)
UPDATE SET tgt.departmentid = src.departmentid,
tgt.employeename = src.employeename
OUTPUT $Action, inserted.*

Following merge statement is to perform table sync with delete operation for the unavailable data.

DELETE from  @emp_stg where EmployeeId=3;

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
OUTPUT $Action, deleted.*

I have separated Insert, Update and Delete operation for this example to show the flexibility of the Merge statement. But, These statements can be used together to perform all in one operation.

Categories: SQL Server Tags: ,

Get the File Last Modified datetime from HTTP Header – SSIS

June 30, 2015 Leave a comment

This VB.Net code can be used to get the Last modified date of a file from HTTP header. It can be used to check the file date before the actual file is downloaded to out computer. It save some time and network resources. You can pass different header key to get its value.

But, this example is to get the last modified date of a file from website.

Dim request As HttpWebRequest = HttpWebRequest.Create _
Dim response As HttpWebResponse

response = request.GetResponse()

Dim HTTPHeader_LastModified_Datetime As String
HTTPHeader_LastModified_Datetime = response.GetResponseHeader(“Last-Modified”)


Hope you will like it.

Categories: SQL Server

Get the File Last Modified datetime from FTP Server – SSIS

June 17, 2015 2 comments

There are many ways to get the file modified date from the FTP server. But, I like to write a code to do that task simpler. Script Task is used in SSIS.

Code: To fetch the file last modified datetime

Imports System.Net

Dim reqFTP As FtpWebRequest = DirectCast(FtpWebRequest.Create(New Uri(“ftp://SERVERName/FileName”)),  FtpWebRequest)

reqFTP.Method = WebRequestMethods.Ftp.GetDateTimestamp

reqFTP.Credentials = New NetworkCredential(“USER NAME”, “PASSWORD”)

response = DirectCast(reqFTP.GetResponse(), FtpWebResponse)



Using Credentials is based on the server requirement. In this script, file date time is just displayed using Message box. We can use package variable to store the value.

Categories: SQL Server

Read and Write to Text file – VB.Net

March 26, 2015 Leave a comment

I created this snippet to save my search time in future. So, you can also make use of this if needed.

The main objective of this code is to read a text file line by line and write it into another file. I know, it looks silly. because, why do we need to read a file and write to others without doing anything. But, The actual purpose is to read a text file and do some operations like update or remove. Using this method I can keep my original file unchanged. does it make sense now?

Code snippet

Imports System.IO

Dim strLine As String
Dim objStreamReader As StreamReader
Dim objStreamWriter As StreamWriter

objStreamReader = New StreamReader(“C:\test\source.txt”)
objStreamWriter = New StreamWriter(“C:\test\destination.txt”)

strLine = objStreamReader.ReadLine

Do While Not strLine Is Nothing
strLine = objStreamReader.ReadLine

‘Close the file.

I know there are millions of ways to do something. But, I choose this method and if you have a new logic please share it in the comment section.


Categories: SQL Server
%d bloggers like this: