|
Hi,
I have written a Stor proc which will write the text/contents into a text file in the remote system. this text file is placed inside a shared folder, and this folder permission is given to everyone(full control). this works fine in some networks and gives the error in some other networks. I tried to print the error in sql query analyzer. Error: OpenTextFile @OLEResult-2146828235 0 Error : WriteLine @OLEResult-2147211483 Below is the stor proc. does any one has idea why it is behaving differently in some places. any ideas would be much appreciated. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SPAppendtoFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SPAppendtoFile] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO Create Proc SPAppendtoFile ( @FileName varchar(255), @strText varchar(500) ) AS Begin Declare @FS int, @OLEResult int, @FileId int DECLARE @output varchar(255) DECLARE @hr int DECLARE @source varchar(255) DECLARE @description varchar(255) Execute @OLEResult = SP_OACreate 'Scripting.FileSystemObject', @FS Out IF @OLEResult <> 0 BEGIN PRINT 'Error: Scripting.FileSystemObject' END --Open a File specified by the @File input parameter Execute @OLEResult = SP_OAMethod @FS, 'OpenTextFile', @FileId out, @FileName, 2, 1 -- Prints error if non 0 return code during sp_OAMethod OpenTextFile execution IF @OLEResult <> 0 BEGIN PRINT 'Error: OpenTextFile' Print '@OLEResult' + cast(@OLEResult as varchar(8000)) PRINT @@error END EXEC @hr = sp_OAGetErrorInfo @OLEResult, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @OLEResult, @FileId RETURN END -- Appends the string value line to the file specified by the @File input parameter Execute @OLEResult = SP_OAMethod @FileId, 'Write', Null, @strText -- Prints error if non 0 return code during sp_OAMethod WriteLine execution IF @OLEResult <> 0 BEGIN PRINT 'Error : WriteLine' END EXEC @hr = sp_OAGetErrorInfo @OLEResult, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END --PRINT 'OLE Automation Error Information' Execute @OLEResult = SP_OADestroy @FileId Execute @OLEResult = SP_OADestroy @FS End GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO regards Krishnan
SPONSORED LINKS
YAHOO! GROUPS LINKS
|
