SQL Server Reporting Services (SSRS) & .NET

When using Reporting Services on the Server for the first time, you might find that reports won’t run in Visual Studio or on the web.

The error you get is: “Execution of user code in the .NET Framework is disabled. Enable clr enabled configuration option.”

By default .NET Framework is disabled in SQL2005 and SQL2008.

To enable it in 2005:
Explore "SQL Server 2005/Configuration Tools/Surface Area Configuration" in your Start menu.
2. Select "Surface Area Configuration for Features"
3. For the "CLR Integration" option, activate it and save.

To enable it in 2008:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

SSIS and an Oracle OLE DB Connection

Here is an interesting situation I came across working with SSIS and Oracle.

I have a server with operating system Windows 2008 Server Standard Edition and SQL Server 2005(x64 bit) installed on it. I accidently installed the Oracle 10.2 client twice. I promptly removed the second Oracle client and verified I could still connect to Oracle databases in SQLPlus.

I have a SQL Server Integration Services (SSIS) solution that extracts from SQL Server and sends to an Oracle database. After my client install misadventure, when checking the Oracle Provider for OLE DB connection, I noticed something was wrong.

When trying to test connection to get an error, I got:
"oraoledb.oracle.1 provider is not registered"

Yikes, this was working before. How can I fix this and save the 13 packages that will run tonight? Surely, they will all error!

When trying to run the package anyway, I would get this error:
Code: 0xC020801C Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle Provider for OLE DB" failed with error code 0xC0202009

After looking through a myriad of other posts, it appears that the Oracle OraOLEDB10.dll file became unregistered at some point.

The fix:
START/Run then,
regsvr32 C:\oracle\product\10.2.0\client_1\BIN\OraOLEDB10.dll

Yes! The connection worked in the packages and the jobs all ran to success!

Business Objects Administration

My environment is one where users access reports on an IIS (Internet Information Services) based InfoView web application There was a Business Objects user that runs a particularly large report every day. Due to the size and activity on Business Objects lately, the report will repeatedly time out after quite a few tries.

The error says:

"An error has occurred. Request timeout".

This is basically as generic of an error text that you can get. Me and my users were not very happy with these occurrences but one day I found the answer.

When searching the actual Business Objects Support site for the exact error text, I found the attached resolution. It increases the timeout from two to 15 minutes. I tried this in Development and the user no longer got the report to error with the time out.

The details of the change were to :

Update the connection timeout from 120 to 900 seconds for that particular site in the IIS manager.

Then update the machine.config file in C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG. (or wherever or whatever version of .Net you use) as follows:

Locate the following section:

executionTimeout="90"

maxRequestLength="4096"

useFullyQualifiedRedirectUrl="false"

minFreeThreads="8"

minLocalRequestFreeThreads="4"

appRequestQueueLimit="100"

enableVersionHeader="true"

/>

Change executionTimeout="90" to executionTimeout="900".

Restart IIS.

I tested this process while logged in to InfoView in DEV. Whether you run a report or are navigating during the IIS restart, there is no difference in service. Therefore, there is no outage associated. However, I would bounce the Business Objects servers for good measure.

The back out plan is to change the IIS setting back to 120 and backup and restore the machine.config file.

First Post

Welcome to the blog for After-Hours Coders, LLC. We look forward to sharing some of the various tips and tricks we encounter in the field to benefit the greater Information Technology community. We’ll have most entries about SQL Server Database administration, Business Intelligence and other miscellaneous topics.