Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Post Unanswered: Microsoft SQL File I/O

    Does anyone know how to do file I/O in T-SQL. When I say that I mean, I would like to get information from a '.dat' file, parse through it, grab information, then store the information in a database.(also what are the limitations if any to file i/o manipulation in T-SQL) After that is done I need to rename the '.dat' file, and cut and then paste it into an archive directory. Before I say fuck it and do it in C++, is it possible to do this in T-SQL (Microsoft SQL Server 2000).

    Thank You
    ..Neo

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sounds kinda harsh when you put it this way, Neo.

    But since you're so comfortable with C++, you'll feel like fish in the water with SQLDMO

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    You can't really do it well with T-SQL.

    If you want to keep the functionality wrapped in your SQL Database, I would recommend a DTS Package, which nicely (graphically) blends COM and SQL functionality, though it can be a little frustrating to use when you are used to just doing things in code.

    Short of that, you could create a COM dll and manipulate it with VB Script from a SQL Agent job.
    -bpd

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can also use sp_OAxxx set of procedures to do pretty much anything you'd do with your C++.

  5. #5
    Join Date
    Jan 2004
    Posts
    3

    Post

    I can't, use any scripting languages. The code was originally written in ColdFusion. I am a C++ coder, not a SQL man, I was kind of thrown into this project (small company and I'm on Co-Op). The idea is to notuse any scripting language to do any "real" processing, so they want me to do it in SQL, as a Stored Procedure. I am not sure if it possible. I could do it in C++ in like 10 minutes. Also what is Pro*C/C++?

    ..Neo

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, if you can do it in C++ in 10 minutes, - why are you waisting your time?

    You can use sp_OAxxx in your stored procedures, it's still T-SQL.

    And what is Pro*C/C++???

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Well, I won't make a big deal about the fact that the sp_OAxxx sp's call window's scripting DLLs, so it's still really writing it in a scripting language...
    -bpd

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Agree, but it's still T-SQL that you implement this solution in. I think the guy is just flashing with something we can't see when he says that he can do it in C++ in 10 minutes. I CAN do it in 10 minutes using sp_OAxxx, and yes I am probably gonna use Scripting.FileSystemObject, but this is a well documented approach to OS file processing techniques, and I don't see anything wrong with it, other than being able to apply the right approach to resolve a problem.

  9. #9
    Join Date
    Jan 2004
    Posts
    3
    When I say I CAN do it in 10 minutes, all I'm trying to say is that I don't know SQL very well. I've done very little with it, and my company wants to stay away from scripting languages, becuase it is very slow when it does file manipulation, becuase it has to go through like 2 differen't engines.

  10. #10
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    My only concern (generally speaking), and I could be wrong, is that you are tying up a SQL thread with the external tasks when you use sp_OA's, and that puts your SQL session in jeopardy (not sure if it is isolated). Using an ActiveX task with SQL Server Agent at least offloads that burden from the main SQL task.

    To be honest, though, I do use them as well.
    -bpd

Posting Permissions

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