sp_OACreate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp_OACreate

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-20 : 22:25:38

John writes "I am trying to write to a file from within a Stored Procedure
and I found a stored procedure that looks like it should work
but it doesn't. I checked the permissions on the folder I am
trying to write to and Everyone has full access so I don't know
if maybe there is something wrong with the stored procedure I
found, but I don't get an error when it runs. Here is the
Stored Procedure I am trying to use and this is how I am
executing it:

exec sp_AppendToFile "c:\Inetpub\wwwroot\JOHN1.txt","Howdy John"

CREATE PROCEDURE sp_AppendToFile( @FileName varchar(255), @Text1 varchar(255) ) AS

DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = master..sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--PRINT 'OLEResult = ' + Cast(@OLEResult as char(10))

--Open a file
execute @OLEResult = master..sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
execute @OLEResult = master..sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = master..sp_OADestroy @FileID
EXECUTE @OLEResult = master..sp_OADestroy @FS
GO

Thank you,
John"

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-05-21 : 02:42:03

It works ok for me on SQL2000 SP3, does the file exist ? Here's another version that will check if the file exists and create it if it doesn't, @append='Y' will append to an existing file

CREATE PROCEDURE sp_writefile (@filename varchar(255),@msg varchar(255),@append char(1)='N')
AS
SET NOCOUNT ON

DECLARE @hr int
DECLARE @fso int 
DECLARE @file int 
DECLARE @exists varchar(5)
DECLARE @ForAppending int ; SET @ForAppending = 8 /* Script Constant */

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

/* Check file exists */
EXEC @hr=sp_OAMethod @fso, 'FileExists',@exists OUT, @filename
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

IF @exists='True' /* File exists */
BEGIN
        IF @append='Y'
        BEGIN
                EXEC @hr=sp_OAMethod @fso, 'OpenTextFile',@file OUT, @filename , @ForAppending
                IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso                        
        END
        ELSE
        BEGIN
                EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @filename , 'True'
                IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso                        
        END
END
ELSE
        BEGIN
                EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @filename
                IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso                        
        END


EXEC @hr=sp_OAMethod @file,'WriteLine',NULL,@msg 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @file

EXEC @hr=sp_OADestroy @file 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @file

EXEC @hr=sp_OADestroy @fso 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

go

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

shifis
Posting Yak Master

157 Posts

Posted - 2003-05-21 : 18:06:26

I tried both of them and no one function to me, when I run the store procedure it sent this message:

EXEC sp_writefile 'C:\C.TXT','TERESITA'

The command(s) completed successfully.

The file already exist, if I see the c:\c.txt file there is nothing.

What I am doing wrong?, I am working with SQL Sever 2000, too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources