Code Project

Link Unit

Monday, September 11, 2017

working with report model

To create a shared data source

  1. In Solution Explorer, right-click Shared Data Sources, and then select Add New Data Source.
  2. In the Name box, type: RMS.
  3. In the Type drop-down list, select Report Server Model.
  4. In the Connection string area, type: Server=<>; datasource=<>.
  5. Select Credentials.
  6. Select Use Windows Authentication (Integrated Security) and then click OK.
    The data source appears in the Shared Data Sources folder in Solution Explorer.
  7. On the File menu, click Save All.
Blogger Tricks

XML parsing: line 1, character 75, illegal name character

When saving strings to XML, or when trying to extract text within tags it important to escape invalid characters . The following table shows the invalid XML characters and their escaped equivalents.

Invalid XML Character Replaced With
<                          <
>                          >
" "
' '
& &

if we try following code in SQL window, where we are trying to extract text from within html tags.

declare @v varchar(40)
Set @v='a & b'
Select cast(@v as XML).value('.','varchar(max)')

we will receive error like "XML parsing: line 1, character xx, illegal name character"


declare @v varchar(40)
Set @v='a & b'
Select cast(replace(replace(replace(@v,'>','><![CDATA['),'</',']]></')+']]>','<![CDATA[]]>','') as XML).value('.','varchar(max)')

As we know CDATA section is "a section of element content that is marked for the parser to interpret as only character data, not markup." so we will include CDATA in such a way that text is inside it and parsing of it wouldn't result in error. 

Hope it helps

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.
For Windows Server x64: Create the following directory:
For Windows Server x86: Create the following directory:

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.

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:
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'


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.


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.

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.