Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Posts
    13

    Unanswered: 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 !

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  3. #3
    Join Date
    Nov 2002
    Posts
    13
    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 !

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I use sp_OAxxx with FileSystemObject.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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!

  7. #7
    Join Date
    Nov 2002
    Posts
    13
    UNCLE !!!!

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

    Thanks again from the school of hard knocks..

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by rdjabarov
    BrutusBuckeye, Pat has a very strong opinion about all this
    Dang! Did I let that secret out again ?!?!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •