How to create a text file using sql query in sql server

We can create a text file and write some data into by sql query in sql server.

Step 1: Enable the some setting to create text file. To execute this query you must have admin permission.

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure  'xp_cmdshell', '1'

RECONFIGURE;

GO

Step 2: Sql query to create text file AppTextFile.txt and write some data into it.

DECLARE @Text AS VARCHAR(100)

DECLARE @Cmd AS VARCHAR(100)

SET @Text = 'Hello world'

SET @Cmd ='echo ' +  @Text + ' > C:\AppTextFile.txt'

EXECUTE Master.dbo.xp_CmdShell  @Cmd

Note: You may face problem regrading the permission to create text file in C drive. In  this case you should use any other drive or any folder in C drive.