Search code examples
sqlsql-serverbulk

Read contents of a text file into a varchar WITHOUT using BULK

I've got a problem whereby I need to read data from a .txt file into a variable in SQL Server. The read needs to be performed programmatically, as it's going to form part of a stored procedure, and it needs not to utilise the BULK method, as I don't have permissions to use the BULK method on the database in question. Is this possible?

Thanks in advance :)

Solution

  • Can you get them to allow Ad Hoc Distributed Queries? Then you can use OpenRowset or OpenDatasource.

    SELECT * 
FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\users\graham\desktop;', 
'SELECT * FROM [data.txt];'

    Here's the recofiguring code, if you need it:

    EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
go

    This is a laborious technique, though -- you sure you can't use client code? Even, I dunno, VBA in Excel or something?

    g.