ASP.Net Error [im003] – Oracle Error

October 8, 2014

Error [im003] specified driver could not be loaded due to system error  5 (oracle in oracle9i)

I had a strange error when accessing an Oracle database over ODBC using an ASP.NET (4.0 framework) Application running on IIS 6.0.

The ASP.net page could not load the database tables while the ODBC manager gave no errors ?

image

In the eventviewer I saw a lot of ASP.NET 4.0.30319.0 – Event ID 1309

image

This showed definitely a security issue on the Oracle home directory.

image

Solution :

Oracle 9.2 Client software requires that you give the Authenticated User privilege to the Oracle Home by following these steps:

1. Log on to Windows as a user with Administrator privileges.

2. Launch Windows Explorer from the Start Menu and and navigate to the ORACLE_HOME folder. This is typically the “Ora92” folder under the “Oracle” folder (i.e. C:\Oracle\Ora92).

3. Right-click on the ORACLE_HOME folder and choose the “Properties” option from the drop down list. A “Properties” window should appear.

4. Click on the “Security” tab of the “Properties” window.

5. Click on “Authenticated Users” item in the “Name” list (on Windows XP the “Name” list is called “Group or user names”).

6. Clear the “Read and Execute” box in the “Permissions” list under the “Allow” column (on Windows XP the “Permissions” list is called “Permissions for Authenticated Users”).

7. Re-check the “Read and Execute” box under the “Allow” column (this is the box you just unchecked).

8. Click the “Advanced” button and in the “Permission Entries” list make sure you see the “Authenticated Users” listed there with:

·

Permission = Read & Execute

·

Apply To = This folder, subfolders and files

If this is NOT the case, edit that line and make sure the “Apply onto” drop-down box is set to “This folder, subfolders and files”. This should already be set properly but it is important that you verify this.

9. Click the “OK” button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.

10. Reboot your computer to assure that these changes have taken effect. (no need for this in my case).

11. Re-execute the application and it should now work.

This made it works like a charm !

Reason was that IIS user did not have enough permission on the folders and subfolders ?


SharePoint Oracle UNICODE issue : Chinese Control Characters

June 18, 2014

When you run an English SharePoint version on an English OS. Getting SSRS to deal with Oracle Unicode Characters is a challenge as well !

ORACLE has does not show the Chinese Characters properly in SharePoint.

image

As you can see there is always 1 control Character between each Chinese character.

Oracle and Microsoft are not friends yet Ninja

Solution :

This should work using the CONVERT function between Character Sets. But it did not 😦

Convert between character sets it says, give it a try.

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E','US7ASCII','WE8ISO8859P1') FROM DUAL

By the way you can’t show the Chinese characters on an English OS either using SQLPLus command line.

Because the Character set is defined in the NLS_LANG environment variable

solve it use the UNISTR() and ASCIISTR() functions to display which control characters are in play.

SELECT UNISTR(ASCIISTR(trim(FieldName)) AS Alias FROM ...

Next you can remove them using the REPLACE() function.

SELECT UNISTR(REPLACE(ASCIISTR(trim(FieldName)), ('\FFFD%'),'')) AS Alias FROM ...

If you know a better way, please share here

Tip 1 :

By the way you can’t show the Chinese characters on an English OS either using SQLPLus command line.

Because the Character set is defined in the NLS_LANG environment variable

or using this qry

<br>SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET','NLS_TERRITORY','NLS_LANGUAGE')<br>

Tip 2 :

If you set the NLS_LANG parameter on the client that runs the ODBC using the Oracle driver to

NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

image

It might just work ?!

Tip 3 :

An other option is NOT to use the ODBC connection3 But to use the .NET Oracle provider including the Unicode=True setting

image

Data Source=YourDataSource;Unicode=True

But it say only valid till Oracle 9i

clip_image002

Ps :

There is definetly no UNICODE support if you use the Microsoft Driver for Oracle.

http://support.microsoft.com/kb/244661
… The ODBC Driver for Oracle does not support any of the new Oracle8 data types — Unicode data types, BLOBs, CLOBs, and so on …

Unless you install a later version of the drivers (See MDAC documention)
But then again I read there ara other issues coming your way ? https://community.oracle.com/message/1147185

Work arounds using the ALTER SESSION command http://msdn.microsoft.com/en-us/library/ms714756(v=vs.85).aspx
Or us the .NET version of the MS Driver for Oracle which support UNICODE.

Look here for the MS versions history http://msdn.microsoft.com/en-us/library/ms810810.aspx

Next Solution might be that you tweak the native Oracle Driver to support the code page you are looking for.

This explains it very well ?
http://www.toadworld.com/products/toad-for-oracle/w/toad_for_oracle_wiki/233.unicode-support-in-toad-10.aspx


SSRS shared data source : error ORA-03134

June 18, 2014

This error came up setting up a shared Data Source in SharePoint ?!

ORA-03134: Connections to this server version are no longer supported.

For a mix of older versions of Oracle  databases. You need to pick the most appropriate driver.

Solution : Install a lower version of the Instant Client Libr. (just downgrade to the lowest level of Database version)

The good news:

It is possible to access an Oracle 8 Server with Oracle Instant Client 10 (see Metalink Note 207303.1).
The bad news: There is no Oracle Instant Client version 9. The product was introduced with Oracle 10g.

Connections to Oracle 9.0.1 databases are not supported.
Connecting with a 10.2 Instant Client to server version 9.2.0.4 or higher is supported.

or avoid the problem

Don’t use a shared Data Source but a Report Data Source


Report Builder 3.0 – Oracle Data Source Issue

June 18, 2014

How to solve Publish Reports via Report Builder 3.0 not able to run against the Oracle Data Source.

I had installed the x64 bit Oracle driver on the server, but was unable to get it to work using the Report Builder 3.0

image

When you open the Report builder and add a new Data Source for Oracle you can choose between the :

.NET Framework Data Provider for Oracle or a regular ODBC or OLE DB connection

As you can see the Oracle Data Source supports only till version 9i.

So if you want to use a more recent version of Oracle, you need to use the ODBC selection.

Anyhow the news here is that Report Builder 3.0 will ONLY recognize the 32bit drivers.

Lessons learned :

RB 3.0 Data Sources = x32 bit

SSRS Data Sources = x64 bit

If you publish reports to a SharePoint library make sure you have both version of the Oracle driver installed on the server side by side.

REMARKS:

1. The older Oracle versions have a different Join Types. The ANSI SQL based one, while MS SQL does not have by default.

So you take this in account when write your queries in the report Builder, which gives you an other challenge 🙂

2. Report Builder has a different way to access the Parameter values using a ‘:’ instead of an ‘@’ character in your Query statement

MS SQL =  … WHERE DatePart(year,sub.StartDate) = @Year

ORACLE = …. WHERE  To_Char( StartDate,’yyyy’)  = :Year

 


Oracle DB connection for SharePoint and SSRS

September 5, 2013

This is a series of adventures to took me a week to go through and find all the pitfalls !

Background : SharePoint 2010 Farm running on a Front-end IIS and Back-end MS SQL / SSRS Windows 2008 server.

I had the idea of quickly connecting the SSRS reports using Report Builder 3.0 to an out of domain Oracle DB.

And once the report was done to publish is on SharePoint. Because I had the SSRS running in SharePoint Integrated Mode. 

Since SSRS can connect to MS SQL and Oracle data source, I thought it would be nice to install the Oracle client and settings as well.

This is where the adventure starts ! Smile

Ok. First create a Free registration account on the Oracle web site in order to download the Client libraries.

http://www.oracle.com/technetwork/topics/winsoft-085727.html

image

I had already setup the Oracle INSTANT Client on my Windows 7 x64 bit laptop. So I though this is a piece of cake to do the same on the hosted server. Which was out of my domain being admin ?

Next Question that came to me is should I download the x32 bit or the x64 bit or both ?

Followed by should I install it on the Front-end server or the Back-end server or both ?

Damn so many questions unanswered ?!! I looked all over the place on the internet to no reveal. Sad smile

So let’s do it the hard way then, using trail and error method.

These are the links to the posts on how to solve all issues I encoutered, installing Oracle ODBC connections for SharePoint and SSRS :

How to install the Oracle Instant Client x32 and x64 bit on the same machine side by side.

How to solve the error 206 when installing the Oracle ODBC drivers.

How to solve Publish Reports via Report Builder 3.0 not able to run against the Oracle Data Source.

How to solve Report Builder Parameter Report not working against an Oracle Data Source ‘@’ versus ‘:’

How to Solve the Report Builder Query does not run when published in SharePoint : ORA-03134

SharePoint SSRS UNICODE issue : Solve Chinese Control Characters