asked on

Load file into SqlString variable using only TSQL.

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

Pratima

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/

pncookson

ASKER

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 Pletcher

But doesn't "ListPathsXML" use xp_cmdshell?

What is wrong with "OPENROWSET( BULK"?

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer