I'm looking everywhere to write easily the result of an T-SQL query in an output file.
I work with MSDE (SQL2000) an the T-SQL code is in a stored procedure.
The query is like :
"select * from myTable where ..."
There are about 150 fields in the table to return in the output file.
I would like to write the line result in one line in an output file in the hard drive.
I've tryed 2 method :
1) Use of "xp_cmdshell" command
DECLARE @PrintInLogFile sysname
DECLARE @var sysname
SET @var = ...
SET @PrintInLogFile = 'echo ' + @var + ' > C:\File.log'
EXEC xp_cmdshell @PrintInLogFile
The problem is that I can't use this command in production because of security constraints.
2) Use of "sp_OAMethod" command with 'WriteLine' method
DECLARE @FS int
DECLARE @FileID int
DECLARE @iCOMret int
DECLARE @FileName VARCHAR(4000)
--creation de l objet pour manipulation du fichier de log
EXECUTE @iCOMret = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXECUTE @iCOMret = sp_OAMethod @FS, 'CreateTextFile', @FileName, true
--Ouvre le fichier (2 = ForWriting, 8 = ForAppending)
EXECUTE @iCOMret = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 2
EXECUTE @iCOMret = sp_OAMethod @FileID, 'WriteLine', Null, 'Bonjour'
-- Fermeture fichier de log
EXECUTE @iCOMret = sp_OADestroy @FileID
EXECUTE @iCOMret = sp_OADestroy @FS
The problem with this method is that I can't write one line with the resul of a select *
Can anyone help me ?
I'm sure that in T-SQL we can write easily the entirely result of a select * in one line of an output file in the hard drive...
Thnaks in advance !