how to dump or append data to a text file
-
i am doing some insert or update on my db, and i want to dump the errors, if any, to a text file.
If the text file deosnt exist, then i want to create it. Then append data to it, in smthg like:
insert into mytable values (f1,f2,f3)
IF
@@ERROR <> 0
begin
-- check if text file exist
-- if no create it and write 'an error occured etc...'
-- else append to it another line 'an error occured etc...
end
thanks for helping!!
-
CREATE
PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varchar(255)) AS
DECLARE
@FS int, @OLEResult int, @FileID int
EXECUTE
@OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF
@OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--Open a file
execute
@OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF
@OLEResult <> 0 PRINT 'OpenTextFile'
--Write Text1
execute
@OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF
@OLEResult <> 0 PRINT 'WriteLine'
EXECUTE
@OLEResult = sp_OADestroy @FileID
EXECUTE
@OLEResult = sp_OADestroy @FS
--testing
--exec sp_AppendToFile 'c:\TestCase.txt', 'Some test text for the example first'
--exec sp_AppendToFile 'c:\TestCase.txt', 'Some test text for the example bledu'
ref:http://www.motobit.com/tips/detpg_SQLWrFile/
if you are using sql 2005 you will need to first enable OLE automation if its not already enable, and on the unsecured sql 2000 box you should not get any errors
-
I've usually run things like this through osql and then captured the error output in a file with the error switch.
-
that seems to work only if i am in sysadmin
but i want a regular user to be able to dump into text file
i even tried xp_cmdshell, any user outside sysadmin can't execute it
-
how about raiserror(...) with log?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply