Code Project

Link Unit

Monday, June 13, 2016

Error ‘Microsoft Office Excel cannot access the file’ while accessing Microsoft Office 11.0 Object Library from SSIS

When executing the Open function. I received the following exception:
Microsoft Office Excel cannot access the file ‘e:\report\report.xls’.
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.
SOLUTION:
For Windows Server x64: Create the following directory:
C:\Windows\SysWOW64\config\systemprofile\Desktop
For Windows Server x86: Create the following directory:
C:\Windows\System32\config\systemprofile\Desktop

Monday, May 23, 2016

Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded. Use the applicable interface instead


.NET 4.0 allows primary interop assemblies (or rather, the bits of it that you need) to be embedded into your assembly so that you don't need to deploy them alongside your application.

Reason:
In most cases, this error is the result of code which tries to instantiate a COM object.

For example
Excel.ApplicationClass xlapp = new Excel.ApplicationClass();

Solution #1
Typically, in .NET 4 you just need to remove the 'Class' suffix and compile the code:
Excel.Application xlapp = new Excel.Application();

Solution #2
For whatever reason, this assembly can't be embedded. Just open the Properties tab for the assembly in Visual Studio 2010 and set "Embed Interop Types" to "False".


Tuesday, January 12, 2016

Long running SQL Agent job


We tried to launch an .exe from SQL Server Agent Job and job keeps on running, it need to be stopped manually.

Steps to troubleshoot
#1. Any program that has windows interaction i.e. opens any type of window e.g. Notepad, ms-paint or excel etc.

#2. So it simply means we can only use console programs and that too without any prompts or wait for input.
Therefore, we can't execute Date or Time (DOS internal command) as they expect input.

Execute the .exe given in job step from command prompt to validate there are no wait for input.

Hope it helps