Reading Files in SQL Server Using T-SQL

• Download script - 1.32 KB

Introduction

SQL Server provides several "standard" techniques for file manipulation but, sometimes, they aren't quite up to the task at hand – especially when dealing with large strings or relatively unstructured data. Here are a number of T-SQL Stored Procedures, based on the use of the FileSystem Object (FSO) that may just get you out of a tight corner.

Background

SQL Server has never been short of ways to read from files, and it is always better to use the standard techniques provided by SQL Server where possible. However, most of them are really designed for reading tabular data, and aren't always trouble-free when used with large strings or relatively unstructured data.

For reading tabular data from a file, whether character-delimited or binary, there is nothing that replaces the hoary old Bulk Copy Program (BCP), which has more esoteric methods such as Bulk Insert. It is possible to read text-based delimited files with ODBC; simple files can be read and written-to using the xp_cmdshell Stored Procedure.

Using the code

Thankfully, when armed with OLE Automation and the FileSystem Object (FSO), all sorts of things are possible. The FileSystem Object was introduced into Windows to provide a single common file-system COM interface for scripting languages. It provides a number of handy services that can be accessed from T-SQL. In this article, I provide an example Stored Procedure that uses this interface to allow you to:

1. Read lines of text from a file.
2. Read a file into a SQL Server data type.

I'll provide a few details on the FSO along the way, but let's start with some examples of some of these procedures in action. You'll need to enable OLE Automation on your test server in order to follow along.

This is all very well, but how about something that reads a file in one gulp into a VARCHAR or XML data type? Perhaps you need to extract data from HTML, XHTML or some other format. I wrote a Stored Procedure dbo.spREADFileStream to read a file as a string. A code example is given below:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Md. Marufuzzaman>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================

/*
DECLARE  @strText VARCHAR(MAX)
EXEC dbo.spREADFileStream 'E:\', 'Input.txt', @strText OUTPUT
PRINT @strText
*/

CREATE PROCEDURE [dbo].[spREADFileStream]
  @FILE_PATH VARCHAR(MAX)
 ,@FILE_NAME VARCHAR(100)
 ,@STRING     VARCHAR(MAX) OUTPUT
AS
BEGIN
DECLARE  @objFileSystem INT
        ,@objTextStream INT
        ,@objErrorObject INT
        ,@strErrorMessage VARCHAR(1000)
        ,@Command VARCHAR(1000)
        ,@Chunk VARCHAR(8000)
        ,@strText VARCHAR(max)
        ,@intResult INT
        ,@YesOrNo INT

 SELECT @strText = ''
 SELECT @strErrorMessage = 'File System Object'
 EXECUTE @intResult  =  sp_OACreate  'Scripting.FileSystemObject' , 
                        @objFileSystem OUT

IF @intResult  =  0 SELECT @objErrorObject = @objFileSystem, @strErrorMessage = 
                    'Opening file "' +  @FILE_PATH  + '\' +  
                    @FILE_NAME  + '"',@command =  @FILE_PATH  + '\' +  @FILE_NAME 

IF @intResult = 0 EXECUTE @intResult  =  
  sp_OAMethod   @objFileSystem  , 'OpenTextFile', 
  @objTextStream OUT, @command,1,false,0 --//Forreading, FormatASCII

WHILE (@intResult = 0)
    BEGIN
        IF @intResult = 0 SELECT @objErrorObject = @objTextStream, 
            @strErrorMessage = 'Finding out if there is more to read in "' +  
                                @FILE_NAME  + '"'
        IF @intResult = 0 EXECUTE @intResult  =  
           sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
        IF @YesOrNo<>0  BREAK
        IF @intResult = 0 SELECT @objErrorObject = @objTextStream, 
            @strErrorMessage = 'Reading from the output file "' +  @FILE_NAME  + '"'
        IF @intResult = 0 EXECUTE @intResult  =  
           sp_OAMethod  @objTextStream, 'Read', @chunk OUTPUT,4000
        
        SELECT @strText = @strText + @chunk
    END
IF @intResult = 0 SELECT @objErrorObject = @objTextStream, 
    @strErrorMessage = 'Closing the output file "' +  @FILE_NAME  + '"'
IF @intResult = 0 EXECUTE @intResult  =  sp_OAMethod  @objTextStream, 'Close'

IF @intResult<>0
    BEGIN
    DECLARE 
         @Source VARCHAR(255)
        ,@Description VARCHAR(255)
        ,@Helpfile VARCHAR(255)
        ,@HelpID INT
    
    EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
        @source OUTPUT,@Description OUTPUT,@Helpfile OUTPUT,@HelpID OUTPUT
    SELECT @strErrorMessage = 'Error whilst '
             + coalesce(@strErrorMessage,'doing something')
             + ', ' + coalesce(@Description,'')
    SELECT @strText = @strErrorMessage
    END
 
    EXECUTE  sp_OADestroy @objTextStream
    
    SET @STRING = @strText

RETURN 
END
GO

OLE Automation

Each OLE Automation Stored Procedure returns an integer code that is the HRESULT returned by the underlying OLE Automation operation. When an error happens, the HRESULT returns an error code rather than the 0 which signals success, which then has to be turned into a meaningful error message with the sp_OAGetErrorInfo procedure.

The full set of OLE automation procedures are given below:

Reference: SQL Server Books Online.

Enable OLE Automation

Use the OLE Automation Procedures option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches. This option can also be configured using Policy-Based Management or the sp_configure Stored Procedure.

The OLE Automation Procedures option can be set to the following values.

When OLE Automation Procedures are enabled, a call to sp_OACreate will start the OLE shared execution environment.

The current value of the OLE Automation Procedures option can be viewed and changed by using the sp_configure system Stored Procedure.

The following example shows how to view the current setting of OLE Automation Procedures.

EXEC sp_configure 'Ole Automation Procedures';
GO

The following example shows how to enable OLE Automation Procedures:

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

Reference: MSDN

Conclusion

I hope that this article will be helpful to you. Enjoy!