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:

  1. Anonymous1/11/2013

    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 "|"

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

      Delete
  2. Anonymous11/05/2013

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

    SET @Text = 'Hello Wolrd ^|'

    ReplyDelete