Here's a PROC I 've used to output <8K files.
Create Procedure SyExport8KBlobToTextFile_sp
(@Blob varchar(8000) = NULL
,@OutPutFile varchar(256) = NULL)
as
if @Blob + @OutPutFile is NULL begin
print ' ******* Procedure SyExport8KBlobToTextFile_sp *****
Creates a file with the passed text data
Parameters:
@Blob Less than 8K long string of text
@OutPutFile Full UNC filename of target file.
'
return
end
-- Real Work Start Here
Declare @SQL varchar(8000), @Select varchar(8000)
set @Select = replace(@Blob, '''', '''''')
set @Select = 'Select ''' + replace(@Select, '"', ''' + char(34) + ''') + ''''
set @Select = replace(@Select, Char(13) + Char(10), ''' + char(13) + Char(10) + ''')
set @Select = replace(@Select, Char(13), ''' + char(13) + ''')
set @Select = replace(@Select, Char(10), '')
set @Select = replace(@Select, '''', '''''')
-- print @Select
set @SQL = 'master.dbo.xp_cmdshell ''BCP "' + @Select + '" QUERYOUT "' + @OutPutFile + '" -c -S"' + @@Servername + '" -a32768 -T -t""'', no_output '
--print @SQL
Exec (@SQL)
Once you understand the BITs, all the pieces come together