Creating text file without using bcp in sql

Question

I have to query a table and print the output in a text file. I have a normal user login with dbo permission on the database. I cannot use bcp or cannot give any special permissions. Can anyone help me with this?

Aaron Bertrand · Accepted Answer · 2021-12-28 03:49:51Z

1

You can't create a text file on the server or elsewhere with only permissions in the database. There are several alternatives to BCP (SQLCLR, xp_cmdshell, sp_OACreate, OPENROWSET, etc.) but Windows is a little more secure than just letting any database user write to the file system. Imagine if anybody running a query against your database could write to the file system? (Some background here.)

Here is an article that describes how others have done it, as well as some ensuing discussions:

Reading and Writing Files in SQL Server using T-SQL

For the CLR approach there is also a codeplex project called FileSystemHelper which will be more useful than re-inventing the wheel.

edited Dec 28, 2021 at 3:49

answered Feb 10, 2012 at 0:11

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

Comments

John Dewey · Accepted Answer · 2012-02-10 05:57:11Z

0

If you're running the query manually, SSMS has an option to output results to file (CTL-Shift-F)

answered Feb 10, 2012 at 5:57

John Dewey

7,1234 gold badges25 silver badges27 bronze badges

Collectives™ on Stack Overflow

Creating text file without using bcp in sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related