November 14, 2003 at 5:27 pm
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
November 15, 2003 at 2:29 pm
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.
November 17, 2003 at 2:54 am
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.
November 17, 2003 at 1:18 pm
-- 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
@OutPutFileFull 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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy