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.

Methods within an assembly

Is there a way to retrieve the methods within an assembly that are attributed with SqlTrigger or SqlProcedure using T-SQL?

There is not a built-in way to do this, but it is not overly difficult to write a SQLCLR procedure that would do this for you. Miles has an example of a trigger than contains essentially all the code you need to do what you want here: http://blogs.msdn.com/sqlclr/articles/495428.aspx

(Note, the assembly does already have to be loaded into the database in order for you to use Reflection to examine its methods).

Steven

Method/Definition inside T-SQL

Did anybody hear if it's possible to define something like a
method/function/macrodefinition inside T-SQL? Particularly I have a few
SELECTs inside one T-SQL script and in most of them the WHERE CLAUSE is
absolutely same, but the retrieving results should be different and
according to these results the data flow should be different, so I can't
merge all these queries into one because the logic depends on the previous
results. The idea was to define this WHERE (...) as something to use it in
all queries inside this T-SQL. But I'm afraid that the standard T-SQL
doesn't allow us to do that because there are column names in this WHERE
clause. Temporary table? It will be huge and makes no sense.
Just D.Results from one table depending on results from another table sounds like a
join or union to me. Can you provide a more concrete example, e.g. table
structure, sample data, desired results?
"Just D." <no@.spam.please> wrote in message
news:nR6af.65318$WR2.43229@.fed1read03...
> Did anybody hear if it's possible to define something like a
> method/function/macrodefinition inside T-SQL? Particularly I have a few
> SELECTs inside one T-SQL script and in most of them the WHERE CLAUSE is
> absolutely same, but the retrieving results should be different and
> according to these results the data flow should be different, so I can't
> merge all these queries into one because the logic depends on the previous
> results. The idea was to define this WHERE (...) as something to use it in
> all queries inside this T-SQL. But I'm afraid that the standard T-SQL
> doesn't allow us to do that because there are column names in this WHERE
> clause. Temporary table? It will be huge and makes no sense.
> Just D.
>|||You could wrap your SQL inside a stored procedure, or even a user-defined
function. It's possible to parameterize either to make them flexible, and a
n
sp or udf is roughly equivalent to a method.
It's even possible to schedule a SQL script as a job; your DBA will be able
to help you with that.
Tell us a bit more about what you want to do and we'll try and help.
Damien
"Aaron Bertrand [SQL Server MVP]" wrote:

> Results from one table depending on results from another table sounds like
a
> join or union to me. Can you provide a more concrete example, e.g. table
> structure, sample data, desired results?
>
> "Just D." <no@.spam.please> wrote in message
> news:nR6af.65318$WR2.43229@.fed1read03...
>
>