Feb 8, 2012

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. 

3 comments:

Anonymous said...

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


Not working because of "|"

Priyanka kumari said...

Write inside "" but it will also write ""
SET @Cmd ='echo "' + @Text + '" > C:\AppTextFile.txt'

Anonymous said...

I had the same problem with the pipe |, you need to escape it with a ^. See below:

SET @Text = 'Hello Wolrd ^|'