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
..NeoSounds 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 ;)|||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.|||You can also use sp_OAxxx set of procedures to do pretty much anything you'd do with your C++.|||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|||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++?|||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...|||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.|||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.|||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.