Post reply

Nightly export query to file

  • February 6, 2008 at 4:31 pm

    #186348

    Currently we are exporting data via a nightly SQL 7.0 job to a .csv file. The problem is that I have not been able to figure out how to "turn off" showing the Title of the job and the totalling of the rows within the .csv. I can turn it off in Query Analyzer but don't know the code to do so within a job.

    I have also tried using a DTS package but just may not be proficient enough for the settings.

    Any advice would be appreciated.

    Thank you

  • February 6, 2008 at 7:59 pm

    #775844

    How are you exporting in the job? Meaning what's being run?

    DTS would make this easy. You can use the data export wizard to help set this up from Enterprise Manager.

  • February 7, 2008 at 8:48 am

    #776057

    Sorry, I didn't know how detailed to get in the posting. It is just a SQL query to pull data for a bank transmission. They were very particular in the formatting and I was able to get the data out of Query Analyzer in the correct format because I can set the nocount option and turn off the column headings, etc. I tried to put it into a nightly job but cannot (or haven't figured out how to) set those features there.

    I tried using DTS but it seems to append each night to the file and adds extra lines instead of overwriting it and it puts a header on the file as well.

    Does this help or do you need more information?

    Thank you.

  • February 7, 2008 at 1:26 pm

    #776164

    Try adding two SQL steps in the DTS package.

    First: drop table with the name for the first.

    Then do a create table, you can get the SQL from the transform step (copy/paste) and then run your transform. That has worked in Excel.

    If you have the script to make it work, wrap that in a stored procedure, SET commands and all. Then schedule that as a job.

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy