October 17, 2013 at 5:15 am
Hi,
I have the following vendor code (I know the use of sp_OA* is past its sell-buy-date) which is working fine when writing to a Windows 2003 file share but is failing when using the same db code to write to a Windows 2008 R2 file share. The sp receives a filepath/file and a text string to write:
ALTER PROCEDURE [dbo].[CS_AppendToFile](@FileName varchar(255), @Text1 varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int
--DECLARE @ErrorSource varchar (255)
--DECLARE @ErrorDesc varchar (255)
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 error: ' + @FileName
--IF @OLEResult <> 0
--BEGIN
-- EXEC sp_OAGetErrorInfo @FS, @ErrorSource OUT, @ErrorDesc OUT
-- PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
--END
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine error: ' + @FileName + ': ' + @Text1
--IF @OLEResult <> 0
--BEGIN
-- EXEC sp_OAGetErrorInfo @FileID, @ErrorSource OUT, @ErrorDesc OUT
-- PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
--END
exec @OLEResult = sp_OAMethod @FileID, 'Close'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
The sp [CS_AppendToFile] is called from another sp and the initial call successfully opens a file and creates a header record. However, subsequent calls fail with sp_OAMethod returning sub-zero values.
The data passed to [CS_AppendToFile] is good (even when sp_OAMethod fails), so it appears to be sp_OAMethod, and that only for second and subsequent writes. The 'close' file was only added in case it remained locked.
Hopefully, I'm being daft. Has anyone else experienced similar issues?
October 18, 2013 at 6:16 am
Ok, adding the sp_OAMethod calls to the calling procedure appears to have resolved the problem. This doesn't explain 'the why' as the initial 'Header' write is successful with the called sp approach. Some sort of file lock, impervious to closing the file and destroying the FSO?
Judging from the lack of replies, I suspect I'll have to leave it there and hope that the next code release from the vendor is a little more up-to-date SQL-wise.
Thanks for your attention.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply