Scheduling a Report in Crystal Report Server XI with ODBC connection

[tubbers]

Hi, I'm using Crystal Report Server XI on a NT machine. I don't have very much experience using ODBC connections so this may be a simple fix but I certainly haven't figured it out.

The problem:

I was recently giving a user id and password so that I could create a report connecting to our AS/400 iSeries database. On my computer which has Crystal Reports XI installed, I created a System DSN to connect via ODBC and then created the report. It successfully runs and works exactly how I needed it to.

When I uploaded the report to the Crystal Reports Server, it fails at every opportunity. Here are some of the settings I have put in place:

Creating the System DSN
Control Panel -> Administrative Tools -> System DSN
1. Click Add
2. Select Data Source: iSeries Access ODBC Driver
3. Click Finish

System DSN Properties
General Tab
1. Data Source Name: AS400 Crystal Reports
2. Description: leave as is
3. System: ip address
4. Click Connection Options
a) Default User ID: Use iSeries Navigator Default
b) Signon Dialog Prompting: Never prompt for SQLConnect
c) Security: Do not use Secure Sockets Layer (SSL)

Server Tab
1. SQL Default Library: BSERVER
2. Click Advanced
a) Commit Mode: Commit Immediate (*None)

Packages Tab
1. Package Library: BSERVER

Translation Tab
1. Select Convert binary data CCSID 65535 to text


I used this to create the System DSN on my computer as well as on the box where Crystal Reports Server XI is loaded.

Once I've uploaded the report to the CR Server, I use these settings under the Process -> Database tab:

I then uploaded the Crystal Report to the server and set the following Database Process settings:

Data Source(s): Crystal iSeries.dsn

Use Custom Database Logon Information:
Specifiy a custom driver: AS400 Crystal Reports
Server: Crystal iSeries.dsn
Database: B10B4F5B
User: my user name
Password: *********

Table Prefix: B10B4F5B.BSERVER.
Use default table prefix: Yes
When Viewing the report: Use same database logon as when report is run

I get this error when I run the report via the scheduler:

Status: Failed 
Printer: The instance is not printed. 
External Destination: Mail the instance to " me@where.com " with a subject of " AS400 Allergies & Reactions ". 
Data Refresh Settings:  Use the server defaults. 
Start Time: 11/22/06 9:20:54 AM 
End Time: 11/22/06 9:20:56 AM 
Server Used: ntserver.reportjobserver 
[COLOR=blue]Error Message: Error in File D:\Program Files\Business Objects\BusinessObjects Enterprise 11\Data\procSched\ntserver.reportjobserver\~tmp177c48acf30c1c5e.rpt: Failed to load database information. [/color]

I've been working with a Business Objects technical support for two weeks now. Their only suggestion so far is to restart all the services on the server with a Network Administrator account. That didn't make any difference.

I would really appreciate if anyone could help me with this problem. Sorry for the really long post. Hopefully, I got all the important information in.

 

[gshadz37]

Do you have Crystal Reports installed on the server? If so, open the report in CR from the Enterprise folders and run it. Does it fail? If so, what's the error message?

 

[BobSuruncle]

Installation of Crystal Reports on the server is a great troubleshooting tool.
However, be VERY careful if you're dealing with XI Release 2.

If you install Crystal Reports XI Release 1 on a server that is running Crystal Reports Server XI Release 2 (or the full version of BOE XI R2) then you'll have problems viewing reports. Make sure that you get Crystal Reports XI R2 from the BOBJ download area.



Bob Suruncle

 

[tubbers]

I'll have to see if the IT guys will give me access to the server to do this.

I'm pretty sure that both Crystal Reports and Crystal Reports Server are Release 1 but I will double check before I start installing and testing.

An added thought, we're sharing this server box with another program which has Crystal Reports built into the program (Ascend-HI). Could this be a problem? Any report which connects via OLE ADO has been working perfectly and able to be scheduled. It seems to be directly related to the ODBC connection.

I'll let you know what happens once I get access to the server box.

 

[BobSuruncle]

Were the accounts that you used to start the Report Job Server and the Page Server administrators on THAT box?

Bob Suruncle

 

[tubbers]

Yes, I am assuming so.

This is what the IT guy who set up Crystal Report Server said earlier this week:

Crystal was installed using the defaults and there are 4 services running:

Crystal Reports Cache Server
Crystal Reports Job Server
Crystal Reports Page Server
Destination Job Server

All of these services use the local system account to run.

Based on a suggestion from the Business Objects technical support guy, we stopped all the services and logged back in under a network administrator account. Not only did that not resolve the issue, it also caused all of my other reports which were scheduled and working perfectly to get messed up visually. We switched back to using a local system account for all the services we have running.

 

[tubbers]

Hi, I finally got access to our server box and installed Crystal Reports XI (R1).

I ran the report I had created on my local machine with a local system DSN and got the following error:

Logon Failed. Details: HY000:General error: Invalid file dsn 'Crystal iSeries.dsn' [Database Vendor Code: 556]

I then created a report on the server using the system dsn that was created on the server and was able to run it successfully. I uploaded it to Crystal Reports Server and was able to schedule the report successfully as well.

Question, is it not possible to create a report on a local machine and then deploy it without having to recreate it on the server when using an ODBC connection? Is there an easy way to migrate reports from a local machine to the server?

Thanks for your help.