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