- page 164

sp_readtextfile

Sp_readtextfile allows you to read a text file on the SQL Server machine (or a machine accessible to it on the network) and either return it as a result set or store the first 8,000 bytes of it in an output parameter. The code is presented in Listing 21-1.

Listing 21-1 sp_readtextfile.

USE master
GO
IF OBJECT_ID('sp_readtextfile') IS NOT NULL
  DROP PROC sp_readtextfile
GO
CREATE PROC sp_readtextfile @textfilename sysname,

@contents varchar(8000)='Results Only' OUT

/*

Object: sp_readtextfile
Description: Reads the contents of a text file into a SQL result set

Usage: sp_readtextfile @textfilename=name of file to read, @contents=optional
output var to receive contents of file (up to 8000 bytes)

Returns: (None)

$Author: Ken Henderson $. Email: khen@khen.com

$Revision: 8.0 $

Example: sp_readtextfile 'D:\ MSSQL7\ LOG\ errorlog'

Created: 1996-05-01. $Modtime: 2000-01-20 $.

*/
AS
SET NOCOUNT ON
IF (@textfilename='/?') GOTO Help

CREATE TABLE #lines (lno int identity, line varchar(8000))

DECLARE @cmd varchar(8000), @crlf char(2)

SET @cmd='TYPE '+@textfilename

SET @crlf=CHAR(13)+CHAR(10)

INSERT #lines (line)


EXEC master


.


dbo


.


xp_cmdshell @cmd

IF ISNULL(@contents,'')='Results Only'
  SELECT ISNULL(line,'') AS line
  FROM #lines
  ORDER BY lno
ELSE
  SELECT @contents=CASE lno WHEN 1 THEN ISNULL(RTRIM(line),'')+@crlf ELSE
@contents+ISNULL(RTRIM(line),'')+@crlf END
  FROM #lines
  ORDER BY lno

DROP TABLE #lines
RETURN 0

Help:
EXEC sp_usage @objectname='sp_readtextfile',
@desc='Reads the contents of a text file into a SQL result set',
@parameters='@textfilename=name of file to read, @contents=optional output var
to receive contents of file (up to 8000 bytes)',
@author='Ken Henderson', @email='khen@khen.com',
@version='8',@revision='0',
@datecreated='19960501', @datelastchanged='20000120',
@example='sp_readtextfile ''D:\MSSQL7\LOG\errorlog'' '
RETURN -1
GO
EXEC sp_readtextfile 'c:\readme.txt'

(Results)

line
---------------------------------------------------------------------------
README.TXT

Use this at your own risk. I don't warranty this software to do anything in
particular, useful or otherwise. I particularly disclaim any responsibility
for the software working at all. If my new Whizbang Calendar Doowop Control
doesn't meet your needs, please return it. You won't get a refund, but at
least you won't be bothered by bad software anymore :-p
-------------------------------------------------------------------------

This routine employs a couple of interesting techniques. First, notice the use of a default value for the output parameter. This makes the parameter optional. It's not unusual for input parameters to be optional, but you may not have considered doing the same thing for output parameters. I've done it this way to allow the procedure to be used two different ways. If all you want is the text file as a result set, you can omit the output parameter. The procedure will simply insert the text file into a table, then clean up the text a bit as it returns the rows as a result set. If, instead, you need the contents of the file loaded into a variable, you can pass in a varchar, and the procedure will fill it with the first 8,000 bytes of the file. We can't use a text field here because local text variables are not supported by Transact-SQL. Here's what the call would look like with an output parameter:

DECLARE @txt varchar(8000)
EXEC sp_readtextfile 'c:\readme.txt', @txt OUT
SELECT @txt

Another interesting technique in the routine is the use of xp_cmdshell to list the file. All we do to load the text file into a temporary table is pass xp_cmdshell a call to the operating system TYPE command. Because xp_cmdshell can be redirected to a table via INSERT EXEC, this serves as a simple mechanism for reading the file. We load the file into a table because xp_cmdshell replaces empty lines in the file with NULLunnecessary and not terribly aesthetic . Putting the data in a table allows us to use ISNULL() to filter out those unsightly NULLs.

NOTE

SQL Server's xp_readerrorlog routine can also read plain text files. To get xp_readerrorlog to read a text file other than an errorlog, pass -1 as its first parameter, followed by a comma and the name of the file you'd like to read. Sp_readtextfile provides a means of reading text files without requiring an extended procedure, but xp_readerrorlog works well if you don't mind dealing with extended procedures.