SSRS Connectivity Issue // Failed to initialize the AppDomain:ReportServer_MSSQLSERVER_0

I had faced the subjected issue (User was not able to access SSRS) following the installation and configuration of SSMA – SQL Server Migration Assistant, required for the migration and conversion of Oracle Schema into SQL Server. 

Here are the excerpts from the SSRS log file-

Failed to initialize the AppDomain:ReportManager_MSSQLSERVER_0
Could not load file or assembly ‘Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified.
servicecontroller!DefaultDomain!16e4!03/26/2014-09:31:45:: i INFO: RPC Server started. Endpoint name =’ReportingServices$MSRS11.MSSQLSERVER’
appdomainmanager!WindowsService_0!16e4!03/26/2014-09:31:45:: i INFO: Appdomain:2 WindowsService_0 initialized.
appdomainmanager!DefaultDomain!1434!03/26/2014-09:31:54:: i INFO: Appdomain:3 ReportManager_MSSQLSERVER_0-1-130402711139014056 started.
appdomainmanager!DefaultDomain!1434!03/26/2014-09:31:56:: e ERROR: AppDomain ReportManager_MSSQLSERVER_0 failed to start. Error: Could not load file or assembly ‘Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config line 176)
library!DefaultDomain!1434!03/26/2014-09:31:56:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: Failed to create HTTP Runtime, Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: An internal or system error occurred in the HTTP Runtime object for application domain ReportManager_MSSQLSERVER_0. —> System.Web.HttpException: Could not load file or assembly ‘Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config line 176) —> System.Configuration.ConfigurationErrorsException: Could not load file or assembly ‘Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config line 176) —> System.IO.FileNotFoundException: Could not load file or assembly ‘Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342’.

Looking at the log file details, it was clear that the highlighted file was missing and not accessible.

As a workaround, the machine.config file was modified in order to skip the line #176.
Just comment the problematic line and voila, the issue was resolved in my case.

<!– <add name=”OracleWebEventProvider” type=”Oracle.Web.Management.OracleWebEventProvider, Oracle.Web, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342″ connectionStringName=”OraAspNetConString” buffer=”true” bufferMode=”OracleNotification” /> –>

Hope this helps you as well.

Cheers!
Aman Kharbanda
 

Advertisements

SSAS Login Failure Issue // OLE DB or ODBC error:Login failed for user NT Service\MSSQLServerOLAPService

While trying to access a cube or dimension in SQL Server 2012, you might encounter the subjected login failure issue.

This sort of error is surfaced when the connection is not successfully established with the data source.

Error 26 OLE DB error: OLE DB or ODBC error: Login failed for user ‘NT Service\MSSQLServerOLAPService’.; 28000

Point to remember :- SQL Server Analysis Services will use the service account(by default) to pull data from the data source (the database server that hosts the data). If your Analysis Services instance is running under the account ‘NT Service\MSSQLServerOLAPService’, then this account will be used(by default) to read data from the databases and if the account doesn’t have read access, you will get the above error.

Now, there are 2 different ways to resolve this –
1) Grant the account read access to the relational database(s) you are trying to pull data from.
OR
2) Change the SSAS account log on as to Local System account.
Image

Hope this helps!

Happy learning. Cheers!
Aman Kharbanda

 

Microsoft .NET Framework 3.5 installation has failed.

As we all know that .NET Framework 3.5.1 is a mandate pre-requisite for the installation of SQL Server. You might encounter the below error, if you are installing SQL on Windows 2008 or above.
Image

Starting from Windows Server 2008, this requirement is pre-shipped in your OS and there is no additional requirement of a manual installation.
Right Click My Computer, go to manage and you will be welcomed by a screen called – Server Manager.
On the left side of page, there is a Features tab. Go to ‘Add features’ and you will a screen as mentioned below- 
Image

Select the check box, and it will ask you to install it. After installation, proceed with your SQL Server Setup.
Installation should now run without any concerns (At least, not the .Net Framework troublesome issue).

Cheers!
Aman Kharbanda

Cross Platform DB Migration – Oracle to SQL Server

With the increasing overhead of cost saving measures being followed by most organizations, there was a task which included the migration of a database from Oracle to SQL Server.

Key reason was to avoid the licensing costs (As we all know, that Oracle license is much more expensive than of an Enterprise edition of SQL Server).

SSMA or more infamously known as the SQL Server Migration Assistant was used for this Cross Platform Migration task. It automates the migration of most database objects, including stored procedures, functions, packages and triggers.
Additionally, it also provides the migration assessment analysis, schema and SQL statement conversion, data migration as well as migration testing which reduces the risk of your database migration project.

‘SSMA for Oracle v5.2’ is designed to support migration from Oracle 7.3 or later version to all edition of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

There could be some complex PL/SQL statements that can’t be automatically converted by the SSMA. You can run a migration assessment on your Oracle database and determine if your database schema contains such statements. The migration assessment will generate an Assessment Summary Report.

Once you have the details, SSMA will allow you to compare the source schema and target database and from there on, you may choose to run a string of test cases via SSMA Tester Wizard.

While we install SSMA prior to migrate the Oracle schema into SQL Server, we need to have the SSMA Extension Pack installed on the server.
This extension pack will help converting the features not extensively used in SQL Server, and it also installs a tester DB called as ‘sysdb’ in your SQL Server. ‘Sysdb’ contains the tables and stored procedures that are required to migrate data, and the user-defined functions that emulate Oracle system functions.

Another thing to keep in mind is that we have the license key placed on the server, where SSMA will be installed.
License can downloaded freely from Microsoft’s Website.

Once we are done with the SSMA and SSMA Extension Pack Installation, we need to connect to the Oracle/SQL Server instances.

Upon successful login validation, we can get started with the actual migration tasks (Converting the schema, synchronizing the DB and finally, migrating the data).

I will writing about those tasks in my next blog.

Happy learning.

Cheers!
Aman Kharbanda

Describing the session states of sql statements

What is the difference between Running, Runnable & Suspended Query in SQL Server?

Let me take a real time example, and quote it in layman terms-

Suppose you are going to a departmental store. You pick up some groceries and put all the things in a trolley and move towards the bill counter. At the bill counter, there are already 5 customers in queue so you stand after the 5th one. After 10 min, 3 customers are done paying their bills, and your turn should come after 2 customers. At the same time you realize, that you need to purchase another item, so you move ‘out’ of the billing queue and pick up the new item. Now, when you head towards the billing queue, this time the waiting queue is even longer. 8 customers are already standing ahead of you, and your turn is after 8 customers who are waiting to complete their bill so that you can pay your’s.

Bill Counter is here known as the CPU/Processor.
You are known as a ‘Query or SPID’.
Customers: – Other running queries.

Running State: – Those customers who are currently paying their bill at the bill counter.
Runnable State: – Those customer who are waiting for their turn to pay bill.
Suspended State: – Those customers who feel that more things are to be purchased while standing in bill counter queue.

Note: – Query is always executed in FIFO Method.

Happy learning!

Cheers!
Aman Kharbanda