Post reply

Writing to a text file using t-sql

  • 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..

  • March 22, 2005 at 9:56 am

    #548233

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

    Hope this helps

    Wayne

  • March 22, 2005 at 5:56 pm

    #548351

    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.

  • March 23, 2005 at 2:00 am

    #548397

    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....

  • March 23, 2005 at 2:06 am

    #548400

    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

  • March 23, 2005 at 3:55 am

    #548412

    Thankyou, that's great!

  • March 23, 2005 at 4:21 am

    #548415

    And if you also want column headers consider this:

    http://qa.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]

  • March 23, 2005 at 7:32 am

    #548478

     

    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. 

  • March 23, 2005 at 8:45 am

    #548513

    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

  • March 23, 2005 at 1:14 pm

    #548590

    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.

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

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