locked
What is the query to export table date to comma delimited file? RRS feed

  • Question

  • Software: SQL 2008 R2 - i need a query that i can run to send files to comma delimited text file. ie ... select * from tablename and send the results to text.txt with the results in comma delimited format.

    There is a wizard that will do this just once and does not save the code and does not allow me to see what it does. I need the actual code that it does so that i can automate the query through a scheduled task.

    

     


    Ron Sweatland

    Wednesday, April 13, 2011 3:31 AM

Answers

  • EXEC  master..xp_cmdshell 'SQLCMD -S Server\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput.txt', no_output
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:57 AM
    Wednesday, April 13, 2011 7:50 AM
  • Ok, I wasn't aware of that but it makes sense since SSIS is only available in Standard and Enterprise.

    The best way to go with be Uri's example using sqlcmd. Either as a scheduled job or wrapped in a xp_cmdshell option.  The other provided option (BCP) would be usefull as well.


    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:57 AM
    Wednesday, April 13, 2011 10:33 AM
  • EXEC  master..xp_cmdshell 'SQLCMD -S Server\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput.txt', no_output
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


    Ron Sweatland
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:57 AM
    Wednesday, April 13, 2011 10:34 AM
  • Either enable xp_cmdshell

    ---- To allow advanced options to be changed.
    
    EXEC sp_configure ‘show advanced options’, 1
    
    GO
    
    —- To update the currently configured value for advanced options.
    
    RECONFIGURE
    
    GO
    
    —- To enable the feature.
    
    EXEC sp_configure ‘xp_cmdshell’, 1
    
    GO
    
    —- To update the currently configured value for this feature.
    
    RECONFIGURE
    
    GO 
    
    
    Or only run the last part from a command prompt
    
    SQLCMD -S Server\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput.txt
    

    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:57 AM
    Wednesday, April 13, 2011 10:39 AM
  • BCP will do what you want. Check the documentation in Books Online. Sample code is provided there as well.

    http://msdn.microsoft.com/en-us/library/ms162802(v=SQL.105).aspx

     


    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:56 AM
    Wednesday, April 13, 2011 10:54 AM

All replies

  • I presume you are refering to the import/export wizard in Management Studio. This does not generate SQL code but a SSIS package. At the end of the wizard you can choose to save the package. Once the package is saved you can schedule it to execute on a regular base.


    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    Wednesday, April 13, 2011 5:54 AM
  • you can use BCP command which can be called using xp_cmdshell from SQL Server to get comma delimited txt file. 

    Regards

    Santy


    MS SQL Server DBA
    Wednesday, April 13, 2011 7:20 AM
  • EXEC  master..xp_cmdshell 'SQLCMD -S Server\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput.txt', no_output
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:57 AM
    Wednesday, April 13, 2011 7:50 AM
  • I presume you are refering to the import/export wizard in Management Studio. This does not generate SQL code but a SSIS package. At the end of the wizard you can choose to save the package. Once the package is saved you can schedule it to execute on a regular base.


    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl

    Sorry but you can't - this is what Microsoft has to say:

    In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation

     


    Wednesday, April 13, 2011 10:27 AM
  • Ok, I wasn't aware of that but it makes sense since SSIS is only available in Standard and Enterprise.

    The best way to go with be Uri's example using sqlcmd. Either as a scheduled job or wrapped in a xp_cmdshell option.  The other provided option (BCP) would be usefull as well.


    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:57 AM
    Wednesday, April 13, 2011 10:33 AM
  • EXEC  master..xp_cmdshell 'SQLCMD -S Server\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput.txt', no_output
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


    Ron Sweatland
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:57 AM
    Wednesday, April 13, 2011 10:34 AM
  • Either enable xp_cmdshell

    ---- To allow advanced options to be changed.
    
    EXEC sp_configure ‘show advanced options’, 1
    
    GO
    
    —- To update the currently configured value for advanced options.
    
    RECONFIGURE
    
    GO
    
    —- To enable the feature.
    
    EXEC sp_configure ‘xp_cmdshell’, 1
    
    GO
    
    —- To update the currently configured value for this feature.
    
    RECONFIGURE
    
    GO 
    
    
    Or only run the last part from a command prompt
    
    SQLCMD -S Server\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput.txt
    

    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:57 AM
    Wednesday, April 13, 2011 10:39 AM
  • ok ... i have it now working however, it is not producing a comma delimited file ... this is what i need it to produce...
    Ron Sweatland
    Wednesday, April 13, 2011 10:49 AM
  • BCP will do what you want. Check the documentation in Books Online. Sample code is provided there as well.

    http://msdn.microsoft.com/en-us/library/ms162802(v=SQL.105).aspx

     


    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    • Marked as answer by usslosangeles Wednesday, April 13, 2011 10:56 AM
    Wednesday, April 13, 2011 10:54 AM
  •  

     Thanks Robert and Uri...
    Wednesday, April 13, 2011 10:55 AM