Creating ASCII Text File
-
Let say that I have a table that contains 2 columns. Column A has "Hello " in it and Column B has "World!". How do I create an ASCII file "MYFILE.TXT" on my C drive to contain the contents of those 2 columns like "Hello World!".
I know that I have to call xp_shell something but I believe there are more to it than just that. Please help.
Don
-
look at bcp in bol
Other methods
http://www.nigelrivett.net/WriteTextFile.html
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control. -
try
declare @cmd varchar(255)
select @cmd = '"echo ' ? @cola ? ' ' ? @colb ? ' > c:\testsql.txt"'
exec master..xp_cmdshell @cmd , nooutput
(NB - change the ?'s to concat character ( plus sign! - ) it doesn't show on my screen!)
This will write the first line of data to a new file. Change the ">" to ">>" to add new lines to the same file.
How you prime the variables @cola & @colb is down to you. This technique is only suitable for small amounts of data.
-
-- A utilitarian SP I wrote a wile back
-- Drop Procedure SyExport8KBlobToTextFile_sp
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply