Load file into SqlString variable using only TSQL.

pncookson
pncookson used Ask the Experts™
on
Hi,

Does anyone know if it is possible to load a file from the file system hosting an sql express instance (so no SSIS) into a SqlString using only TSQL without using xp_cmdshell or OPENROWSET(BULK.

Also CRL Integration is disabled.

Mission Impossible?

Paul Cookson
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can try this option Getting Text into a Table

DECLARE @LotsOfText NVARCHAR(MAX),
       @ii INT,
       @iiMax INT,
       @File VARCHAR(2000)
DECLARE @files TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, [Path] VARCHAR(2000))
DECLARE @lines TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, [line] NVARCHAR(MAX))

DECLARE @FileList XML
EXECUTE ListPathsXML 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG.*',
    DEFAULT , @XMLFileList = @FileList OUTPUT

INSERT INTO @files(path)
   SELECT   x.thefile.value('fullpath[1]', 'varchar(2000)') AS [path]
        FROM    @FileList.nodes('//thefiles/thefile') AS x ( thefile )
DELETE FROM @files WHERE REVERSE(path) LIKE 'golrorre%'
--don't look at the current errorlog!
SELECT @ii=1, @iiMax=MAX(MyID) FROM @Files
WHILE @ii<=@iiMax
   BEGIN
   SELECT @File= [path] FROM @files WHERE MyID=@ii
   INSERT INTO @lines(line)
       EXECUTE spLoadTextFromAFile @file, @Unicode=1
   SELECT @ii=@ii+1
   END
SELECT MyID AS [line Number], Line, @file FROM @lines
       WHERE line LIKE '%Error%' 

Open in new window


refer for more deatils , this will contain all options
https://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

Author

Commented:
Thanks Pratima, sorry for the delay in getting back.

As soon as I have 5 minutes I'll check this out.

Thanks again,

Paul.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
But doesn't "ListPathsXML" use xp_cmdshell?

What is wrong with "OPENROWSET( BULK"?
Top Expert 2012
Commented:
Mission Impossible?
Yes.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial