Writing to a text file using t-sql

Question

Post reply

Writing to a text file using t-sql

Paula-196779

SSCrazy

Points: 2575
More actions
March 22, 2005 at 9:14 am

#67175

Hi,
I'm trying to write to a text file using t-sql. I've done this before in Oracle using UTL file handles and wondered if there is an equivalent in sql server. I would like to do this:
/* Create Street File
-- open text file
--write to text file with following SQL
select cast(street.streetcode as varchar(2)) + '|' + streetname + '|' + addressline1 + '|' + pdcode as StreetFile
from pdstreet inner join street on street.streetcode = pdstreet.streetcode
--Close text file.
Any ideas please? Is there a way of just spooling?
Thank you all.
P.S This is fairly urgent..

Viewing 10 posts - 1 through 9 (of 9 total)

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

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Take a look at books on line for BCP with a queryout parameter. The topic you want is "bcp Utility".

Hope this helps

Wayne

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 2

Look in BOL, but also search this forum for "bcp" and "queryout". There are some really good threads here on this topic. If you get stuck, post again.

There is no "i" in team, but idiot has two.

Paula-196779 SSCrazy Points: 2575 More actions · Answer 3

Thanks very much for that. Also, is there any reason why writing a DTS package to export the data from table to a text file is not a good idea? Cos I thought I might try that....

Meghana SSC Eights! Points: 840 More actions · Answer 4

If you do not have time to go thru BOL . this is how you can do it

create view streetfile

AS

select cast(street.streetcode as varchar(2)) + '|' + streetname + '|' + addressline1 + '|' + pdcode as Street

from pdstreet inner join street on street.streetcode = pdstreet.streetcode

exec master..xp_cmdshell 'bcp "select street from streetfile" queryout C:\Streetfile.txt -c -t -S<ServerName>'

This might help !

Regards,

Meghana

Paula-196779 SSCrazy Points: 2575 More actions · Answer 5

Paula-196779

SSCrazy

Points: 2575

March 23, 2005 at 3:55 am

#548412

Thankyou, that's great!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

And if you also want column headers consider this:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=169337

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

fcallawa Mr or Mrs. 500 Points: 531 More actions · Answer 7

You are looking for the SP: sp_OACreate. Calling this SP will allow you to create a file handle to a file on the disk, which you can then use to write out to the text file.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

In this case, the sp_OAxxx procs are overkill.

imo, they are to be avoided if possible. There are way too many issues with sp_OAxxx procs for me to consider them as a reliable option.

just my .02 though

/Kenneth

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 9

DTS will work just fine. I base the design decision on whether to do that from DTS or from code in a stored proc on where it needs to be controlled from. If you need to write to a file from inside a stored proc (or a web page that calls a proc), do it from another stored proc. If you need it to run on a schedule (from SQL Agent) do it from DTS. Yes, you could code a stored proc that is then run by the Agent, but sometimes it is just more straightforward, maintenance-wise, to do a DTS package. Especially if you have non-programmers maintaining your database.

There is no "i" in team, but idiot has two.