open a file handle in sql script

Question

open a file handle in sql script

anton.vandersteen

Grasshopper

Points: 17
More actions
November 28, 2008 at 5:52 am

#198229

Hello Fellow SQL nerds,
I want to be able to open a file in a sql script.
First I select data via a cursor.
Second, I want to be able to insert or append the data into a file.
This way I will be able to create my own xml files.
Thanks.
Hope to hear from you soon.:)

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 739723 More actions · Answer 1

You don't want to do this in T-SLQ, it's horribly inefficient. Holding a file handle is asking for concurrency issues. Using a cursor wastes resources. And SQL Server will not read a line at a time, that's something you do in C or C++

You would be better served by using VBSCript to do this and then perhaps calling a VBS file from SQL Server.

anton.vandersteen Grasshopper Points: 17 More actions · Answer 2

Thanks for the reply.

I have found a solution myself.

I am going to develop a component and use this a a assembly in SQL Server 2005.

SQL 2005 and 2008 are still a mecano box.

Cost al lot, but few standard functionalities.

anton.vandersteen Grasshopper Points: 17 More actions · Answer 3

Furthermore, garbage collection in SQL Server and Microsoft in general is still something to cry about.

I hope that those software buyers of Microsoft well learn from their mistakes, eventually.

Jeff Moden SSC Guru Points: 1004666 More actions · Answer 4

anton.vandersteen (11/28/2008)
Hello Fellow SQL nerds,
I want to be able to open a file in a sql script.
First I select data via a cursor.
Second, I want to be able to insert or append the data into a file.
This way I will be able to create my own xml files.
Thanks.
Hope to hear from you soon.:)

I agree with the others... this really isn't something that should be done in SQL... and cursors make just about any code worse.

However, if you insist, you can create a linked server for text files that'll do what you . Take a look at Books Online under the title of "Linked Servers".

Heh... and the term "Nerd" is not exactly and endearing term. Not sure I'd use that on my next post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

anton.vandersteen (11/29/2008)
SQL 2005 and 2008 are still a mecano box.
Cost al lot, but few standard functionalities.

Actually, Anton, what you are describing is extremely non-standard functionality. SQL Server is actually the best or close to the best for providing the standard features and functionality of SQL.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Sergiy SSC Guru Points: 110209 More actions · Answer 6

anton.vandersteen (11/29/2008)
Furthermore, garbage collection in SQL Server and Microsoft in general is still something to cry about.

My last 2 DB projects are in production one for 2 years, another one for a year. I did not hear from clients for many month. They keep paying, so I assume they still work.

Both have processed gigabytes of transactional data, validation, matching, summarizing, reporting, etc.

No garbage collection was used, and no single byte of garbage was generated by the systems.

Was I doing something wrong?

I hope that those software buyers of Microsoft well learn from their mistakes, eventually.

The biggest mistake of MS, as for me, is to make SQL Server interface and syntax look familiar to those who don't have a clue about database programming.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004666 More actions · Answer 7

Sergiy (11/29/2008)
The biggest mistake of MS, as for me, is to make SQL Server interface and syntax look familiar to those who don't have a clue about database programming.

Here hear! I drink to that!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)