cannot write files to network using sp_OACreate & sp_OAMethod

Question

Post reply

cannot write files to network using sp_OACreate & sp_OAMethod

Bhagat

SSC-Addicted

Points: 405

June 13, 2023 at 8:44 am

#4210115

On a monthly basis files are created to a network location from sql server.

However this month is has been failing and have managed to narrow the problem down to saving the file as it produces an error code of -2146825284.

My thinking is permissions may have changed, but checking the security of the folder, the necessary users have full control.

Any ideas where to debug, below is the code

open cur
fetch next from cur into @invoiceno,@pdf_filename
while @@fetch_status=0 begin
select @invoiceid=sysinvoiceid from AllocatedInvoice WITH (NOLOCK) where invoiceno=@invoiceNo
SELECT @invoiceNo,@invoiceid
select @image=fileimage from [sqlcluster].repository.dbo.bills bill where bill.sysinvoiceid=@invoiceid and filetype='pdf'
if @invoiceNo is not null begin
EXEC @hr = sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
IF @hr <> 0  
BEGIN  
    RAISERROR('Error %d creating object.', 16, 1, @hr)
    RETURN
END
EXEC @hr = sp_OASetProperty @ObjectToken, 'Type', 1;
IF @hr <> 0  
BEGIN  
    RAISERROR('Error %d Set Property.', 16, 1, @hr)
    RETURN
END
EXEC @hr = sp_OAMethod @ObjectToken, 'Open';
IF @hr <> 0  
BEGIN  
    RAISERROR('Error %d opening file.', 16, 1, @hr)
    RETURN
END
EXEC @hr = sp_OAMethod @ObjectToken, 'Write', NULL, @image;
IF @hr <> 0  
BEGIN  
    RAISERROR('Error %d writing file.', 16, 1, @hr)
    RETURN
END
EXEC @hr = sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @pdf_filename, 2;
IF @hr <> 0  
BEGIN  
    RAISERROR('Error %d saveing file.', 16, 1, @hr)
    RETURN
END
EXEC @hr = sp_OAMethod @ObjectToken, 'Close';
IF @hr <> 0  
BEGIN  
    RAISERROR('Error %d closing file.', 16, 1, @hr)
    RETURN
END
EXEC @hr = sp_OADestroy @ObjectToken;
IF @hr <> 0  
BEGIN  
    RAISERROR('Error %d destroying file.', 16, 1, @hr)
    RETURN
END

end

fetch next from cur into @invoiceno,@pdf_filename
end
close cur
deallocate cur

commit tran

end

as on what to check, on permissions?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

Are you saying that permissions have definitely not been changed?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bhagat SSC-Addicted Points: 405 More actions · Answer 2

yes, we can manually create files onto that network location as the users.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

OK, is the SQL Server service running under the same account as one of the users you have tested?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bhagat SSC-Addicted Points: 405 More actions · Answer 4

Thanks Phil for getting back.

Bit of progress, the output location is a Share, when changed to a different share, the writing of files work.

So going to ask my IT department to look into that.

Bhagat SSC-Addicted Points: 405 More actions · Answer 5

the problem with security in the fileshare, for some reason it was missing MSSQL$ITSQL account.

adding it back, allowed files to be written.