Click here to monitor SSC
  • Av rating:
  • Total votes: 205
  • Total comments: 45
Phil Factor

Reading and Writing Files in SQL Server using T-SQL

10 April 2007

T-SQL stored procedures for reading and writing files can be hugely useful on those occasions where SQL Server’s standard techniques aren’t up to the task. Phil Factor’s FileSystem Object (FSO)-based stored procedures may well help you out in a tight corner. For more techniques, see The TSQL of Text Files.

SQL Server has never been short of ways to read from and write to 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 and writing 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 underlies 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 xp_cmdshell, and you will find that OSQL is wonderful for writing results to file, but occasionally I've found I need to do more than this.

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 TSQL. In this article, I provide examples of stored procedures that use this interface to allow you to:

  • Read lines of text from a file
  • Read a file into a SQL Server data type
  • Write out to a file
  • Get file details
  • Tidy up XML, XHTML or HTML code

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

Reading lines from a file

I have often pined for a simple function that will read information a line at a time, and to present to me a 'fake table' where each line of text is a row, with a primary key based on the line number. With such a function, one can then do one's own parsing and checking of data.

Well, here it is. Create the uftReadFileAsTable stored procedure in your test database, and try it out with something like:

 

Select line from
 Dbo.uftReadfileAsTable('MyPath','MyFileName')
where line not like '#%'
--where line doesnt begin with a hash

Just fill in an existing file name and path to the file you wish to read, instead of 'MyPath' and 'MyFileName', and away you go.

This is a method I use for reading web logs and gathering usage statistics. It is also useful where the data feed has to be validated before one can parse it into the final SQL data format.

Reading a file into a SQL Server data type

This is all very well, but how about something that reads a file in one gulp into a varchar or XML datatype? Perhaps you need to extract data from HTML, XHTML or some other format. Create the ufsReadfileAsString procedure and try something like…

Select dbo.ufsReadfileAsString ('MyPath','MyFileName')

Writing out a file

No problem – just create spWriteStringToFile and try:

execute spWriteStringToFile 'This article describes how to fully access the
local filesystem from SQL Server. It shows a
way of reading and writing data to file, and
accessing the details of the server's
filesystem using OLE Automation to access
the filesystem object'
, 'MyPath','MyFileName'

The path you use instead of 'MyPath' will have to exist, in this example.

Getting file details

If you need to find out the attributes of a particular file, then try out spFileDetails:

Execute spFileDetails 'c:\autoexec.bat'

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. I pause here just to give some minimum necessary background details on FSO, and on OLE Automation.

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

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:

  • sp_OACreate
  • sp_OADestroy
  • sp_OAGetProperty
  • sp_OASetProperty
  • sp_OAMethod
  • sp_OAGetErrorInfo
  • sp_OAStop

The attached files have many examples on how to use these procedures and they are well covered on Books on Line, so I won't bother to repeat the details here.

The FileSystem Object

The FileSystem Object is a COM object that is provided primarily for scripting languages such as Jscript, ASP, and VBscript. It provides everything that is necessary for file or folder operations. It also allows for most simple file reading and writing operations, and to gather information about drives, directories and files.

The FSO allows you to manipulate and shred path names, to copy, move, delete or create files or folders. You can check for their existence or get a number of objects and collections. These are…

  • Drive Object

This provides information about a logical drive (physical, or network) attached to the system, such as its share name, type, total space, and how much room is available

  • Drives Collection

This will give a list of the physical or logical drives attached to the system and includes all drives, regardless of type. (Removable-media drives need not have media inserted for them to appear in this collection).

  • File Object

This will allow you to create, delete, or move a file, and to find out the file name, path, and other properties. It has a method to open a file as a text stream.

  • Files Collection

This provides a list of all files contained within a folder.

  • Folder Object

The Folder object has methods that allow you to create, delete, or move folders. Also there are several properties that return folder names, creation date, paths and so on.

  • Folders Collection

This provides a list of all the folders within a Folder.

  • TextStream Object

This object is extraordinarily useful as it provides the means to read and write to text files.

All of these methods, properties and collections are readily available from Books on Line and W3Schools. I also find Dino Esposito's book 'Windows Script Host' (Wrox 1999) very useful

The HTML Tidier

We end up with a rather more esoteric procedure that we will throw into the pot just to show what can be achieved with FSO. This is a procedure that corrects and tidies up any XML, XHTML or HTML. It will take any file and format it prettily and check for any errors. It is an example which illustrates several techniques and shows how one can integrate a DOS application.

This stored procedure requires the latest version of HTMLTidy (March 2007) to be installed on your server. I put it in System32, as it is a simple MSDOS.EXE file.

HTML Tidy was written by Dave Raggett, who works on assignment to the World Wide Web Consortium where he is the W3C lead for Voice and Multimodal. It has become the standard way of checking XML and HTML files for syntax. It is a remarkable tool that combines a Lint and a prettifier. It has now been adopted by an enthusiastic band of people who work through W3C and SourceForge to maintain it. It also requires you to set the configuration that I have embedded in the source of the stored procedure, but which you can supply as a parameter to over-ride the defaults I have chosen. Before twiddling with these parameters you'd be best advised to check the documentation at http://tidy.sourceforge.net/docs/quickref.html

Once everything is in place, all you have to do is to supply the input as a string and the spHTMLtidy stored procedure will put the tidied version in the output string and fill another string with all the warning and error information.

Declare @ProcessedFile varchar(max)
Declare @Messages varchar(max)
Declare @UnprocessedFile varchar(max)
Select @UnprocessedFile = '<html>
<head <title>awful HTML</head>
<body>
<p>This is my first real HTML<br><div>
<table><tr><th>What a mess</tr>
<tr><td></td>this is supposed to be in the table</tr><td></td>
<tr> this is awful<td>and this is a line</td></tr>
</table>
</body>
</html>
'

Execute spHTMLtidy @UnprocessedFile, @processedFile output,
@Messages output
Select
[after]=@ProcessedFile

This will produce a tidied up XHTML like this…

<?xml version="1.0" encoding="us-ascii"?>
<!
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<
html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>
      awful HTML
    </title>
  </head>
  <body>
    <p>
      This is my first real HTML
      <br />
    </p>
    <div>
      this is supposed to be in the table this is awful
      <table>
        <tr>
          <th>
            What a mess
          </th>
        </tr>
        <tr>
          <td></td>
       
</tr>
        <tr>
          <td></td>
        </tr>
       
<tr>
          <td>
            and this is a line
          </td>
        </tr>
      </table>
    </div>
  </body>
</
html>

If you are using SQL Server 2000, then I'm sorry to say you will be restricted to source code of 8000 characters, but I guessed that this routine would be more useful in SQL Server 2005 which has a native XML data type for which this routine would be ideal.

Summary

SQL Server is not designed as a means of file and directory manipulation, and for most of the time it is not the appropriate place in an application to do it. However, now and then in the life of a DBA or database developer, it is nice to have the functionality to hand and, for this sort of work, the File System Object is ideal. I've used it mostly in combination with DMO for automating routine DBA chores, such as copying and updating server configurations. It is particularly handy for creating test cells, and test configurations. I also find it is ideal for the occasional one-off task, such as importing and extracting the data from a large number of XHTML files, or indexing a large text-based repository of data.

Phil returns to this theme in The TSQL of Text Files and also, with Robyn Page, in workbenches such as Importing Text-based data: Workbench. they show more advanced ways of processing text-based information in Getting HTML Data: Workbench and TSQL Regular Expression Workbench

Even Phil Factor codes faster with SQL Prompt 4...download a free trial version and check it out.

Phil Factor

Author profile:

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Google + To translate this article...

Search for other articles by Phil Factor

Rate this article:   Avg rating: from a total of 205 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: OLE vs CLR
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 12, 2007 at 4:25 AM
Message: Given that the OLE automation starts up in the shared memory space of SQL Server there is possiblity of corruption if the one of the scripts does not correctly clean up all its objects.

An alternative might be to use the CLR functionality with EXTERNAL_ACCESS permissions set or a linked server.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

Both OLE, CLR and xp_cmdshell can be turned on and off using the SQL Server Surface Area Configuration tool.

Subject: Need to read file from the internet
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 25, 2007 at 5:42 AM
Message: I am not able to read file from internet.

Subject: re: Need to read file from the internet
Posted by: Phil Factor (view profile)
Posted on: Thursday, April 26, 2007 at 7:53 AM
Message: I hope I've understood you right....

I've been caught out the same way. The icon in the rounded box at the top of the article (with the article rating in it) doesn't actually do anything.

You need to click on the words 'CODE DOWNLOAD' next to the icon. The text for the routines can also be reached via anchors in the text.


Subject: ansi vs unicode
Posted by: frosenberg@globaltouchtelecom.com (view profile)
Posted on: Friday, April 27, 2007 at 3:59 PM
Message: this is excellent, thank you!

while trying spWriteStringToFile, it appears to write a unicode file, is that correct? there must be a parameter to change this to ansi, could you point this out?

thanks!

fred

Subject: Wrtie data to anther pc.
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 16, 2007 at 1:41 PM
Message: This proc is great when I write data from server to local pc.
but, I try to write some data to another pc by using ip address, I got the msg:
Server: Msg 50000, Level 16, State 1, Procedure spWriteStringToFile, Line 48

Error whilst Creating file "188.188.8.8\d$\Webroot\FTP\\test.txt"

Can anybody help with this?

Thank,

Tom

Subject: Re: Wrtie data to anther pc.
Posted by: Phil Factor (view profile)
Posted on: Wednesday, May 16, 2007 at 2:18 PM
Message: Have a look at that path. there is a double \ there! take the '\' out of the end of the path parameter and all should be OK

Subject: thanks.
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 16, 2007 at 2:45 PM
Message: I did use following as path:

'\\188.188.8.8\d$\Webroot\FTP\\test.txt'

Do you think this is a permission issue.

thanks.

Tom

Subject: FileSystemObject
Posted by: Shreesh Trivedi (not signed in)
Posted on: Monday, May 28, 2007 at 5:43 AM
Message: The FileSystemObject is limited to the scope of LOCAL File System of the BOX. The File System on another boxes are taken care by those boxes themselves. Any thing in Between these is the Network [LAN]. FileSystemObject is not suitable to work on LAN.
Only workaround is you map the target location as a drive on your local box to exnd the scope of FileSystemObject to target locations accross LAN.

Subject: re: FileSystemObject
Posted by: Phil Factor (view profile)
Posted on: Tuesday, May 29, 2007 at 3:55 AM
Message: >>Shreesh Trivedi said..
>>The FileSystemObject is limited to the scope of >>LOCAL File System of the BOX

I've just tried it here again and it works fine across a network, both using a network share and via a '\\server\share' filepath. There are plenty of examples of this on MSDN too. Could you be thinking of the problem outlined in http://support.microsoft.com/kb/197964 ? Not what we're doing here.

The original problem was that he is using
'\\188.188.8.8\d$\Webroot\FTP\\test.txt'
rather than
'\\188.188.8.8\d$\Webroot\FTP\test.txt'
there could be permission problems as well.

Subject: Network User
Posted by: Daniel Bates (not signed in)
Posted on: Saturday, June 09, 2007 at 1:41 PM
Message: Verify the account you're running the script under has rights to the other workstation/server. If you use the SQL Server agent or DTS, verify the user account the server runs under has rights to the network share. Also take in account you're attempting to access the default share, which by default only allows Administrators access. If you change this and decide to use a windows file share, verify the share permissions are set as well.

Subject: How to Read & Wrtie Binary files
Posted by: Anonymous (not signed in)
Posted on: Wednesday, June 13, 2007 at 7:34 PM
Message: How to Read & Wrtie Binary files

Subject: re: How to Read & Wrtie Binary files
Posted by: Phil Factor (view profile)
Posted on: Thursday, June 28, 2007 at 2:17 AM
Message: The FSO will allow you to write binary files. The biggest problem you have is that Microsoft either forgot, or were to idle to bother with, updating the COM interface in SQL Server 2005, so you can't use the (MAX) data types with any COM application. You'll see, in the examples I give, that one has to do it in 8000 byte gulps! (4000 unicode characters). A simple operation is turned into an unpleasant one. You can always use BCP!

Subject: How to set database file permitions
Posted by: Nikola (not signed in)
Posted on: Monday, July 02, 2007 at 7:19 AM
Message: Is there any way I can add ntfs permitions to database file using SQL?

Subject: Anonymous comments disabled
Posted by: AnnaL (view profile)
Posted on: Monday, September 17, 2007 at 10:02 AM
Message: We've had to disable anonymous comments on this article due to relentless spamming.

To post a comment please sign in, or register if you are not already a member.

Subject: spWriteStringToFile fails midway
Posted by: harry (view profile)
Posted on: Friday, December 07, 2007 at 12:30 AM
Message: I am using your SP to append data in to a text file repeatedly...I made a modification to open the file in append mode. here is the code
--- Check File already exist
EXEC sp_OAMethod @objFileSystem, 'FileExists', @Append out, @FileAndPath

IF @Append = 1
BEGIN
--open the text stream for append
IF @HR=0 EXECUTE @hr = sp_OAMethod @objFileSystem,'OpenTextFile', @objTextStream OUTPUT, @FileAndPath, 8

END
ELSE
BEGIN

--Create the text file for write
IF @HR=0 EXECUTE @hr = sp_OAMethod @objFileSystem,'CreateTextFile',@objTextStream OUTPUT,@FileAndPath,-1

END

however after calling the SP multiple times, at a point it fails as it is not able to open the file in append mode.
@hr value returned from the statement
EXECUTE @hr = sp_OAMethod @objFileSystem,'OpenTextFile', @objTextStream OUTPUT, @FileAndPath, 8
is 10007.
Could you tell me why is it happening midway...and what could be the workaround for this.

Subject: re: spWriteStringToFile fails midway
Posted by: Phil Factor (view profile)
Posted on: Monday, February 18, 2008 at 3:33 AM
Message: It looks to me as if you aren't destroying the OAObjects you are creating. This is the usual thing that happens if you forget to do that

Subject: append the file using spWriteStringToFile
Posted by: mahesha_dba (view profile)
Posted on: Monday, June 30, 2008 at 9:23 AM
Message: could you help me out . i want to append the file using this sp. please mail me soon.

spWriteStringToFile

Reg
Mahesh

Subject: Limit of 255 Files?
Posted by: Balance (view profile)
Posted on: Friday, September 05, 2008 at 10:08 AM
Message: I've been trying to use spWriteFileToString to output a text file for each record in my database but the process appears to fail on the 256th file. I've changed my code to put the first 255 files in folder 1, 2nd 255 files to folder 2 etc but the 256th file always fails - my code in a loop of records is :

-- loop of policies in table
select @text= --some data, I do check to ensure there is content
select @file = @Policy+'.txt'
select @tpath = @path + '\'+cast(@loop as varchar)
exec utility..spWriteStringToFile @text, @tpath, @file
--end loop

Can anyone shed any light please - the folders have identical rights and there is nothing identifiable wrong with the filename generated (I've tried skipping offending @policy valus with no change).

Many thanks

Subject: Repeated function call at the end of spWriteStringToFile
Posted by: JoeSchmoe007 (view profile)
Posted on: Friday, September 05, 2008 at 6:18 PM
Message: At the end of spWriteStringToFile you execute the same SP twice:

EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

Is this by design?

Subject: You can write to ansi instead of unicode if you change TRUE to FALSE
Posted by: xerosaburu (view profile)
Posted on: Tuesday, October 14, 2008 at 4:30 PM
Message: In the procedure, if you change the parameter after @Filepath to False, it will output in ANSI

Unicode (True):
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True

ANSI (False):
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,False

Subject: You can write to ansi instead of unicode if you change TRUE to FALSE
Posted by: xerosaburu (view profile)
Posted on: Tuesday, October 14, 2008 at 4:31 PM
Message: In the procedure, if you change the parameter after @Filepath to False, it will output in ANSI

Unicode (True):
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True

ANSI (False):
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,False

Subject: Writing to a file from SQL
Posted by: ltimms (view profile)
Posted on: Tuesday, November 25, 2008 at 9:01 AM
Message: I was extremely impressed by this article as it got to the point and was sure you understood the concepts involved.

Subject: Question regarding permission of sp_OA* stored proc
Posted by: livezone (view profile)
Posted on: Wednesday, November 26, 2008 at 10:42 AM
Message: If my user is executing a stored proc. (from webpage) to output data from sql to text file which is using sp_OA* stored proc., Do I have to give them permission to individual stored proc or I have to make them a member of sysadmin fixed role?

Permission
sp_OACreate requires membership in the sysadmin fixed server role.
Thanks
Shafiq

Subject: @fileandpath needs more room
Posted by: nskerl (view profile)
Posted on: Thursday, December 11, 2008 at 8:13 PM
Message: This may account for the sporadic errors:

@Path VARCHAR(255)
@Filename VARCHAR(100)

@fileAndPath varchar(80)

@fileAndPath = @path+'\'+@filename


Its likely @fileAndPath will truncate the result of @path + @filename.

Subject: 256 limit
Posted by: nskerl (view profile)
Posted on: Thursday, December 11, 2008 at 8:32 PM
Message: As JoeSchmoe007 mentioned, you exec the same destroy at the end. This is imposing the 256 limit (when exec in batch loop).

Should be:

EXECUTE sp_OADestroy @objTextStream
execute sp_OADestroy @objFileSystem

Subject: Only getting the first character with the ufsReadfileAsString
Posted by: lonuel (view profile)
Posted on: Wednesday, January 21, 2009 at 10:27 AM
Message: I am using sql server 2008 and windows server 2008. I am only getting the first character with the ufsReadfileAsString. Any ideas would be greatly appreciated.
Thanks,
Lon

Subject: Only getting the first character with the ufsReadfileAsString
Posted by: lonuel (view profile)
Posted on: Wednesday, January 21, 2009 at 11:25 AM
Message: I am using sql server 2008 and windows server 2008. I am only getting the first character with the ufsReadfileAsString. Any ideas would be greatly appreciated.
Thanks,
Lon

Subject: Changed to ANSI to Fix
Posted by: lonuel (view profile)
Posted on: Wednesday, January 21, 2009 at 12:38 PM
Message: Changed to ANSI to Fix

Subject: spWriteStringToFile error -2146828212
Posted by: SMYE (view profile)
Posted on: Wednesday, January 28, 2009 at 3:09 PM
Message: I'm trying to use this SP and it gets a @hr value of -2146828212 when running:
execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True

Does anyone know what the problem is? Thanks.

Subject: It makes error sometimes like "Create file error...".
Posted by: nvidia2 (view profile)
Posted on: Monday, September 07, 2009 at 4:08 AM
Message: I found a syntax error at destory filesystem object.
Replace last line :
EXECUTE sp_OADestroy @objFileSystem
--> EXECUTE sp_OADestroy @objFileSystem

Subject: Retrieve documents from their containts
Posted by: prakash.gupta83@hotmail.com (view profile)
Posted on: Thursday, December 03, 2009 at 6:15 AM
Message: I am uploading word,pdf files and storing these file in a drive(C) within a folder and store path of this folder into database.
I want to search any word within document(word,pdf files) and it should display all the files(word,pdf files) containing that word.

any help

Thanks
Prakash Gupta
prakash.gupta83@hotmail.com

Subject: spWriteToFile to append data
Posted by: curious (view profile)
Posted on: Wednesday, December 16, 2009 at 4:28 PM
Message: I understand that spWriteToFile is to write text to a file. This store procedure is excellent, but doesn't work if your data are comprised of rows and columns. For example,
ID Name
1 AAAA
2 BBBB
3 CCCC
I would like to have all rows and columns in my final file. The result would be either the first row or last row. How can I append BBBB and CCCC to AAAA? Can you please help?

Thank you veru much.

Subject: ansi vs unicode
Posted by: fhanlon (view profile)
Posted on: Tuesday, February 16, 2010 at 11:36 AM
Message: I also had a problem with unicode files. To change this write out an ansi file, find the line of code like:

IF @hr=0 EXECUTE @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,TRUE

and change it to:

IF @hr=0 EXECUTE @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath

The TRUE parameter means UNICODE, the default is ANSI

Subject: More than 8000 characters in SQL 2000
Posted by: Emtucifor (view profile)
Posted on: Friday, March 19, 2010 at 11:15 AM
Message: > If you are using SQL Server 2000, then I'm sorry
> to say you will be restricted to source code of
> 8000 characters

This is not true at all. In SQL 2000, a stored procedure can have parameters of type text/ntext. Even though *columns* and string literals put into *columns* in SQL 2000 can never be longer than 8000 characters, the SQL query parser can handle *queries* of up to 2147483647 bytes long, which is a pretty darn big XML document. You'd execute that SP like this:

EXEC @ReadThisXML 'Many more than 8000 characters here.'

For more information on SQL Server character limitations, see http://blogs.lessthandot.com/index.php/All/?p=386 .

Subject: Whoops
Posted by: Emtucifor (view profile)
Posted on: Friday, March 19, 2010 at 12:02 PM
Message: Sorry about that. That should read:

EXEC ReadThisXML 'Many more than 8000 characters here.'

The link above also has more details on working with text/ntext columns (which of course can hold more than 8000 characters).

Subject: Re: Whoops
Posted by: Phil Factor (view profile)
Posted on: Saturday, March 20, 2010 at 11:20 AM
Message: Well certainly, TEXT supports var larger BLOBs, but there are a lot of restrictions on their use. See the source of the SQL Prettifier, which I wrote for SQL Server 2000 using a TEXT input, and does not suffer the 8000 character limit. The SQL Server 2000 limit I was mentioning in this article  refers to VarChars since, as far as I'm aware, OLEDB  in SQL 2000 does not support the use of TEXT variables. I'd be fascinated if you can prove me wrong! You can certainly use the FSO from a routine that takes a TEXT variable and chops it up into 8000 character chunks, but filling a text variable in the first place is a nightmre!

Subject: Excellent presentation
Posted by: dianagele (view profile)
Posted on: Wednesday, June 02, 2010 at 5:31 PM
Message: good presentation how-to.

Subject: Error writing file into a table
Posted by: prad_talks (view profile)
Posted on: Tuesday, August 03, 2010 at 5:32 AM
Message: Hi,

I tried using the query you created 'uftReadFileAsTable' to read a stored procedure saved in my D: drive, but it gave me the following error.
Error whilst Opening file "D:\my_test_proc.sql",

Do you know why I might be getting this?

Subject: Error in DLL
Posted by: AbhishekM (view profile)
Posted on: Tuesday, December 07, 2010 at 5:20 AM
Message: Hi, I am getting the following error.

Msg 50000, Level 16, State 1, Procedure spWriteStringToFile, Line 48
Error whilst opening the File System Object, A dynamic link library (DLL) initialization routine failed.

System Config : Windows 2003 server SP2, 64 bit
SQL server 2005 SP2

Not sure whether its due to Scrrun.dll or because of 64 bit OS. Please help..

Thanks

Subject: Error in DLL
Posted by: AbhishekM (view profile)
Posted on: Tuesday, December 07, 2010 at 9:58 PM
Message: Hi, I am getting the following error.

Msg 50000, Level 16, State 1, Procedure spWriteStringToFile, Line 48
Error whilst opening the File System Object, A dynamic link library (DLL) initialization routine failed.

System Config : Windows 2003 server SP2, 64 bit
SQL server 2005 SP2

Not sure whether its due to Scrrun.dll or because of 64 bit OS. Please help..

Thanks

Subject: IF EOF
Posted by: remixtwelve (view profile)
Posted on: Thursday, January 26, 2012 at 2:12 PM
Message: Great article, thank you!

So my

Execute spFileDetails @FilePath

Works well.

If it doesn't return a result I'd like to have an IF statement

IF EOF
BEGIN
---Do Something here
END

But how to I acknowledge if the return was EOF or Not ?

Thanks,
Chuck.


Subject: thank you both author and nskerl
Posted by: Epon (view profile)
Posted on: Monday, February 13, 2012 at 12:06 AM
Message: thank you for an author's guidance.
and big thanks for nskerl.
i spend more than 3 hours finding the 255 limitations. (didnt read entire replies before using the code.)

now i can construct tons of html files from my very small t-sql procedure.

big thanks!

Subject: Write to File
Posted by: jarce (view profile)
Posted on: Friday, February 17, 2012 at 10:54 AM
Message: Love the write to file sp. Worked like a charm. My follow on question is: How do I create a carriage return so there are multiple rows? I tried \n but this only adds the text '\n' to my file.

I ask because I'm doing a 'type' command later on in the process and I need to have a carriage return in the first file in order for it to work properly.

Subject: update
Posted by: jarce (view profile)
Posted on: Tuesday, February 21, 2012 at 8:52 AM
Message: Tried char(13), but found out that somehow after executing the stored procedure, it sets a hex encoding that changes the language. Hence, when I append anything it looks like a different language.

Subject: sql lite
Posted by: seejoc (view profile)
Posted on: Sunday, July 15, 2012 at 6:16 AM
Message: I am pretty sure you can use sqlite to write in data in sql format without to much overhead.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.