Creating text file without using bcp in sql

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?

Answer

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.