/* SearchFile.sql:  
      Search within a file on the HardDrive and return a record 
      set of the lines found within the file based on the search criteria.

 Copyright:
      2007-2008, Karland International (www.karland.com)

 Permission:
      Permission to use, copy, modify, distribute and sell this software and its 
      documentation for any purpose is hereby granted without fee, provided that the 
      above copyright notice appears in all copies and that both that copyright notice 
      and this permission notice appear in supporting documentation. Karland International 
      makes no representations about the suitability of this software for any purpose. 
      It is provided "as is" without express or implied warranty.
*/
DECLARE @Error INT, @i INT
-- I'm creating a temp array, NOT a Temp table. This is in memory vs HD.
-- The reason I'm creating this table is if you dont you see the records sets 
-- each time you call anything. I'm wanting to mask that and only display what
-- I want shown.

DECLARE @dbData TABLE (i INT IDENTITY(1,1) NOT NULL, data VARCHAR(7000))

SET NOCOUNT ON

-- Check to see if file exists

EXEC master..xp_fileexist 'C:\WINDOWS\win.ini', @i out
IF @i=1 BEGIN -- 1 = file exists, 0 = file does not exist
     -- Find text inside of file..

     INSERT INTO @dbData EXEC @Error = master..xp_cmdshell 'c:\windows\system32\find.exe /N /I "mail" C:\WINDOWS\win.ini'

     -- 0 = "mail" found in win.ini, 1 = "mail" was not found in win.ini

     IF @Error=0 BEGIN
          -- SQL always puts the first and last line as a null, then on the 
          -- second line puts the "----" trailing with the file name "win.ini"

          DELETE FROM @dbData WHERE data IS NULL OR data LIKE '----------%'
          -- Since this is a single string, above the /N means to give me Line numbers of where it was found.
          -- I'll split that up from the actual text so to show two columns, 'Line #', 'Text'.

          SELECT SUBSTRING(data, 2, CHARINDEX(']', data)-2), SUBSTRING(data, CHARINDEX(']', data)+1, LEN(data)) FROM @dbData
     END ELSE
          SELECT 'Error Code', @Error -- Should always be 1. Codes are only 0 or 1

END