Error from sp_OACreate 'Scripting.FileSystemObject'

Question

I'm using the following function to write text to a file from an SQL-Server stored procedure:-

USE [mydb]
GO
/****** Object:  StoredProcedure [dbo].[spWriteStringToFile]    Script Date: 03/01/2012 15:51:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spWriteStringToFile]
(
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
AS
DECLARE @objFileSystem   int,
        @objTextStream   int,
        @objErrorObject  int,
        @strErrorMessage Varchar(1000),
        @Command         varchar(1000),
        @hr              int,
        @fileAndPath     varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@Filename

if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream, 
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare 
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject, 
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')

raiserror (@strErrorMessage,16,1)
end

Use of this stored procedure results in the error message:-

Msg 50000, Level 16, State 1, Procedure spWriteStringToFile, Line 48
Error whilst opening the File System Object,

Now I don't know all that much about SQL Server and particularly not stored procedures; could anyone give me a pointer as to what the problem could be? I have tried to get the error number from the sp_OACreate call without any success. Looking at this question here I checked that scrrun.dll exists, which it does.

Edit Incidentally, the name of the stored procedure is underlined in red and showing 'invalid object name'; the object must exist or I couldn't edit it. Ought I to worry about that?

score 1 · Answer 1 · answered Jun 29 '15 at 20:25

It looks like you copied that stored procedure from Simple Talk's web site, and they have a few issues in the code.

First off, you need to create the stored proc before you can execute it - change the ALTER at the top to a CREATE, and then you'll be able to run it.

Second, if you have a case sensitive database, the mixed cases of the variables (@HR and @hr) are going to error out as well - they need to be all the same case.

This should do the trick:

CREATE PROCEDURE spWriteStringToFile
 (
@String NVarchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)

--
)
AS
DECLARE  @objFileSystem int
        ,@objTextStream int,
        @objErrorObject int,
        @strErrorMessage Varchar(1000),
        @Command varchar(1000),
        @HR int,
        @FileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @HR = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@Path+'\'+@Filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @HR = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
    , @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream, 
    @strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @HR = sp_OAMethod  @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @HR = sp_OAMethod  @objTextStream, 'Close'

if @HR<>0
    begin
    Declare 
        @Source varchar(255),
        @Description Varchar(255),
        @Helpfile Varchar(255),
        @HelpID int

    EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
        @Source output,@Description output,@Helpfile output,@HelpID output
    Select @strErrorMessage='Error whilst '
            +coalesce(@strErrorMessage,'doing something')
            +', '+coalesce(@Description,'')
    raiserror (@strErrorMessage,16,1)
    end
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

Error from sp_OACreate 'Scripting.FileSystemObject'

1 Answers1