Creating a text file in SQLSERVER

Method
-----------

Create Procedure ee_DumpOutput (
@StrRights nvarchar(4000)
)
AS
Begin
DECLARE @FullFileName Varchar(500)

SET @FullFileName = 'c:\test.txt'
SET @StrRights = 'osql -Slocalhost -E -q"SET NOCOUNT ON;' + @StrRights +'" -s"," -n -w8000 -o"' + @FullFileName +'"'
Exec master..xp_cmdShell @StrRights, NO_OUTPUT
END
GO

exec ee_DumpOutput 'select * from tetdb..emp'

Description
---------------

The @FullFileName is where the file will get saved.And here the name of the file is test.txt.One thing we have to make sure is that the user should have rights to create the same.Say the username is 'sa'.In that case 'sa' should have rights to do so.xp_cmdShell* is the stored procedure that is implementing this procedure.

Here in this case we are passing the command text as a parameter.This is the command to select the rows from a table and save the result of that query as a text file.

*About xp_cmdShell
---------------------------------

'xp_cmdshell' is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code.Now not just anyone can run this extended stored procedure. If you want to execute this extended stored procedure, you will either need to be a member of the sysadmin role, or have the xp_sqlagent_proxy_account set up on your SQL Server.

A simple directory search example. Say you do not have access to the physical SQL Server machine, but you want to see all the files in the "C:\temp" directory that have a ".sql" extension. You can do this by issuing the following command:

exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'

Author:	Aneesh Kuzhikattil
	He is an accomplished IT professional with over 4 years of extensive experience in Microsoft technologies.