Export Documents saved as Blob / Binary from SQL Server

T-SQL Scripts to Export Blob or Binary data stored in SQL Server

I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server. However, finally came up with below solution which worked very well. Below script was used to export around 25GB of files stored in SQL Server.

To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob .

CREATE TABLE [dbo].[Document](
	[Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[Extension] [varchar](50) NULL,
	[FileName] [varchar](200) NULL,
	[Doc_Content] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

How to Insert Blob into Database

For demo purpose, we will save two files in our table using below queries

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*
FROM OPENROWSET 
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB)  [Doc_Data]

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'html', 'Progress.html',[Doc_Data].*
FROM OPENROWSET 
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB)  [Doc_Data]

If we try to see content in actual table, it will look like

Insert Blob into Database
Insert Blob into Database

Export Blob From SQL Server and save it as a file

For demo purpose, we want to save documents on local disc. We will use Doc_Num to be created as folder and document will be saved in that folder. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post.
Note : We will create only one folder per document. If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder. Its explained in this blog post.

Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.

USE [POC]
DECLARE @outPutPath varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract Blob'
, @i bigint
, @init int
, @data varbinary(max) 
, @fPath varchar(max)  
, @folderPath  varchar(max) 
 
--Get Data into temp Table variable so that we can iterate over it 
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )
 
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [Doc_Num] , [FileName],[Doc_Content] FROM  [dbo].[Document]
 
--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable
 
WHILE @i >= 1
BEGIN 

	SELECT 
	 @data = [Doc_Content],
	 @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
	 @folderPath = @outPutPath + '\'+ [Doc_Num]
	FROM @Doctable WHERE id = @i
 
  --Create folder first
  EXEC  [dbo].[CreateFolder]  @folderPath
  
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1;  
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources
 
  print 'Document Generated at - '+  @fPath   

--Reset the variables for next use
SELECT @data = NULL  
, @init = NULL
, @fPath = NULL  
, @folderPath = NULL
SET @i -= 1
END

Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.

Below image shows output in action :

SQL Server Export Blob as File
SQL Server Export Blob as File

SQL Server – Search complete database for value

Search complete Database in SQL Server for some value

Recently, I was in need to search complete Database for some value and found below very useful script (T-SQL)

DECLARE @SearchStr nvarchar(100) = 'SEARCHSTRING'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')


WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
	BEGIN TRY
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
END TRY
BEGIN CATCH
END CATCH
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1) 
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName 
        )
         

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

If we run above script for word ‘king’ then output in below format will be displayed Continue reading “SQL Server – Search complete database for value”

SQL Server – Create Folders using T-SQL & OLE automation

Tutorial to create folders in SQL Server using Transact-SQL and OLE Automation Stored Procedures

SQL Server has some standard stored procedures that allow OLE automation.

First step, is to check whether Ole Automation Procedures are enabled in SQL Server or not ? It can be enabled by simply executing below T-SQL commands.

T-SQL to enable Ole Automation Procedures in SQL Server

        sp_configure 'show advanced options', 1;
	GO
	RECONFIGURE;
	GO
	sp_configure 'Ole Automation Procedures', 1;
	GO
	RECONFIGURE;
	GO

Next step is to create a stored procedure, which will use an OLE Automation procedures and create a folder on system drive Continue reading “SQL Server – Create Folders using T-SQL & OLE automation”

Pagination and Switch Case in Where clause – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with 1 Comment on Pagination and Switch Case in Where clause – SQL Server

Example of using pagination and switch case in where clause in SQL server

After a long time i am going to write any article on SQL Server. That last article i think was before 18 months. As currently i am exploring the Salesforce and its API’s, i caught in situation to create a stored procedure and provide the offset feature, that means i needed the pagination support in my stored procedure. There is no direct keyword available in SQL Server something like LIMIT in mysql. And therefore i thought to share my solution with community. So, here we go…

I am considering that there will be table named “Employee” in which i want to search by employees firstName with pagesize and offset attribute. Continue reading “Pagination and Switch Case in Where clause – SQL Server”

“Update From” Statement in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags with 2 Comments on “Update From” Statement in SQL Server

Example to demonstrate the “Update From” Statement in SQL Server. This example can be used in scenarios where developer wants to update the Table with the help of Inner Join.

In This article, i am going to demonstrate that how to use the “Update From” statement in SQl Server. Inner Join or Joins can be easily used in Insert Statement, but some times it is needed in Update Statements Also.

Lets have the below example:

Continue reading ““Update From” Statement in SQL Server”

What is ODBC and OLEDB – Interview Question

Author posted by Jitendra on Posted on under category Categories c#, Microsoft, SQL Server and tagged as Tags , , with Leave a comment on What is ODBC and OLEDB – Interview Question

This articles explains in detail description of ODBC and OLEDB component of Microsoft and its Usage

Open Database Connectivity (ODBC) :

How ODBC Works
How ODBC Works

In computing, Open Database Connectivity (ODBC) provides a standard software interface for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of programming languages, database systems, and operating systems. Thus, any application can use ODBC to query data from a database, regardless of the platform it is on or DBMS it uses.

Continue reading “What is ODBC and OLEDB – Interview Question”

Difference in ADO and ADO.Net – Interview Question

Author posted by Jitendra on Posted on under category Categories c#, SQL Server and tagged as Tags , with 1 Comment on Difference in ADO and ADO.Net – Interview Question

Basic comparison between ADO and ADO.Net component in .Net Framework

Following are the basic difference between the ADO and ADO.Net in Microsoft .Net framework.

ADO.Net
ADO.Net

Continue reading “Difference in ADO and ADO.Net – Interview Question”

Use of OPENXML in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with Leave a comment on Use of OPENXML in SQL Server

In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string.

In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string. OpenXML is used to parse the XML in Rowset data form.

In this tutorial, i have created one XML string and saved the parsed data in Table variable. If XML data is present in table, then any operation can be performed.

Continue reading “Use of OPENXML in SQL Server”

Manually attach database in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with Leave a comment on Manually attach database in SQL Server

Download sample database of SQL server and Manually attach database in SQL Server – AdventureWorksDB

SQL server 2005, does not come with default / sample databases. so it can be downloaded and installed from http://msftdbprodsamples.codeplex.com/releases/view/4004.

After downloading, the challenge for the new developer is that how to associate database with existing SQL server installed on system.

To associate the database, follow below steps:

right click on database and select “Attach” option.

Click on Attach option of Database

Continue reading “Manually attach database in SQL Server”

Convert rows into comma separated values column – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags , with Leave a comment on Convert rows into comma separated values column – SQL Server

Convert rows into comma separated values column – SQL Server

Converting rows values into comma separated column value is required lots of time:

example, i want to convert :

Name
------------------------------
India
USA
Japan
China
Switzerland

to

Continue reading “Convert rows into comma separated values column – SQL Server”