how to create a text file using sql server

 In this article, we are going to see, how to create a text file using an SQL server

this script you run on command prompt 

bcp "select * from TESTDB.dbo.Employee" queryout d:\employee.txt -T -S -c -t, -r \n

or :

sqlcmd -s, -h-1 -W -Q "set nocount on; select * from TESTDB.dbo.Employee" | findstr /v /c:"-" /b > "d:\employee.txt"

or:

run the script below to grant authorisation

EXECUTE sp_configure 'show advanced options', 1;  

GO    

RECONFIGURE;  

GO  

EXECUTE sp_configure 'xp_cmdshell', 1;  

GO   

RECONFIGURE;  

GO

EXECUTE sp_configure 'Ole Automation Procedures', 1;  

GO  

RECONFIGURE;  

GO

declare @cmd varchar(8000);

select @cmd = 'sqlcmd -s, -h-1 -W -Q "set nocount on; select * from TESTDB.dbo.Employee" | findstr /v /c:"-" /b > "d:\employee.txt"'

exec xp_cmdshell @cmd,no_output

or:

CREATE PROCEDURE [dbo].[CreateToFile] 

@File VARCHAR(250)

,@Text VARCHAR(MAX)

WITH EXECUTE AS CALLER

AS

BEGIN

DECLARE @OLE INT

DECLARE @FileID INT

EXECUTE sp_OACreate 'Scripting.FileSystemObject',@OLE OUT

EXECUTE sp_OAMethod @OLE,'OpenTextFile',@FileID OUT,@File,8,1

EXECUTE sp_OAMethod @FileID,'WriteLine',NULL,@Text

EXECUTE sp_OADestroy @FileID

EXECUTE sp_OADestroy @OLE

END;

--EXEC CreateToFile 'd:\CreateToFile.txt','Hello World'