Write Text File

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_WriteTextFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[usp_WriteTextFile]
GO

/*

Author:	amcleod
	Adapted from script by Rodrigo Acosta 

Date:	4.6.04

Accepts any table name (may include a WHERE clause), any output file path/name,  and an optional field list (may include udfs,cast/converts,etc...).

*/

Create Procedure usp_WriteTextFile @varTableName varchar(100),@varOutFile varchar(100),@varFieldList varchar(100) = '*'
As

SET NOCOUNT ON

DECLARE @bcp AS VARCHAR(8000)
DECLARE @Status AS INT


	SELECT @bcp = 'bcp "SELECT ' + @varFieldList + ' FROM ' + @varTableName + '" QUERYOUT "' + @varOutFile + '" -T -c'
	EXEC @Status = master.dbo.xp_cmdshell @bcp, no_output
	
	IF @Status <> 0
		BEGIN
			PRINT 'An error ocurred while generating the file ' + @varOutFile + '.'
			RETURN(-1)
		END 
	ELSE
		Begin
			PRINT @varOutFile + ' file generated succesfully.'
		End

GO

/*

--  To use with Rodrigo Acosta's script to Export text of 
--  SPs, place this after the cursor is deallocated:
Declare @varTableName varchar(100),@varOutFile varchar(100)

Select @varTableName = DB_NAME() + '.dbo.##SPs', @varOutFile  = 'C:\StoredProcs_' + @@SERVERNAME + '.txt'

Exec usp_WriteTextFile @varTableName = @varTableName,@varOutFile  = @varOutFile,@varFieldList = '*'

*/