Post reply

Exporting data from SQL to txt

  • July 6, 2004 at 8:02 pm

    #83422

    I'm trying to export data from SQL into a text file.  When I do, just as a scheduled job, the text file comes out as Unicode instead of ANSI.  If I run the exact same script through DTS it exports it correctly (ANSI). The issue with this is the data is being imported into a Unix program, and it seems to hate Unicode. My problem is I want the file to be appended and not over written.  I can see how to do this with a scheduled job but not with DTS.

    So can someone please tell me how to append a file using DTS or how to have it export in ANSI format as a scheduled job?

    Thanks in advance

    Newy

    Thanks,

    Kris

  • July 6, 2004 at 9:22 pm

    #513548

    How did you try to export the data with a scheduled job? I mean BCP etc......

    Kindest Regards,

  • July 6, 2004 at 9:29 pm

    #513550

    I created a new job under SQL agent and wrote the script in the steps section and then created a schedule for it to run.

    Thanks,

    Kris

  • July 6, 2004 at 10:41 pm

    #513563

    Ok, but was is the script? BCP, T-SQL etc.....

    Kindest Regards,

  • July 6, 2004 at 10:44 pm

    #513566

    T-SQL

    Thanks,

    Kris

  • July 6, 2004 at 11:32 pm

    #513570

    Kristen,

    Please elaborate. How on earth can I help you when you give me short answers such as T-SQL?

    If you would like me to further assist you, please show me the code. If you are afraid of showing the code, then just replace what you deem as secure information with a dummy name/s!

    Kindest Regards,

  • July 7, 2004 at 12:40 am

    #513574

    Sorry, here is more info. Here is the script.  The DTS package is SQL server as the source and notepad as the destination.  It goes onto another server as a flat txt file, but it overwrites instead of appends the new data and just leave all the previous data there.

    Normally I would just copy and paste this into a scheduled job and just run it every night, but it keeps saving as Unicode instead of ANSI.

    I hope this is more helpful.

    Begin Tran

    select P1.kh01

         , P2.ky

         , P1.rpv

         , P1.rpd

      from testdta.3b11 P1

         , testdta.101 P2

     where P1.lc = 'clnt'

       and P1.rpd != 'RU'

       and P1.rpv > '0'

       and (P1.urc1 = '0'

       or P1.urc1 = ''

       or urc1 is null)

       and P2.aban = P1.rpan

       and P2.ky not in ('lion','tiger')

    update testdta.3b11

       set urc1 = '1'

     where rpg = 'nt' 

       and rpv > '0'

       and (urc1 = '0'

       or urc1 = ''

       or urc1 is null)

    Commit Tran

    Thanks,

    Kris

Viewing 7 posts - 1 through 6 (of 6 total)

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