Search within a file on the HardDrive and return a record
set of the lines found within the file based on the search criteria.
2007-2008, Karland International (www.karland.com)
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 ISNULL 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
SELECT'Error Code', @Error -- Should always be 1. Codes are only 0 or 1