Link to home
Start Free TrialLog in
Avatar of kavya_deepthi
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.
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.

Open in new window

Avatar of jogos
jogos
Flag of Belgium image

When procedure runs the file path must be from the point of view of the SQL2005 instance, maybe that's diffrent between your SQL2000 and SQL2005.
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.
Avatar of kavya_deepthi
kavya_deepthi

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
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial