Va salut,
Tot studiind netul pentru a vedea cum se trimite rezultatul unui query intr-un fisier text, am descoperit (se pare) clasica
procedura a dlui
Phil FactorInsa procedura asta imi sterge tot fisierul text si pune rezultatul.
Ce imi doresc este (si nu i-am dat de cap) posibilitatea de a face sau append la fisier sau sa am fisierul gol inainte de rezultatul query-ului .
Am atasat mai jos procedura unde am pus si un if unde am crezut ca ar fi linia esentiala pentru append sau write.Totusi se pare ca nu acolo
ar fi.
Aveti ceva sugestii, va rog?
ALTER PROCEDURE [dbo].[spWriteStringToFile]
(
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100),
@writeOrAppend bit -- parametru pentru alegere Write sau Append file
--
)
AS
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80)
set nocount on
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'OpenTextFile', @objTextStream OUT, @FileAndPath,8,True
---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
-- probabil ca in loc de linia de deasupra ar trebui ceva ca mai jos unde 8 se pare ca e pt append si 2 pt write
if @writeOrAppend=0 -- daca e 0 fac append, daca nu sterg fisierul ca sa fie curat
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'OpenTextFile', @objTextStream OUT, @FileAndPath,8,True
else
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile', @objTextStream OUT, @FileAndPath,2,True
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String
if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream