Exporting a table from SQL Server 2008 R2 to a file WITHOUT external tools

Question

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)

no, it has something called "Query Analyzer" and some proprietary sql tool that is of no use — Denis
– Denis, Commented Oct 1, 2013 at 21:02

Andrey Morozov · Accepted Answer · 2015-01-06 23:06:09Z

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

edited Jan 6, 2015 at 23:06

answered Sep 28, 2013 at 7:08

Andrey Morozov

8,0175 gold badges59 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrey Morozov Over a year ago

why? It is really cool and you can install it for free microsoft.com/en-us/download/details.aspx?id=8961

James Johnson · Accepted Answer · 2013-09-27 19:34:06Z

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

answered Sep 27, 2013 at 19:34

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

Comments

Hannah Vernon · Accepted Answer · 2013-09-27 19:35:09Z

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

answered Sep 27, 2013 at 19:35

Hannah Vernon

3,4711 gold badge34 silver badges53 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:29:32Z

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.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Oct 2, 2013 at 16:47

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

1 Comment

Denis Over a year ago

This is good except I would like to have this as an automated process without my intervention that exports tables to a file.

Collectives™ on Stack Overflow

Exporting a table from SQL Server 2008 R2 to a file WITHOUT external tools

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related