{"id":504,"date":"2009-01-19T00:00:00","date_gmt":"2009-01-19T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-tsql-of-text-files\/"},"modified":"2021-09-29T16:22:07","modified_gmt":"2021-09-29T16:22:07","slug":"the-tsql-of-text-files","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-tsql-of-text-files\/","title":{"rendered":"The TSQL of Text Files"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">This article revisits the theme of an article I wrote for Simple-Talk two years ago called <a href=\"http:\/\/www.simple-talk.com\/sql\/t-sql-programming\/reading-and-writing-files-in-sql-server-using-t-sql\/\">Reading and Writing Files in SQL Server using T-SQL<\/a> , which to my amazement, has proved to be the most popular article I&#8217;ve ever written.<\/p>\n<p class=\"start\">In this belated second part (I&#8217;ll assume you have SQL Server 2005 or 2008 most of the time) We&#8217;re going to show you the TSQL you can use to<\/p>\n<ul>\n<li>Get a List of Files in a directory\/folder, with, if you with, the subfolders and attributes such as file size, revision date, or short-form.<\/li>\n<li>Access the SHELL.APPLICATION to do file operations<\/li>\n<li>Read data from file into a TSQL variable<\/li>\n<li>Read data into a table, each line in a table row<\/li>\n<li>Write data from a TSQL variable into a file<\/li>\n<li>Write the String-based results of a SQL Expression into a file<\/li>\n<\/ul>\n<p>These are simple operations, but it is some of the simplest operations in a database that can prove to be the most taxing:\u00a0 By way of contrast when you are working in DOS, the DIR or ls commands are one of the first commands that one learns. The operation of getting data to or from files is so easy; but not always so in SQL Server. This is because SQL Server is usually the worst place to do such an operation. Very occasionally, it isn&#8217;t. In the hard reality of life in an IT department, it is sometimes the only practical option. For the majority of operations, it is the obvious choice to use SSIS\/DTS, but not invariably.<\/p>\n<p>The first problem you face is security. The Database User that runs these routines that I show will need &#8216;special powers&#8217;, powers that can&#8217;t possibly be given to general users for reasons of security .It also requires you to open up hatches in your database security &#8216;Surface Area Configuration&#8217;, that are normally battened down, such as OLE Automation, and use of the command line. (you&#8217;ll probably need to use the utility called &#8216;Surface Area Configuration for Features&#8217;) We&#8217;re not going to cover these issues here, or show you ways to solve the problem of keeping your database secure. Instead, refer to <a href=\"http:\/\/www.simple-talk.com\/sql\/t-sql-programming\/sql-server-security-workbench-part-1\/\">The SQL Server Security Workbench<\/a> and <a href=\"http:\/\/www.simple-talk.com\/sql\/t-sql-programming\/process-delegation-workbench\">The Process Delegation workbench<\/a>\/<\/p>\n<h2>Getting a List of Files in a directory\/folder.<\/h2>\n<p class=\"start\">Why should you ever want to get a list of files in SQL Server, perhaps with their properties, such as creation date or size as well? Most commonly, it is when one wants to archive off old backup files as part of a maintenance program, or delete old log-shipping files. I often find myself needing a list of files from a directory and all its subdirectories when I&#8217;m importing HTML or data files into a database. There are quite a few occasions where one wants an automatic data feed based on files appearing in a directory. I&#8217;m routinely having to pick up text-based log files of various description, and import them into tables. I&#8217;ve even used routines like these to index up an intranet store of documents for fast access. All this requires you to be able to check what&#8217;s on disk. read stuff in, and sometimes write stuff out., preferably at lightening speed. It is not always as simple as one might wish.<\/p>\n<p>The most obvious way of getting a list of files into TSQL as a table is to use the following technique. I give it to you in a stored procedure that you will find is of little use except where it is part of a longer process. I&#8217;ll explain why later.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\r\n--------------------------------------------------------------------\r\nIF OBJECT_ID (N'dbo.ListPaths') IS NOT NULL\r\n\u00a0\u00a0 DROP PROCEDURE dbo.ListPaths\r\nGO\r\nCREATE PROCEDURE dbo.ListPaths\r\n@FileSpec VARCHAR(2000),\r\n@order VARCHAR (80) = '\/O-D'--sort by date time oldest first\r\n\/*\r\nproduce a table with a single column consisting of the path of every file, including subdirectories, of the directory specified You can specify the order in the order parameter\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 N\u00a0\u00a0By name (alphabetic)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 S\u00a0\u00a0By size (smallest first)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 E\u00a0\u00a0By extension (alphabetic)\u00a0\u00a0D\u00a0\u00a0By date\/time (oldest first)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 G\u00a0\u00a0Group directories first\u00a0\u00a0\u00a0\u00a0-\u00a0\u00a0Prefix to reverse order\r\n\u00a0\r\n*\/\r\nAS\r\nDECLARE @myfiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, FullPath VARCHAR(2000))\r\nDECLARE @CommandLine VARCHAR(4000)\r\nIF @order IS NOT NULL -- abort if the order is silly\r\n\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0 SELECT @CommandLine =LEFT('dir \"' + @FileSpec + '\" \/A-D \/B \/S '+@order,4000)\r\n\u00a0\u00a0 \/*\r\n\u00a0\u00a0\u00a0\u00a0 \/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Displays files with specified attributes.\r\n\u00a0\u00a0\u00a0\u00a0 attributes\u00a0\u00a0 D\u00a0\u00a0Directories\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0R\u00a0\u00a0Read-only files\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0H\u00a0\u00a0Hidden files\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 A\u00a0\u00a0Files ready for archiving\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0S\u00a0\u00a0System files\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -\u00a0\u00a0Prefix meaning not\r\n\u00a0\u00a0\u00a0\u00a0 \/B\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Uses bare format (no heading information or summary).\r\n\u00a0\u00a0\u00a0\u00a0 \/S\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Displays files in specified directory and all subdirectories.\r\n\u00a0\u00a0 *\/\r\n\u00a0\u00a0 INSERT INTO @MyFiles (FullPath)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTE xp_cmdshell @CommandLine\r\n\u00a0\u00a0 DELETE FROM @MyFiles WHERE fullpath IS NULL OR fullpath='File Not Found'\r\n\u00a0\u00a0 END\r\nSELECT fullpath FROM @MyFiles\r\n-------------------------------------------------------------\r\nGO\r\nEXECUTE listpaths 'C:\\Workbench\\*.txt' \r\n<\/pre>\n<p>&#8230;and you get an error saying\u00a0 &#8216;<code>An INSERT EXEC statement cannot be nested<\/code>&#8216;<\/p>\n<p>There are other problems to tackle. You only get the full path when you use the \/S switch (to list subdirectories) and the DIR command goes doolally if you use the \/S switch and if it can&#8217;t find the directory spec. Also, if you want to use this to archive all backups made over three weeks ago then you&#8217;re stuffed. the bare command doesn&#8217;t pass back the file sizes, or dates, and the information can&#8217;t be got reliably from the DIR command anyway for a variety of reasons (varying Date Settings, DOS version differences in the column settings, not all useful info there etc). Another problem is that you usually want the directory too without having to parse the filename. If you omit the \/B \/S, you get the filename, with the \/B you get the whole path: Hassle.<\/p>\n<p>There is one problem we can solve pretty smartly. It is due to the fact that it is a stored procedure rather than a function. <code>insert into ...Exec<\/code> cannot be nested. You cannot use the obvious solution of doing an <code>xp_cmdshell<\/code> in a function because insert into&#8230;execute isn&#8217;t allowed in a function. You can do an <code>OPENROWSET<\/code> to do the <code>EXECUTE<\/code>, but the <code>OPENROWSET<\/code> only takes string literals, not variables, so you cannot specify the directory to get a directory listing from! This requires patience and cunning. Our first technique will be to use an XML output variable.<br \/>\n \u00a0<\/p>\n<pre>--------------------------------------------------------------------\r\nIF OBJECT_ID (N'dbo.ListPathsXML') IS NOT NULL\r\n\u00a0\u00a0 DROP PROCEDURE dbo.ListPathsXML\r\nGO\r\nCREATE PROCEDURE dbo.ListPathsXML\r\n@FileSpec VARCHAR(2000),\r\n@order VARCHAR (80) = '\/O-D',--sort by date time oldest first\r\n@xmlFileList XML OUTPUT\r\n\r\nAS\r\nDECLARE @myfiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, FullPath VARCHAR(2000))\r\nDECLARE @CommandLine VARCHAR(4000)\r\nIF @order IS NOT NULL -- abort if the order is silly\r\n\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0 SELECT @CommandLine =LEFT('dir \"' + @FileSpec + '\" \/A-D \/B \/S '+@order,4000)\r\n\u00a0\u00a0 INSERT INTO @MyFiles (FullPath)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTE xp_cmdshell @CommandLine\r\n\u00a0\u00a0 DELETE FROM @MyFiles WHERE fullpath IS NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR fullpath = 'File Not Found'\r\n\u00a0\u00a0 END\r\nSET @xmlFileList = (SELECT fullpath FROM @MyFiles\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0XML PATH('thefile'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ROOT('thefiles'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0TYPE)\r\nGO<\/pre>\n<p>We can use the routine like this&#8230;<\/p>\n<pre>DECLARE @FileList XML\r\nEXECUTE ListPathsXML 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\LOG',\r\n\u00a0\u00a0\u00a0\u00a0DEFAULT , @XMLFileList = @FileList OUTPUT\r\nSELECT\u00a0\u00a0 x.thefile.value('fullpath[1]', 'varchar(2000)') AS [path]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0\u00a0@FileList.nodes('\/\/thefiles\/thefile') AS x ( thefile )\r\n------------------------------------------------------------------------  \r\n<\/pre>\n<p>so, fired with enthusiasm, we construct a solution that allows us to do both subdirectory and simple directory listings, and tells us the directory that every file was found in. Just to show off a bit, we can get it just to list the directories.<\/p>\n<p>The code for the procedure <code>spGetFilePaths<\/code> is here. <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/634-spGetFilePaths.htm\">You need to click here to see the code!<\/a> And then, maybe you could use it something like this, to get a listing of files<\/p>\n<pre>DECLARE @FileList XML, @FilePath VARCHAR(2000)\r\nSELECT @FilePath='C:\\Program Files\\Microsoft SQL Server'\r\nEXECUTE spGetFilePaths @FilePath, '*.*',\r\n\u00a0\u00a0\u00a0\u00a0@subdirectories = &gt;1, @XMLFileList = @FileList OUTPUT\r\nSELECT directoryList.* FROM\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT\u00a0\u00a0@filepath AS BaseDirectory,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 x.thefile.value('thePath[1]', 'varchar(200)') AS Fullpath,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0x.thefile.value('filename[1]', 'varchar(200)') AS [filename],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0x.thefile.value('path[1]', 'varchar(200)') AS [Subdirectory]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0\u00a0@FileList.nodes('\/\/thefiles\/thefile') AS x ( thefile ))directoryList <\/pre>\n<p>Let&#8217;s try a more sophisticated approach to try to encapsulate it in a function. We can use OLE automation in functions but there is a big snag. The File System Object (FSO) doesn&#8217;t allow file listings from <code>xp_oaMethod <\/code>since the item property of a folder doesn&#8217;t have an index value. Strangely, Microsoft also have a slightly flawed method that uses <code>SHELL.APPLICATION.<\/code> This last one works, more or less (It hates it if the directory you specify doesn&#8217;t exist, so we have to program around that)<\/p>\n<h2>Accessing the SHELL.APPLICATION to get a directory listing.<\/h2>\n<p>Here is a function that uses <code>SHELL.APPLICATION<\/code> automation. <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/634-dboDIR.htm\">You&#8217;ll have to click here to see the source code<\/a>!<\/p>\n<p>As you can see, it makes the whole process a lot simpler, but it is very slow.<\/p>\n<pre>--list all subdirectories\u00a0\u00a0beginning with M from \"c:\\program files\" *\/\r\nSELECT [path] FROM dbo.dir('c:\\program files')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE name LIKE 'm%' AND IsFolder =1\r\n--list every file and subdirectory in the WINNT directory\r\nSELECT\u00a0\u00a0* FROM dbo.dir('c:\\winnt') <\/pre>\n<p>This routine returns a table representing all the items in a folder. It takes as parameter the path to the folder. It does not take wildcards in the same way as a DIR command. Instead, you would be expected to filter the Table results of the function using SQL commands.<\/p>\n<p>Notice that the size of the item (e.g. file) is not returned by this function. It is just not in the list of properties. Microsoft can be cussed at times.<\/p>\n<p>This function uses the Windows Shell COM object via OLE automation. It opens a folder and iterates though the items listing their relevant properties. You can use the SHELL object to do all manner of things such as printing, copying, and moving filesystem objects, accessing the registry and so on. Powerful medicine.<\/p>\n<p>This seems great. But the problem is that it doesn&#8217;t iterate over subdirectories. Oops. Also, it is a bit short on properties. Still it does for most purposes. It isn&#8217;t a complete substitute for using DIR because it is s&#8230;l&#8230;o..w. Most of the time, this doesn&#8217;t matter much, but just occasionally it is a pain.<\/p>\n<p>So we now do a function that addresses most of these problems except for the slowness.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/634-dboFiles.htm\">You will have to click here to<\/a> see the source code for the <code>dbo.files <\/code>function. This is an example of how it might be used.<\/p>\n<pre>SELECT SUM(size), COUNT(*)\r\nFROM dbo.files ('C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\LOG', 1,1)\r\n<\/pre>\n<h2>Getting Text into a TSQL Variable<\/h2>\n<p>Now we have solved the awful problem of getting the names of the files we want, we can have some fun. It is now easy to read a file into SQL Server. There are a number of ways of getting stuff in to SQL Server but this is the simplest.<\/p>\n<pre>DECLARE @LotsOfText VARCHAR(MAX)\r\nSELECT\u00a0\u00a0@LotsOfText = BulkColumn\r\nFROM\u00a0\u00a0\u00a0\u00a0OPENROWSET(BULK 'C:\\workbench\\MyBigfile.txt', SINGLE_BLOB) AS x\u00a0\u00a0 <\/pre>\n<p>Yes, but some silly old moo in Microsoft has cobbled the <code>OPENROWSET<\/code> function so it won&#8217;t take a variable so unless you hard-wire your <code>OPENROWSET<\/code> commands to literals rather than variables (of course you don&#8217;t) you can&#8217;t use it.<\/p>\n<p>However, there is a cunning trick you can use with <code>sp_ExecuteSQL<\/code> to get round this. It is easier to demonstrate than explain.<\/p>\n<p>So let&#8217;s try it out by writing a routine to find a particular string, &#8216;Explosive&#8217; in this example, in a whole lot of files. We&#8217;ll use our ListPathsXML procedure from our file-listing armoury just because we only need the paths, but no other file information and we want it fast.<\/p>\n<pre>DECLARE @LotsOfText NVARCHAR(MAX),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ii INT,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @iiMax INT,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @File VARCHAR(2000),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Command NVARCHAR(4000)\r\nDECLARE @files TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, [Path] VARCHAR(2000))\r\n\r\nDECLARE @FileList XML\r\nEXECUTE ListPathsXML 'C:\\MyDocuments\\MyData.TXT',\r\n\u00a0\u00a0\u00a0\u00a0DEFAULT , @XMLFileList = @FileList OUTPUT\r\n\r\nINSERT INTO @files(path)\r\n\u00a0\u00a0 SELECT\u00a0\u00a0 x.thefile.value('fullpath[1]', 'varchar(2000)') AS [path]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0\u00a0@FileList.nodes('\/\/thefiles\/thefile') AS x ( thefile )\r\n--don't look at the current errorlog!\r\nSELECT @ii=1, @iiMax=MAX(MyID) FROM @Files\r\nWHILE @ii&lt;=@iiMax\r\n\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0 SELECT @File= [path] FROM @files WHERE MyID=@ii\r\n\u00a0\u00a0 SELECT @command='SELECT\u00a0\u00a0@LotsOfText = BulkColumn\r\nFROM\u00a0\u00a0\u00a0\u00a0OPENROWSET(BULK '''+REPLACE(@FILE,'''','''''')+''', SINGLE_BLOB) AS x'\r\n\u00a0\u00a0 EXECUTE sp_ExecuteSQL @command, N'@lotsOfText nvarchar(max) output ',@lotsoftext output\r\n\u00a0\u00a0 IF PATINDEX('%Explosive%',@LotsOfText)&gt;0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT 'Error report found in file \"'+@file+'\"'\r\n\u00a0\u00a0 SELECT @ii=@ii+1\r\n\u00a0\u00a0 END\r\nSELECT CAST(@ii AS VARCHAR(5))+' files checked' <\/pre>\n<p>Well, that is fine, but sometimes you want something that is going to bring the text from a file line-by-line so you can process it in a table, line-by-line.\u00a0<\/p>\n<h2>Getting Text into a Table<\/h2>\n<p>\u00a0We&#8217;ll illustrate this with a version of the routine that reads in all the <code>errorlog<\/code> files for an instance line-by-line as a table, and prints out each line that has the word &#8216;error&#8217; in it. This uses a new procedure called <code>spLoadTextFromAFile<\/code> which does just that. It loads the text from a file line buy line and returns it as a result. <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/634-dbosploadtextfromafile.htm\">You&#8217;ll have to see the code by clicking here<\/a>. And here is the procedure in use.<\/p>\n<pre>DECLARE @LotsOfText NVARCHAR(MAX),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ii INT,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @iiMax INT,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @File VARCHAR(2000)\r\nDECLARE @files TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, [Path] VARCHAR(2000))\r\nDECLARE @lines TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, [line] NVARCHAR(MAX))\r\n\r\nDECLARE @FileList XML\r\nEXECUTE ListPathsXML 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\LOG\\ERRORLOG.*',\r\n\u00a0\u00a0\u00a0\u00a0DEFAULT , @XMLFileList = @FileList OUTPUT\r\n\r\nINSERT INTO @files(path)\r\n\u00a0\u00a0 SELECT\u00a0\u00a0 x.thefile.value('fullpath[1]', 'varchar(2000)') AS [path]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0\u00a0@FileList.nodes('\/\/thefiles\/thefile') AS x ( thefile )\r\nDELETE FROM @files WHERE REVERSE(path) LIKE 'golrorre%'\r\n--don't look at the current errorlog!\r\nSELECT @ii=1, @iiMax=MAX(MyID) FROM @Files\r\nWHILE @ii&lt;=@iiMax\r\n\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0 SELECT @File= [path] FROM @files WHERE MyID=@ii\r\n\u00a0\u00a0 INSERT INTO @lines(line)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTE spLoadTextFromAFile @file, @Unicode=1\r\n\u00a0\u00a0 SELECT @ii=@ii+1\r\n\u00a0\u00a0 END\r\nSELECT MyID AS [line Number], Line, @file FROM @lines\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE line LIKE '%Error%' <\/pre>\n<h2>Write data from a TSQL variable into a file<\/h2>\n<p>If you want to write text out from a variable to a file, here is something that will do the trick.<\/p>\n<pre>-----------------------------------------------------------------\r\nIF OBJECT_ID (N'dbo.spSaveTextToFile') IS NOT NULL\r\n\u00a0\u00a0 DROP PROCEDURE dbo.spSaveTextToFile\r\nGO\r\nCREATE PROCEDURE spSaveTextToFile\r\n\u00a0\u00a0@TheString VARCHAR(MAX),\r\n\u00a0\u00a0@Filename VARCHAR(255),\r\n\u00a0\u00a0@Unicode INT=0\r\nAS\r\n\u00a0\u00a0SET NOCOUNT ON\r\n\u00a0\u00a0DECLARE @MySpecialTempTable VARCHAR(255)\r\n\u00a0\u00a0DECLARE @Command NVARCHAR(4000)\r\n\u00a0\u00a0DECLARE @RESULT INT\r\n\r\n--firstly we create a global temp table with a unique name\r\n\u00a0\u00a0SELECT\u00a0\u00a0@MySpecialTempTable = '##temp'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))\r\n--then we create it using dynamic SQL, &amp; insert a single row\r\n--in it with the MAX Varchar stocked with the string we want\r\n\u00a0\u00a0SELECT\u00a0\u00a0@Command = 'create table ['\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + @MySpecialTempTable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + '] (MyID int identity(1,1), Bulkcol varchar(MAX))\r\ninsert into ['\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + @MySpecialTempTable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + '](BulkCol) select @TheString'\r\n\u00a0\u00a0EXECUTE sp_ExecuteSQL @command, N'@TheString varchar(MAX)',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @TheString\r\n\r\n--then we execute the BCP to save the file\r\n\u00a0\u00a0SELECT\u00a0\u00a0@Command = 'bcp \"select BulkCol from ['\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ @MySpecialTempTable + ']'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ '\" queryout '\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ @Filename + ' '\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ ' -T -S' + @@servername\r\n\u00a0\u00a0EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT\r\n\u00a0\u00a0EXECUTE ( 'Drop table ' + @MySpecialTempTable )\r\n\u00a0\u00a0RETURN @result\r\nGO <\/pre>\n<p>We&#8217;ll first read text in and then write it back out again to a different file just to get confidence that nothing gets corrupted. We&#8217;ll use a handy large text file for this. In effect, we are copying the file.<\/p>\n<pre>DECLARE @LotsOfText VARCHAR(MAX)\r\nSELECT\u00a0\u00a0@LotsOfText = BulkColumn\r\nFROM\u00a0\u00a0\u00a0\u00a0OPENROWSET(BULK 'C:\\workbench\\Moby-Dick.txt', SINGLE_BLOB) AS x\u00a0\u00a0\r\nEXECUTE spSaveTextToFile @lotsOfText, 'C:\\workbench\\Moby-Dick-copy.txt',0,0 <\/pre>\n<h2>Write the String-based results of a SQL Expression into a file<\/h2>\n<p>We can do other things with the same idea we&#8217;ve just seen. The following procedure even allows you to write the results of a SQL Statement to a file as long as the result is a single string column. This sort of idea can be adapted to a number of purposes.<\/p>\n<pre>IF OBJECT_ID (N'dbo.spSaveTextResultToFile') IS NOT NULL\r\n\u00a0\u00a0 DROP PROCEDURE dbo.spSaveTextResultToFile\r\nGO PROCEDURE spSaveTextResultToFile\r\n\u00a0\u00a0@TheSQL VARCHAR(MAX),\r\n\u00a0\u00a0@Filename VARCHAR(255),\r\n\u00a0\u00a0@Unicode INT=0\r\n\/*\r\ne.g. spSaveTextResultToFile 'Select logstring+'', ''+convert(char(11),insertionDate,113) from activitylog', 'C:\\workbench\\Logreport.txt'\r\n\r\n*\/\r\nAS\r\n\u00a0\u00a0SET NOCOUNT ON\r\n\u00a0\u00a0DECLARE @MySpecialTempTable VARCHAR(255)\r\n\u00a0\u00a0DECLARE @Command NVARCHAR(4000)\r\n\u00a0\u00a0DECLARE @RESULT INT\r\n\r\n\u00a0\u00a0IF CHARINDEX ('Select ',LTRIM(@TheSQL))=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR ('Usage spSaveTextResultToFile &lt;The SQL Expression&gt; &lt;The Filename)',16,1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n--firstly we create a global temp table with a unique name\r\n\u00a0\u00a0SELECT\u00a0\u00a0@MySpecialTempTable = '##temp'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))\r\n--then we create it using dynamic SQL,\r\n--\r\n\u00a0\u00a0SELECT\u00a0\u00a0@Command = 'create table ['\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + @MySpecialTempTable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + '] (MyID int identity(1,1), MyLine varchar(MAX))\r\ninsert into ['\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + @MySpecialTempTable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + '](MyLine) ' +@TheSQL\r\n\u00a0\u00a0EXECUTE sp_ExecuteSQL @command\r\n\r\n--then we execute the BCP to save the file\r\n\u00a0\u00a0SELECT\u00a0\u00a0@Command = 'bcp \"select Myline from ['\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ @MySpecialTempTable + ']'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ '\" queryout '\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ @Filename\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ ' -T -S' + @@servername\r\n\u00a0\u00a0EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT\r\n\u00a0\u00a0EXECUTE ( 'Drop table ' + @MySpecialTempTable ) <\/pre>\n<p>So here we are. We have a range of functions and stored procedures to do the basic file I\/O, which you can combine with the routines I provided in the first article. None of this is pretty or elegant, but it is all part of the armoury of the DBA and database developer for those occasional routines where, for one reason or another, there isn&#8217;t a better alternative solution.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Phil returns to the old subject of &#8216;Getting text-based data in and out of SQL Server&#8217;. He shows various easy ways of getting a file listings of directories from the file system, shows how one can access the Shell automation Objects, and demonstrates several ways of reading or writing data between database and f&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,4150,5134,4151,4252,4190,4918],"coauthors":[6813],"class_list":["post-504","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-sql","tag-sql-prompt","tag-sql-server","tag-t-sql-programming","tag-tsql","tag-tsql-sql-examples-sample-code-free-speed"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/504","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=504"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/504\/revisions"}],"predecessor-version":[{"id":76357,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/504\/revisions\/76357"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=504"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=504"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=504"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=504"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}