Post reply

Output to CSV or txt file from Query Window

  • May 4, 2012 at 8:11 am

    #255441

    Dear All,

    I have a requirement as below.

    Push the output of a query to a Flat file (csv , txt) directly from a SQL Query window. Doing a bcp will not be possible as i will not get permission for xp_cmdshell. Please help whether we have any other options available to do the same.

    Thanks in Advance!!

  • May 4, 2012 at 8:33 am

    #1483595

    Set results to grid, then Save Results As. Options are csv or tab delimited.

  • May 6, 2012 at 9:05 pm

    #1483948

    Thanks. But this will not help 🙁 Output should be automatically pushed into CSV and not a manual process.

  • May 6, 2012 at 10:25 pm

    #1483951

    if adding a CLR is an option, take a look at this project i slapped together over at codeplex, and this thread that discusses other options, as well as tightening the CLR down for security reasons.

    http://SQLCLRExport.Codeplex.com

    http://www.sqlservercentral.com/Forums/Topic1294716-392-1.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 7, 2012 at 3:38 am

    #1484011

    haichells (5/6/2012)

    Thanks. But this will not help 🙁 Output should be automatically pushed into CSV and not a manual process.

    This requirement sounds flaky to me.

    You want an automatic process, and that's fine. But why does it need to happen "from a query window"? What happens if the 'query window' is closed when the process needs to run?

    Why not just a silent background process?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 7, 2012 at 3:52 am

    #1484016

    Sorry. I mean to say that this need to be happen through a stored procedure (can be assigned to a job) and not from the query window.

  • May 7, 2012 at 3:54 am

    #1484017

    A simple SSIS package would do this easily, if that is an option.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 7, 2012 at 4:09 am

    #1484022

    Can we name the .csv file dynamically. i.e for example file name should be "XXXXX_mmddyyyy.csv" it should be suffixed with current date so that it will not overwrite and also we have csv file daily.

  • May 7, 2012 at 4:19 am

    #1484024

    i think ssis package could finish it ,just set file name to a variable.

  • May 7, 2012 at 4:35 am

    #1484028

    haichells (5/7/2012)

    Can we name the .csv file dynamically. i.e for example file name should be "XXXXX_mmddyyyy.csv" it should be suffixed with current date so that it will not overwrite and also we have csv file daily.

    If you are talking about SSIS, then yes. But it's easier to implement as follows:

    1) Leave the output file name static

    2) Add a step at the end of the package that does the copy/rename for you. Either a script task (if you're happy coding, I would recommend this) or file system task.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 8, 2012 at 4:53 am

    #1484425

    "Doing a bcp will not be possible as i will not get permission for xp_cmdshell"

    What about simply creating a SQL Server Job with a step of type CmdExec to run your BCP command.

  • May 8, 2012 at 5:06 am

    #1484429

    nick.mcdermaid (5/8/2012)

    "Doing a bcp will not be possible as i will not get permission for xp_cmdshell"

    What about simply creating a SQL Server Job with a step of type CmdExec to run your BCP command.

    Exactly. Although I think SSIS is the very good option, bcp & SQLCMD are also possible with step type CmdExec to produce the output as CSV file.

    Sujeet Singh

  • May 8, 2012 at 5:31 am

    #1484437

    .. and of course you run your SSIS package with DTEXEC.EXE.

    So thats a total of three command line solutions (SQLCMD, BCP, DTEXEC), all of which can be run from SQL Agent without requiring you to emable xp_cmdshell.

  • May 8, 2012 at 10:05 am

    #1484635

    bcp may not work because the output of a stored procedure needs to be exported to a flat file. since it is not taken direct from a table nor a simple query doing bcp will not be possible.

    I have seen some Openrowset , Opendatasource commands in net for reading data from a file directly from a query. Similar way i was trying to find whether we have an option of pumping data to a flat file directly from query.

    Thanks

  • May 8, 2012 at 10:14 am

    #1484642

    haichells (5/8/2012)

    bcp may not work because the output of a stored procedure needs to be exported to a flat file. since it is not taken direct from a table nor a simple query doing bcp will not be possible.

    I have seen some Openrowset , Opendatasource commands in net for reading data from a file directly from a query. Similar way i was trying to find whether we have an option of pumping data to a flat file directly from query.

    Thanks

    If you write the results of your query out to a table which is created on the fly (find a way of naming the table uniquely), you can BCP it straight out and then delete the table again.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 15 posts - 1 through 15 (of 16 total)

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