sp_readtextfile

for RuBoard

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.


for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net