kavya_deepthi
asked on
Problem using sp_OACreate to create a text file in SQL server 2005
I am using the below code to create a log file at remote machine in SQL server 2005.
I am able to create a file successfully using the same code on SQL server 2000 but the file is not getting created in SQL server 2005.
I am able to create a file successfully using the same code on SQL server 2000 but the file is not getting created in SQL server 2005.
ALTER PROCEDURE GEGR_SPODA_SHIP_RECV_temp @param1 varchar(10) = 'DEV'
AS
declare
@vchrFile VARCHAR(1000) ,
@lv_string Varchar(8000),
@CmdTxt Varchar(8000),
@vchrText Varchar(8000),
@vchrFileID INT ,
@FS INT ,
@RC INT ,
@DBID Int,
@DBNAME VarChar(128)
SELECT @vchrFile = 'C:\cmmsdevdata\Log\Temp.txt'
EXECUTE @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXEC @RC = sp_OAMethod @FS , 'OpenTextFile' , @vchrFileID OUT , @vchrFile , 8 , -1
set @vchrText ='Text'
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrText
EXECUTE @RC = sp_OADestroy @vchrFileID
EXECUTE @RC = sp_OADestroy @FS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Can someone help me with this.
ASKER
When i am creating the file on SQL server2005..i am giving a different path accordingly.So that should not be the problem
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also the credentials to write could be diffrent, so it could be possible it runs when you start the procedure in a query window with your credentials and it fails when it runs with the credentials of SQL*Agent.