T-SQL Script to read all files in directory and store back in table – SQL Server
Recently I was in need to analyze Salesforce debug log for one stubborn issue which was very hard to reproduce. Was able to download 1500+ debug logs on my system, however to analyze it, I decided to take help of SQL Server.
Very soon I came into challenge to read all files in a directory and store it back in SQL Server table.
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 .
If we try to see content in actual table, it will look like
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.
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
@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
Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.
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
SET @ColumnName = ''
SET @TableName =
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
) = 0
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
SET @ColumnName =
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND QUOTENAME(COLUMN_NAME) > @ColumnName
IF @ColumnName IS NOT NULL
INSERT INTO @Results
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
SELECT ColumnName, ColumnValue FROM @Results
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…
Step By Step Hibernate (ORM Tool) Tutorial Using eclipse WTP
Hibernate is the ORM tool widely used in java community to persist the java object using Object Relational Mapping (ORM) concept. ORM reduces number of lines to interact with database with optimized query language which is Hibernate Query language (HQL).
In this example, we will create a simple login application using hibernate tool of eclipse. We will use eclipse WTP (Web Tools Platform), to install “Hibernate Tools”. Follow below steps :
In Eclipse IDE, menu bar, select “Help” >> “Install New Software …” put the Eclipse update site URL “http://download.jboss.org/jbosstools/updates/stable/helios”
Before starting this demo application, I am assuming that you already have done CC&B set up on your local and Eclipse is configured.
What I am going to do so that I can use Business Script, Data Area, UI Map and BPA Script?
In this tutorial, I will create one UI Map which will take the person name as input and display list of all the person names as per input.
Final output screen would look like: