Post reply

Help! Query and Job creating monster .csv file size

  • January 11, 2006 at 5:10 am

    #93159

    When I run the following SQL in a job, the file usually returns about 20K rows but the file size in CSV is relatively large, between 4MB and 5MB and all the data is in one column rather than in individual colums. Searching this site I found a tip that helps put data into columns when using Analyzer (http://www.sqlservercentral.com/columnists/yShlafman/thebestkeptsecretaboutsqlqueryanalyzer.asp)

    but this didn't change how my job ran. Here's my query, cleaned to protect the innocent:

    SELECT DISTINCT (Data1 thru Data7)

    FROM (Table)

    WHERE (Criteria)

    AND (More Criteria)

    AND (More Criteria)

    ORDER BY (Data selected from first statement)

    Simple enough, and I don't know how to change either the query or some setting so the data in the csv file that is created by the job put the seven data elements into their own columns which I belive will reduce the file size at the same time.

    Thanks for any help!

  • January 11, 2006 at 5:49 am

    #614024

    Within SQL Query Analyser make sure that under Tools/Options/Results you have the following settings

    Default Results Target : Results To File

    Results Output Format : comma delimited

    Print Column Headers : Checked

    Putting the data into separate columns won't make the file smaller, in fact it will make it larger by (rows * (fields -1)) bytes due to the extra delimiters.

    LinkedIn Profile

  • January 11, 2006 at 8:32 am

    #614083

    Thanks, I'll try this. Do you know if the Query Analyzer settings are "fixed" across different instances? In other words if I normally leave two instances open, will the settings you recommend above be "forced" onto both?

  • January 11, 2006 at 8:35 am

    #614086

    sorry realized I can easily answer my own question on that second part.

  • January 11, 2006 at 11:54 am

    #614151

    I'm just wondering why you are using SQL Query Analyser to do this?

    Is is just for a quick one off?

    If you have to do it more regularly then consider using DTS. To export tables or views is simply a case of working through a Wizard.

    I have had some success in using the sp_makewebtask and sp_runwebtask to spit out the results of a query in a specific XML format. Far easier than wading through FOR XML EXPLICIT!

    Because sp_makewebtask/sp_runwebtask can bung the data into place holders within any text template file you could even use it to drop a correctly formatted email into appropriate SMTP folder on your server

    LinkedIn Profile

  • January 11, 2006 at 12:09 pm

    #614157

    I'm a newbie to SQL relative to most of the folks on the board here and have zero experience with DTS although I will give it a try.

  • January 11, 2006 at 12:21 pm

    #614161

    David

    This seems to work. What are the advantages of DTS or running a scheduled job?

    Thanks for the suggestion!

  • January 11, 2006 at 12:29 pm

    #614166

    DTS's can be as simple or as complex as you want.

    You can import/export from/to a variety of different sources. For example, I had a datawarehouse that needed to almagamate data from text files, several different databases and an exchange server.

    If you use the wizard to do an import from one database to another you will notice that the wizard will build your DTS to run several imports simulatenously.

    OK, what you have described isn't that complicated but the DTS effectively saves your job so you can re-execute it.

    There is also a tool called DTSRUNUI where you fill in certain details such as the package name, server, user, password and it will give you a batch file command so you can save it onto your desktop, or wherever for future execution.

    When DTS first came out comparable products were VERY expensive. Many professionals at the time were astonished by how much could be achieved by it. I get the impression that MS included it as a nice-to-have but were rather surprised by how popular it turned out to be.

    LinkedIn Profile

  • January 11, 2006 at 12:58 pm

    #614175

    David

    Thanks again, I created the DTS but can't seem to find where to go to edit the SQL that I created in the DTS "editor"?

  • January 12, 2006 at 10:39 am

    #614436

    You should have

    an Icon for a server that represents you connection to your SQL Server.

    An Icon for your text file that represents you CSV file.

    A connecting line between the two that represents the transformation between the two.

    I'm not sitting in front of a sql server at home but I believe that you can right-click on the connecting arrow, select properties and under source you will see options for Table/View or query. Choose query and enter the name of your procedure or your query.

    Failing that look at the possible tasks on the left hand side and drag the "Data Driven Query Task" onto the main pane.

    This allows you to specify the connection (Choose your server connection) and the query.

    You can then create a new transformation between your data driven query task and the text file connection.

    LinkedIn Profile

  • January 12, 2006 at 2:49 pm

    #614494

    Thanks. I'm still fumbling with this but you've been a great help.

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

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