Create a file from SQL Statement
-
Hello,
Can anyone advise on the best way to create a file from running a SQL Statement that does not use DTS ?
The reason why not DTS is there is a lot of control and add character used as the column and row delimeter that DTS doesn't like.
Thanks
J
-
look up osql or bcp in Books online
-
Not sure what file you want. But used BCP if you want to copy to flat file.
-
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
@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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply