System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file

I develop a package in SSIS, I need to use format excel sheets and I am sure many of you will using it because of business people needing data in pretty form. Any ways, till this part when I run from SSIS it works like a champ. But as soon as I scheduled in SQL agent on server it started to fail with below error.

Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'xxxxxlsx'. There are several possible reasons:    ? The file name or path does not exist.  ? The file is being used by another program.  ? The workbook you are trying to save has the same name as a currently open workbook.     at Microsoft.Office.Interop.Excel.Workbooks


After sometime when I was about to give up because the solutions I was seeing on the web either say go play with DcomConfig or hack registry keys yeah like some one is gonna let me do that on prod server.

Finally found MS document that hints the below procedure it works and best part don't need to mess with registry keys.

Solution(1)

Create folder called "Desktop" really dont know why but it works.

Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop” (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop” (for 32 bit Windows)

In my case I had created folder on both locations and give read write permissions on it

P.S

You will still see this error even if you have created the folder but the permissions are not correct.

Could not Obtain Exclusive Lock On Database

Some time this error appears and First things comes in our mind is What has possibly gone wrong, I normally see this error when I am restoring the databases or overwrite a a dump database.

Since SQL is also a software and like every software it has its own way of operating and connecting with other entities. Let solve this issue, what steps will take care of this error.




1)  If the above error appears, close your Studio Management Console. Because sometimes its Studio Management that is holding a old connection. First and normally it fixes the problem.

2)  Second reason may come because of "Auto Close" property if turn "ON", Go in DB properties and turn off this property.

3) Check if any other window is open holding a connection to the specified DB. 


Use below script to turn "AUTO Close" off

SELECT DATABASEPROPERTYEX('Your DB' , 'IsAutoClose' ) AS [AutoClose]

GO

ALTER DATABASE [Your DB] SET AUTO_CLOSE OFF

GO

Execution cannot continue as the language dependent resource file

Arrrrrrr!! Yes that the first reaction came from my junior DBA. And I think it necessary as he is learning. Ok what to do now. Solution is straight and simple.

As you receive this error you will be prompted for  location where the SQL cannot find the required file.
Download those files easily available put in the folder specified and you are good to go.

Is nt this simple and straight? So Join our mailing for small but effective techniques.