MS Word interop and {"Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"}

When you first see below exception your Word interop code execution in production environment, you think that there is something directly related with security. It normally results in spending more time on verifying the security of files and com dlls.

System.UnauthorizedAccessException {"Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"}

But is that the actual problem?
No.When we got this exception, we spent sometime on the security aspects. But when we looked at the below code we easily found that this is thrown because a com object is not supporting TextFrame property. The code was as follows.

---

For Each oShape As Word.Shape In wDoc.Shapes

                    If (oShape.TextFrame IsNot Nothing) Then

                        If (oShape.TextFrame.HasText <> 0 AndAlso oShape.TextFrame.TextRange IsNot Nothing) Then

                            'Processing code

                        End If

                    End If

...

---

Actually when we iterate through the Shapes, there may be some shapes which may not support the TextFrame property .We need to avoid those shapes from the TextFrame processing. This confusion would have been avoided if MSFT provides a meaningful exception message.

Happy debugging...

Reading and writing files in StoredProcedure

I don’t know why I didn’t thought about reading a text file from a stored procedure until I came across a requirement in SSRS.First I searched for something like usp_OpenFile and usp_ReadFile as usual.But no results.After that I realized that I can interact with system from SQL Server only through the Ole Automation.

It was another new thing to me.I have never used Ole Automation through SQL Server.It is not enabled in SQL server by default.We need to enable through the statement sp_configure.

First I tried to see the status of Ole Automation using the query

EXEC sp_configure 'Ole Automation Procedures';
GO

But this returned error saying that this is an advanced option.Then I turned on the advanced options by the below query

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

After this I turned on Ole Automation.

sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Now the environment is ready for Ole Automation.Just need to execute the SQL which opens and reads a file using COM objects.

DECLARE 
        @strPath VARCHAR(255),
        @objFileSystem int,
        @objTextStream int,
        @strRead Varchar(8000),
        @strCommand varchar(1000),
        @HRESULT int,
        @YesOrNo INT 
--Initialize variables        
select @strPath='c:\temp\test.txt'
select @strRead=''

--Creating the COM File System Object'
EXECUTE @HRESULT = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT
--HRESULT will be 0 on success
if @HRESULT=0 select @strCommand=@strPath
--Open file
if @HRESULT=0 execute @HRESULT = sp_OAMethod   @objFileSystem  , 'OpenTextFile', @objTextStream OUT, @strCommand,1,false,0
--Checking for EOF
if @HRESULT=0 execute @HRESULT = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
--Reading file
IF @YesOrNo=0  and @HRESULT=0 execute @HRESULT = sp_OAMethod  @objTextStream, 'ReadAll', @strRead OUTPUT
--Displays the read text.
select @strRead

-- Closing the file "'
if @HRESULT=0 execute @HRESULT = sp_OAMethod  @objTextStream, 'Close'
EXECUTE  sp_OADestroy @objTextStream

Its not easy if you are not from the COM world.It’s VB equivalent code can be found here in msdn.

You can use the same Ole Automation to write into file as well. Make it as function by returning the variable @strRead.If you just want to run this, copy above code  into a query window of SQL Server Management Studio and press F5.I am not sure whether this will work in other databases except SQL server 2008.

Happy scripting…