Business Objects Enterprise Timeout

When running a new, very large report, I encountered this generic error in my Business Objects XI 3.1 SP2 environment using Infoview on IIS.  

“An error has occurred. Request timeout.”

Here are the steps to resolve the issue that I used on my Windows 2003 R2 Application Server.

Go into IIS (Start/Run/ inetmgr)

Right Click on the “default web site”, choose properties.

Bump up “Connection timeout” seconds to 900:

Tab over to Home Directory, configuration and options.  Update ASP Script Timeout to 900 seconds:

Finally, update the .NET settings in the Machine.Config file.  Please backup the current file before you begin!

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG

Add within the system.web section:

Save, exit.  Restart IIS by typing “iisreset” on the DOS command line.

Crystal Reports in JobBOSS

I was recently contracted to do some work for a local engineering and manufacturing firm who uses the JobBOSS application software. They needed some custom Crystal reports to be developed as their existing canned reports were good but not doing what they needed to.

After a few false starts, we were able to get some custom Crystal reports loaded. Here is some advice for developing Crystal reports with JobBOSS.

• Have Crystal Reports at the site. It is expensive to have Crystal as I know, but you will save time in the long run to make the investment and to have it. If you know what you want, you can probably get away with the trial version, but only have 30 days to use it.

• Know what you want reported. I deciphered the SQL from some of the existing reports by looking at the “(reportname)_rpt.htm” files on the JobBOSS server, then created a new report. JobBOSS support can share with you a schema of your tables. From there, decide what you want to report and bring in those tables through the “database expert” in Crystal. Drag and drop the fields into the report. Save it to the “User Defined” section of reporting in JobBOSS.

• Have some basic reporting knowledge. Even with little reporting experience, with dragging and dropping, you will be able to quickly assemble a Crystal report. Snag a Crystal reports book or find one online.

• Call me, I can help! Contact me through my company’s site at http://www.afterhourscoders.com.

In summary, I found it to be pretty easy to develop custom Crystal Reports with JobBOSS. You can also develop these reports to help decision making in your business!

Getting the E-mail name from Windows Active Directory (AD) code

Getting the E-mail name from Windows Active Directory (AD) code.

Recently I needed to get the e-mail address for a person to be outputted on a SSRS report. The user table did not have the actual address and the name would not be possible to concatenate somehow. What did I have? It was only the long Active Directory (AD) data. How can I parse through this to get the name and concatenate on the address for this report? We will want to use T-SQL to extract the “CN” which stands for “Common Names”.

Please walk through this example with me!

Say you have the field “distinguished Name” in your table with:

DistinguishedName
------------------------
CN=joe.smith,OU=factory,OU=Email,DC=companynet,DC=company,DC=com

The code you can use to extract ”joe.smith” and concatenate ‘@company.com” is:

SELECT SUBSTRING((LEFT(U.DistinguishedName, CHARINDEX(',', U.DistinguishedName + ',') -1)),4,LEN((LEFT(U.DistinguishedName, CHARINDEX(',', U.DistinguishedName + ',') -1)))) + '@company.com’ AS "Email Address"

FROM (table)

Your result will equal:

Email Address
-----------------
joe.smith@company.com

Moving SQL Server System Databases to New Drives

It may become necessary to move an already installed SQL Server from one file storage location to another. It is necessary to move the system and other databases. In this case, we are moving the databases from the M:\ drive LUN to three drives, separated by function. This equates to:

• M:\sqlserver\backup to B:\
• M:\sqlserver\data to D:\
• M:\sqlserver\log to L:\

While this is entirely possible, it is necessary to follow a few steps below to cleanly move the SQL Server instance to have storage in a different location. This example uses SQL Server 2008R2, so be sure to take note of the version when clicking through the registry.

• Start the SQL Server Configuration Manager located in the programs section , under the version of SQL Server.
• Shutdown SQL Server Instance.
• In Configuration Tools for SQL Server Instance, put this in for startup parameters:

-dD:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;

-eD:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;

-lL:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

• In the registry:

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer

Set default paths to:

B:\, L:\, D:\

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Replication

Set Working Directory to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL\repldata

o Change default location in registry for Data Root:

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/100/MSSQL10_50.MSSQLSERVER/Setup/

Set SQLDataRoot to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL

Set FullTextDefaultPath to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL\FTData

• Change default location for SQL Agent error file:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent/

Set ErrorLogFile:

M:\sqlserver\data\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT

To

D:\ MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT

• Update Dump Directory to :

D:\MSSQL10_50.MSSQLSERVER\MSSQL

• Check all Directories in the SQL Configuration manager. As an example, Dump Directory should be set to: D:\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\

• Start upSQL Server Instance
• Move Other System Databases:

USE master;

GO

--test change startup script in SQL Config MGR to point to new database/location.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

--MOVE SYSTEM DBs

--MOVE MODEL

USE master;

GO

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'model');

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf' )

ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' )

USE master;

GO

--MOVE MSDB

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'msdb');

ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf' )

ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf' )

--MOVE TEMPDB

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'D:\templog.ldf');

GO

• Restart SQL Server Instance!
• Check locations

--Check

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'model');

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'msdb');

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

• Update Backups/Maintenance Jobs…they all point to the old location.
• Remaining Databases, such as the Report Server databases, can be detached from the old location, copied to new location and attached.

Moving a SQL Server Database with SSIS's Transfer SQL Server Objects Task

On a recent client project, I was asked to move a backup file of a SQL Server 2008 database to another database. On the surface, that is about as simple of a task a SQL Server DBA can be asked to do. There were ten databases. No big deal I thought.

The databases were all for websites and were to be moved from one hosting server to another. The hosts will remain nameless to protect the innocent!

On the destination server, the issue became evident very soon. I created the blank database and I uploaded the .bak file and tried to restore. No dice, it can only restore its own backups, when a database is backed up using the web tool. I then tried creating the blank database then going in through SSMS. This was possible using the connection parameters. I figured I would outsmart the system by restoring the backup file with a replace. It said I had no rights to do so.

I was stuck with no answer. I decided to see if I could import tables somehow or doing some other task. I then though I would try SSIS (SQL Server Integration Services), where I hit pay dirt! The data was more important than the actual database information, so that is what I concentrated on.

SSIS offers a control flow item called “Transfer SQL Server Objects Task.” The obvious question is why not use a “Transfer Database Task”? Simple, the database task is good, but requires destination file source which I did not have. The “Transfer SQL Server Objects Task” was perfect.

First, you choose the source and destination connections. Next, choose the following options to move up the database contents.

I cleared out the connection information, but this is no simpler than any other SQL connection. You can choose more items than my example, but if all you need is the data transferred, this should do it.

So in summary, this task will help you move databases when you do not have the rights to move the data files (attach database) or restore (restore database).

SQL Server Restore Issue

Cannot open backup device 'C:\PATH\*.BAK'.

Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

I added “NETWORK SERVICE” to the file root with the following permissions and the resore worked fine.