1

I would like to export a table from SQL Server 2008 R2 to a file. The problem is that I don't have bcp (nor can I install it or anything else) and am not able to run xpcmdshell. Anyone have any ideas on how this could be done without those permissions/tools? (I would like to have this happen on some automated basis preferably)

Denis
  • 11,796
  • 16
  • 88
  • 150
  • no, it has something called "Query Analyzer" and some proprietary sql tool that is of no use – Denis Oct 01 '13 at 21:02

4 Answers4

2

I'm usually using Copy/Paste from SSMS Results Pane to Excel

enter image description here

OR

you can right click on database in the Object Explorer and select Database->Tasks->Export Data. An SQL Server Import and Export Wizard dialog opens and you will be able to export data from any table or query to the file or another destination.

OR

you can use LinqPad - awesome, simlpe and free tool (I really love it) that doesn't require installation

enter image description here

Andrey Morozov
  • 7,839
  • 5
  • 53
  • 75
1

In the results pane, click the top-left cell to highlight all the records, and then right-click the top-left cell and click "Save Results As". One of the export options is CSV.

You can also use a command like this too:

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product')

Lastly, you can look into using SSIS (replaced DTS) for data exports. Here is a link to a tutorial: http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm

James Johnson
  • 45,496
  • 8
  • 73
  • 110
1

If you have SQL Server 2012 you could add File Tables to your database. Thus you could use SQL Agent to schedule a simple stored proc to update the file table when desired.

http://technet.microsoft.com/en-us/library/ff929144.aspx#Description

Hannah Vernon
  • 3,367
  • 1
  • 26
  • 48
1

it has something called "Query Analyzer"

Query Analyzer (isqlw.exe) is the SQL 2000, pre-SSMS, query tool. A very fine tool. Among other things, is capable of exporting query results to a file. See https://stackoverflow.com/a/3769766/105929:

go to the Tools -> Options menu. On the Results tab, choose to send your output to a CSV file and select the "Print column headers" option.

Community
  • 1
  • 1
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • This is good except I would like to have this as an automated process without my intervention that exports tables to a file. – Denis Oct 18 '13 at 14:17