ORA-12154 : TNS Could not resolve the connect Identifier

Accessing an Oracle DB over a 64 bit system was a pain for many SQL Server DBAs.
The purpose of this document is to explain how to fix issues running a SSIS package, Linked Server and SQL Agent which are using Oracle OLEDB under 64bit SQL Server 2008.

The Following solution is based on Oracle 11g version. You can download Oracle 11g client from http://www.oracle.com/technology/software/products/database/index.html

You will need 32bit and 64bit both Oracle clients.

Oracle Client & OLEDB component Installation steps in 64bit Windows:

1. If you have old Oracle clients, copy tnsnames.ora and sqlnet.ora files for backup. 
   (They are under %ORACLE_HOME%/network/ADMIN/.)
2. Uninstall & Delete previous version of Oracle client include installed directory. 
   If you have or ever installed Oracle client, uninstall them and reboot the server. Then you can delete old Oracle directory. If you don’t reboot, you cannot delete them. 
3. Run setup.exe of Oracle 11g Client in 64bit SQL Server running machine.
   You have to install 32bit first.
4. Select “Custom” for OLEDB component installation in "Select Installation Type" step. 
   

   

5. Properly select Oracle Base and Path. 
   

   

6. You must select “Oracle Windows Interfaces 11.x.x” component for OLEDB in “Available Product Components” step. 
   

   

7. After installation, restore TNSNAMES.ORA backup file under the new location of %ORACLE_HOME%/network/admin folder.
8. Repeat above step #4-#7 with 64bit Oracle 11g Client setup.  
   Oracle "Base directory" is same as the 32bit, but Path is supposed to be different from the previous one in above step #5.  
   i.e) If Oracle 32bit Client Path was C:/Oracle/product/11.1/client_32, 
     Then 64bit Client Path is under C:/Oracle/product/11.1/client_64.
9. You will see an error message “OracleMTSRecoveryService already exists” as below screenshot during the 64bit client installation. You can “Ignore” it.

   

10. Modify the system registry settings as follows. And then reboot server! 
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI     and 
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI 
    are correct: 
    a. OracleOciLib = oci.dll 
    b. OracleSqlLib = orasql11.dll   (old: SQLLib80.dll) 
    c. OracleXaLib = oraclient11.dll  (old: xa80.dll) 
11. Test sqlplus.exe or tnsping.exe in command console.

Now you can access Oracle OLEDB provider in BIDS (Business Intelligence Development Studio) & SSMS both places. What I tested was:

• Design SSIS package with Oracle data source (OraOLEDB) in BIDS
• SSIS package debug & run in BIDS
• C# & VB sample code test to access OraOLEDB.Oracle provider in VisualStudio
• Add the developed SSIS package & run on SSMS
• Add SSIS package job in SQL Agent & run on SSMS
• Add Linked Server to access Oracle Server through Oracle OLEDB in SSMS

One thing you have to keep in mind is that BIDS is based on Visual Studio 32bit modules. When you design & test a new SSIS package on it, actually it’s using 32bit Oracle OLEDB modules. 
After the SSIS package added in 64bit SQL Server, SQL Agent is using 64bit Microsoft & Oracle OLEDB modules.

NOTE1:

While you are developing or testing SSIS package, you might see the following ignorable messages:  “Warning: Cannot retrieve the column code page info from OLE DB…”.

NOTE2:

When you add new SSIS package in SQL server, you have to select “Rely on server storage and roles for access control” in Package Protection Level as below screenshot to avoid XML password encryption error. For some reason, saved Oracle password couldn’t be decrypted by SSIS service on my test.

NOTE3:

You might see the following error “Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB Provider “OraOLEDB.Oracle” for linked server XXX (Error:7399)” in an Oracle Linked Servers as follows.

To resolve this, open properties of OraOLEDB.Oracle provider, and set “Enable” for “Allow inprocess” option as follows.

Instance>>Sever Objects>>Linked Servers>>Providers>>OraOLEDB.Oracle

Microsoft Reporting Services in Silverlight Applications is Possible

 

The main obstacle to the spread of Microsoft Silverlight as a platform for line-of-business RIA’s is the lack of built-in reporting components. At the same time, not a single corporate application can function without effective reporting system.

Perpetuum Software allows you to combine two powerful Microsoft technologies: Silverlight and Reporting Services, so that you can leverage their advantages in your business intelligence applications.

Silverlight Viewer for Reporting Services provides you the ability to view Reporting Services reports in Silverlight applications. The component is a native Silverlight control and it easily integrates into any Silverlight application. Thus, it is possible to use the entire set of features which are, as a rule, available in the original desktop applications, including dynamic scaling, smooth panning with animation, interactive sorting, search and so on.

Silverlight Viewer can be used in applications, located directly on the html page, as well as the out-of-browser mode.

Delicate setup of viewer appearance with the use of styles and templates allows the inclusion of the component into the design of any application. All aspects of the Silverlight Viewer component behavior can be controlled from the code.

The user will view only the necessary report pages without loading the entire report. This feature provides quick reporting while reducing internet traffic Fonts will be loaded automatically if they are not set on the client side.

The main competitive advantages of the Silverlight Viewer for Reporting Services are:

• WYSIWYG report preview in web browsers with support for scaling, scrolling, panning. The reports are displayed in vector graphic. The advantage is that it provides high quality report representation which is impossible to achieve when reports are converted to html;
• Out-of-browser mode;
• Abilities of the full-functional desktop applications: search, interactive sorting, dynamic zooming, navigation through the reports via hyperlinks, input of the report parameters by the user and so on;
• Smooth animation when you manipulate reports, no sudden changes on the display occur when you scroll, zoom in or out or navigate the report;
• Support for styles and skins allow the change of any control elements;
• Reports export to PDF, Excel, Html, Cvs, Xml, Mhtml, TIFF;
• Automatic font loading.

The current product version only supports MS Sql Reporting Services 2005. The support for MS Sql Reporting Services 2008 will be available in the next Silverlight Viewer for Reporting Services release.

Use the functionality of the Silverlight technology for displaying reports from Microsoft SQL Server Reporting Services!

Download Silverlight Viewer for Reporting Services...

Error in Performance Point 2007

I was involved in a Project with Microsoft Performance Point 2007 that requires Integration with lot of PPS Web Service APIs and 3.5 Asp.NET Application

I logged most the issues I faced and I’d like to leave this thread as an open discussion thread to help other in there deployments and Development with PPS.

Below are a list of Issues and Fixes that will help you solve your problem.

Configuring PPS on Production Environment

1. Install PPS on the SharePoint Server

2. Configure PPS to Install Dashboard Viewer for SharePoint on the Moss Server

3. Please note the following Steps will be done on all Moss Servers in the production farm

a. Locate the web.config file for your SharePoint site, as well as the PerformancePoint Monitoring Designer web service and its Preview directory.

b. Use any text editor (or Visual Studio), and do a search/replace to replace all references to System.Web.Extensions 1.0.61025.0 to .Net's3.5.0.0.

c. Add the following line to your web.config under the <configuration> node:

<runtime> 

    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> 

      <dependentAssembly> 

        <assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35" culture="neutral" /> 

        <bindingRedirect oldVersion="1.0.61025.0" newVersion="3.5.0.0" /> 

      </dependentAssembly> 

    </assemblyBinding> 

</runtime>

Remember there are 3 web.configs that needs to change here:

i. under PPSMonitoring/WebService

ii. under PPSMonitoring/Preview

iii. SharePoint Site[MM1]

4. Open your SharePoint web.config for the current web application and find HTTPHandlers replace it with the following Set[MM2]

<httpHandlers>

<remove verb="GET,HEAD,POST" path="*" />

<add verb="GET,HEAD,POST" path="*" type="Microsoft.SharePoint.ApplicationRuntime.SPHttpHandler, Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />

<add verb="OPTIONS,PROPFIND,PUT,LOCK,UNLOCK,MOVE,COPY,GETLIB,PROPPATCH,MKCOL,DELETE,(GETSOURCE),(HEADSOURCE),(POSTSOURCE)" path="*" type="Microsoft.SharePoint.ApplicationRuntime.SPHttpHandler, Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />

<add verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false" />

<add verb="*" path="_vti_bin/ReportServer" type="Microsoft.ReportingServices.SharePoint.Soap.RSProxyHttpHandler, RSSharePointSoapProxy, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

<add verb="*" path="Reserved.ReportViewerWebPart.axd" type="Microsoft.ReportingServices.SharePoint.UI.WebParts.WebPartHttpHandler, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

</httpHandlers>

5. If you installed URL Scan in our production Environment (as in testing) you may face a problem on all Images are not Displayed in the PPS Dashboard Viewer WP The Solution would be the following

a. Open the following folder “C:\Windows\System32\inetsrv\urlscan”

b. Edit URLScan.ini file

i. AllowDotsInPath = 0 to 1

6. Go to the following path in both SharePoint Servers

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\wpresources

Replace the content of the web.config with the following

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<configuration>

<system.web>

<customErrors mode="Off"/>

<httpHandlers>

<add verb="*" path="*.aspx" type="System.Web.UI.PageHandlerFactory, System.Web, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

<remove verb="*" path="*.asmx" />

<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false" />

</httpHandlers>

</system.web>

<runtime>

<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

<dependentAssembly>

<assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35" culture="neutral" />

</dependentAssembly>

</assemblyBinding>

<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint.Dsp" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint.Dsp.OleDb" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint.Dsp.SoapPT" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint.Dsp.Sts" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint.Dsp.XmlUrl" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint.intl" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint.Library" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name="Microsoft.SharePoint.Security" publicKeyToken="71e9bce111e9429c" culture="neutral" />

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />

</dependentAssembly>

</assemblyBinding>

</runtime>

</configuration>

Performance Point 2007 Hotfixes

I was looking around through Microsoft website looking Performance Point 2007 hot fixes and I found out that they release a lot of hot fixes that address lots of Issues in Performance Point

I hope this will you since I faced many issues with this product that these hot fixes solve

Build Number	Link	KB Number
3.0.4318.00	Description of the PerformancePoint Server 2007 hotfix package: June 17, 2009	971928
3.0.4317.00	Description of the PerformancePoint Server 2007 hotfix package: May 26, 2009	970524

Some of the new fixes that are available in the latest hotfixes that might be of interest to you are:

• Fix for the disappearing Analytical Chart toolbar when you click on the Filter buttons to remove empty items (it still does disappear when you click on the drop down arrow and select Reset View though)
• Fix for exporting an Analytical Chart with more than 14 items in the legend.  Now all of the items will be exported to Excel if there are more than 14
• Another fix for exporting to Excel with scorecards to resolve issue of exporting values from 9.5 to 9.9 so that they maintain their format instead of being multiplied by 100
• For Planning the fix was to remove a duration restriction for extending the calendar to a value greater than 25 (now you can go up to 200)

These are just a few of the minor fixes that were done, check out the official links above to see the complete list for each hotfix and just a reminder that these are cumulative, so if you download the latest it will include all of the previous hotfixes also.

Here is the previous list I posted and I updated the list to include the KB number.

Build number	Link	KB Number
3.0.4315.00	Description of the PerformancePoint Server 2007 hotfix package: April 28, 2009	969842
3.0.4313.00	Description of the PerformancePoint Server 2007 hotfix package: February 26, 2009	968131
3.0.4312.00	Description of the PerformancePoint Server 2007 hotfix package: February 24, 2009	967900
3.0.4311.00	Description of the PerformancePoint Server 2007 hotfix package: February 9, 2009	967343
3.0.4305.00	Description of the PerformancePoint Server 2007 hotfix package: February 2, 2009	961878
3.0.4300.00	Description of the PerformancePoint Server 2007 hotfix package: December 22, 2008	961041
3.0.4209.00	Description of PerformancePoint Server 2007 Service Pack 2	958291
3.0.3917.00 (I had 3.0.3916.00 after my install)	Description of PerformancePoint Server 2007 Service Pack 1	948060
3.0.3520.0	RTM Release of PPS 2007