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
Blogger Tricks

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

Thursday, July 23, 2015

Unable to generate a temporary class (result=1)

While trying to Invoke Web Services we faced following error:
Error:
Unable to generate a temporary class (result=1).
error CS0029: Cannot implicitly convert type '
WebServices.Proxy.CustomType' to 'WebServices.Proxy.CustomType[]'
error CS0030: Cannot implicitly convert type '
WebServices.Proxy.CustomType[]' to 'WebServices.Proxy.CustomType'

 Cause

A known issue with WSDL.exe can cause a proxy class to be generated incorrectly if an array of complex type includes an element that is also an array of complex type and for which only one element exists.

Resolution:

We need to open reference.cs file and change respective customtype from double dimension to single dimension. Rebuild the project and it started working fine.


Wednesday, April 29, 2015

Task failed because 'sgen.exe' was not found

In one of SSIS package we were trying to build the SSIS script task and it was failing with following  message:

Task failed because "sgen.exe" was not found, or the correct Microsoft Windows SDK is not installed. The task is looking for "sgen.exe" in the "bin" subdirectory beneath the location specified in the InstallationFolder value of the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v6.0A. You may be able to solve the problem by doing one of the following: 1) Install the Microsoft Windows SDK for Windows Server 2008 and .NET Framework 3.5. 2) Install Visual Studio 2008. 3) Manually set the above registry key to the correct location. 4) Pass the correct location into the "ToolPath" parameter of the task.

Reason:
This issue occurs because the default setting for the SSIS script task creates the serialization assembly. The serialization assembly requires files that may not be installed by SQL Server 2008 alone.

Solution:  Installing Windows SDK can resolve the issue, but I found following easier to follow with apparently no side effect.

Disable the Generate serialization assembly option. To do this, follow these steps:
  1. Click Start, point to Programs, point to Microsoft SQL Server 2008, and then click SQL Server Business Intelligence Development Studio.
  2. Open the project file or the solution file.
  3. In Solution Explorer, double-click the SSIS Package to open the Package Designer.
  4. Double-click Script Task in the Package Designer to open the Script Task Editor dialog box.
  5. Click Edit Script to open the Microsoft Visual Studio Tools for Applications (VSTA) IDE.
  6. In Project Explorer, right-click the project, and then click Properties.
  7. Click the Build tab. In the Output area, click Off in the Generate serialization assembly list.
  8. On the File menu, click Exit.

Tuesday, January 13, 2015

Error 13005 : Error translating SQL statement: line dbAMp

We started receiving error 13005 while executing sf_replicate. The error was due to xp_cmdshell which is making call to dbAmp.exe.

We also noted that sf_replicate3 was working fine, a peek into procedure revealed that it uses "Select * into XYZ from salesforce...XYZ" hence works.

Solution:
 
To resolve, upgrade to the latest version of DBAmp using the instructions at http://www.forceamp.com/upgrade.htm
 
 

Wednesday, September 03, 2014

Performance Improvement

 
Update Statistics: 

Let's look at following query.

SELECT *
FROM customer
WHERE city = 'Pune'
 AND phone = '2020666666' -- dummy Number 
 
Above query contain two fields in the "WHERE" clause and suppose there are two indexes defined, each containing one field. One very important notion to remember is that the optimizer can only use ONE index per table.

Therefore, it has to make a decision as to which index to use. Ideally, optimizer should pick index created on phone but in absence of updated statistics it can pick cityIndex.

This decision can be informed one if statistics are up to date.


Avoid foreign key constraints

Foreign keys constraints ensure data integrity at the cost of performance. Therefore, if performance is your primary goal you can push the data integrity rules to your application layer. A good example of a database design that avoids foreign key constraints is the System tables in most databases. Every major RDBMS has a set of tables known as system tables. These tables contain meta data information about user databases. Although there are relationships among these tables, there is no foreign key relationship. This is because the client, in this case the database itself, enforces these rules.