0

I am using sql server 2008 and (c#, asp.net). How do I write a select statement result to a text file (table with 5 columns and 30+ rows) in sql server? I have a database with hundreds of tables and stored procedures.

Nitrodbz
  • 1,276
  • 1
  • 15
  • 25
  • Related thread- http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd – KV Prajapati Sep 24 '12 at 14:38
  • I would have to create a job that would call execute a command line for bcp to write a file. Isn't there another way I can do this (similar to a stored procedure)? – Nitrodbz Sep 24 '12 at 14:51
  • 1
    I'm not sure but have a look at this thread - http://serverfault.com/questions/210526/ms-sql-server-2008-how-to-export-all-the-tables-into-csv – KV Prajapati Sep 24 '12 at 15:36
  • 1
    Since your using C#, you can easily write the results of your query out using a DataReader (e.g. http://www.mikesdotnetting.com/Article/58/Exporting-data-to-a-CSV-tab-delimited-or-other-text-format). – Zachary Sep 24 '12 at 17:32

1 Answers1

1

You can run the select through ISQL via xp_cmdshell and use the -o parameter to output the results to a file. This example uses the -E parameter to avoild hard-coding a userid.

declare @str varchar(255)
select @str = 'isql -Q "select * from <tablename>" -E -o c:\text.txt'
exec master.xp_cmdshell @str
Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51