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:
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 "|"
Write inside "" but it will also write ""
SET @Cmd ='echo "' + @Text + '" > C:\AppTextFile.txt'
I had the same problem with the pipe |, you need to escape it with a ^. See below:
SET @Text = 'Hello Wolrd ^|'
Post a Comment