Reading a text file from a stored procedure.

A rookie question - all I want to do is open a text file x.txt and read each line - no bcp or bulk insert required.
Is there a simple way to do this ?
Thanks in advance to all who reply !Not with ANSI-92 syntax. You'd have to use specific DBMS extensions for it. Pick the engine (Oracle, SQL Server, UDB, etc.) and post accordingly.|||I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply !|||I use sp_OAxxx with FileSystemObject.|||Originally posted by BrutusBuckeye
I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply ! In my opinion, this is one of the fundamental design flaws in Oracle. They are attempting to make PL/SQL a programming language instead of a data[base] manipulation language.

If you stop and think about it, reading text the way that you want to do it is a client side activity. Using BCP or BULK INSERT are server side activities. There is a fundamental difference between them (which machine the code actually runs on)!

Any solution you find for MS-SQL will involve server side activity. Sybase (now Microsoft) never intended for Transact-SQL scripts to run on the client, they always assumed that those Transact-SQL scripts would run on the server. That is exactly why user interface code, file access, etc are absent from Transact-SQL... The absence is by design.

Using Microsoft Transact-SQL, you'll need to either adopt a server centric point of view, or write your client side code using the client language. There is a clear distinction between the client and server in Transact-SQL.

-PatP|||BrutusBuckeye, Pat has a very strong opinion about all this ;)

I'd still use sp_OAxxx if you insist on reading a text file one line at a time, but why bother? Use BULK INSERT and then deal with it in a recordset-based fashion!|||UNCLE !!!! :)

Thanks for all your help. I am indeed going the Bulk Insert route.

Thanks again from the school of hard knocks.. :)|||Originally posted by rdjabarov
BrutusBuckeye, Pat has a very strong opinion about all this ;) Dang! Did I let that secret out again ?!?!

-PatP

Read a file name from network folder automatically for a BULK INSERT

Hi again all,

Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks.

ddaveDECLARE @.FilePathToSQLFiles VARCHAR(2000)
DECLARE @.Path VARCHAR(2000)

SET @.FilePathToSQLFiles = 'C:\'

CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))

SET @.Path = 'dir /b "' + @.FilePathToSQLFiles + '*.sql"'

INSERT INTO #SQLFiles
EXECUTE master.dbo.xp_cmdshell @.Pathsql