A Simple Way to Write String into a Text File in SQL Server using T-SQL

• Download script - 1.34 KB

Table of Contents

• Introduction
• What is an OLE OBJECT?
• Background
  • A Few Basics on BCP Operation
  • Sample Bulk Insert SQL Statement
  • What is an OPENROWSET Statement? 
  • What is BLOBs?
• Using the Code
  • Sample Code Example
• OLE Automation
  • How to Enable "OLE Automation"
• More on the FSO and OLE Automation
  • Sample Example to Enable "OLE Automation"
• Conclusion
• Reference
• History

Introduction 

File manipulation is a very frequent requirement for various types of software development. Most of the times, we are dependent on the SQL server Data ex/import wizard for transferring data into a flat file / CSV format as well. In this article, I would like to demonstrate how we can write a simple text file using T-SQL.

SQL servers allow us to integrate an OLE object. This feature is really cool when you need to integrate SQL server with any third party DLL / ActiveX com as well.

More details on OLE object integration can be found at this link. 

What is an OLE OBJECT?

OLE (Object Linking and Embedding) is Microsoft's framework for a compound document technology. Briefly, a compound document is something like a display desktop that can contain visual and information objects of all kinds: text, calendars, animations, sound, motion video, 3-D, continually updated news, controls, and so forth. Each desktop object is an independent program entity that can interact with a user and also communicate with other objects on the desktop. Part of Microsoft's ActiveX technologies, OLE takes advantage and is part of a larger, more general concept, the Component Object Model (COM) and its distributed version, DCOM. An OLE object is necessarily also a component (or COM object).

So we can define an OLE object as “OLE is a compound document standard developed by Microsoft Corporation. It enables you to create objects with one application and then link or embed them in a second application. Embedded objects retain their original format and link to the application that created them.”

More details can be found at this link. 

Background

I believe that 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 writing tabular data into 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.

A Few Basics on BCP Operation

SQL server BCP is not directly used in this article. I just want to share with you the basics which may help you to view this article from a different angle, and you can also use this command to use various SQL operations. More details will be available at this link.

Sample Bulk Insert SQL Statement

DECLARE @SQL_QUERY      NVARCHAR(1000)

SET @SQL_QUERY= '
 INSERT INTO #ORStable
 SELECT len(bulkcolumn), *
 FROM OPENROWSET(BULK '''+@VAL2+''', _
 SINGLE_BLOB) AS BinaryData'
            exec SP_executesql @SQL_QUERY

--Here variable VAL2 contains the single file path information.
--Example VAL2 = "\\192.168.1.1\myFiles\myFile.pdf"
--        VAL2 = "C:\myFiles\myFile.pdf"

What is OPENROWSET?

OPENROWSET: Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

What is BLOBs

Binary Large Objects (BLOBs): BLOB data type to store any data that a program can generate: graphic images, satellite images, video clips, audio clips, ...

BulkColumn: The BulkColumn referenced in the query represents the varbinary value to be inserted.

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 write a text into a text file.

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 / writes 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.spWRITEFileStream to write a string into a text file.

Sample code example is given below:

/****** Object:  StoredProcedure [dbo].[spWRITEFileStream]    
    Script Date: 04/20/2009 21:52:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=============================================
-- Author:        <Author,,Md. Marufuzzaman>
-- Create date: <Create Date,,04/20/2009>
-- Description:    <Description,,>
-- =============================================

/*
DECLARE  @strText VARCHAR(MAX)
SET @strText = 'This is a simple text message.'
EXEC dbo.spWRITEFileStream 'C:\', 'TempE.txt', @strText 
PRINT @strText
*/

CREATE PROCEDURE [dbo].[spWRITEFileStream]
  @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
        ,@IsAppend INT
        ,@NewFileAndPath VARCHAR(100)

 SELECT @strText = ''
 SET NOCOUNT ON

SELECT @strErrorMessage='opening the File System Object'
EXECUTE @intResult = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUTPUT

SET  @NewFileAndPath= @FILE_PATH + '\' + @FILE_NAME
IF @intResult=0 SELECT @objErrorObject=@objFileSystem , _
    @strErrorMessage='Creating file "'+ @NewFileAndPath + '"'
IF @intResult=0 EXECUTE @intResult = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
    , @objTextStream OUTPUT, @NewFileAndPath,2,True

IF @intResult=0 SELECT @objErrorObject=@objTextStream, 
    @strErrorMessage='writing to the file "'+@NewFileAndPath+'"'
IF @intResult=0 EXECUTE @intResult = sp_OAMethod  @objTextStream, 'Write', Null, @STRING

IF @intResult=0 SELECT @objErrorObject=@objTextStream, _
    @strErrorMessage='closing the file "'+ @NewFileAndPath + '"'
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,'')
        RAISERROR (@strErrorMessage,16,1)
    END
    
    
    -- Release the reference to the COM object */
    EXECUTE  sp_OADestroy @objTextStream
    EXECUTE  sp_OADestroy @objFileSystem
    
    SET @STRING = @strText

RETURN 
END       

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:

How to 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.

More on the FSO and OLE Automation

There are all sorts of things you can do with the FSO. You can copy files, move files, create folders, delete files, get the names of special directories, and so on. This may sound esoteric, but sometimes the simplest backup procedures require such operations.

The Filesystem Object, on which all the stored procedures in this article rely, is a component of the scripting runtime library. As it is a COM object, it is readily accessible from the set of stored procedures built-in to SQL Server called the OLE Automation Stored Procedures. These allow a connection, through T-SQL commands, to create and use COM-based objects.

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  

Conclusion

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

Reference 

• support.microsoft.com

History

• 25^th July, 2009: Initial post
• 29^th July, 2009: Basic overview on SQL Server bcp operation - this is another way to manage file manipulation in SQL server
• 17^th August, 2009: Corrected spelling & code formation