Posts tagged crystal reports server

Failed to open the connection When Viewing Report On Crystal Server 2011

So you created a report in Crystal Designer 2011 (or whatever other version) and it runs well on your system.  You uploaded it to your Shiny New Crystal Reports Server 2011 (BO 4.0, XI4, whatever you call it) and it doesn’t work.  You can’t view the report because you get an error:

The Problem is that Crystal Designer is 32-bit, and Crystal Server 2011 is 64-bit.

So if you want to use your report on Crystal Server 2011, you need to add the ODBC connection to the server using the \Windows\SysWOW64\odbcad32.exe ODBC Manager Control panel.  After that, your report should run just fine.

Facebook Twitter Email Linkedin Digg Delicious

How to Change Owner for a Crystal Reports Server Report

In Crystal Reports Server 2008 (and Older versions too), Whenever a user gets deleted or reports are imported, and if the original owner does not exist, the reports get automatically assigned to the “Administrator” User

This can become a pain if you already have security setup, where only owners can change reports.

In order to change report owners, you have to use the SDK, but SAP does a poor job even explaining how to do it, which is why im posting my own quick and dirty .NET solution to changing report owner (SI_OWNERID) in Crystal Report Server

The Code at the heart of it all:

– This function will pull the reports from Crystal, and bind itself to the gridview control called gridview1

 

– The next piece of code.  This is a built in Gridview Method, so you have to write your own code to update the Infostore

 
– The Last piece of code is the function I use to retrieve the list of users. Once I have that list of users, I create a datatable and bind it a combo box which displays the user name, but returns the user ID. With that User ID, I set the new SI_OWNERID

Thats pretty much it, Those 3 functions are the heart of my application and should either help you complete your project, or get it started.

Im not a .NET developer, I learned to develop C++, Basic, PHP, Java, etc, so if you find mistakes in my code or better ways to do things, please post a comment.  If you want a full copy of my project, where you can just download it and use it, just Contact Me and I will email you a copy of the entire project.

I dont have a Java Version of my code, but If you are looking for a Java Solution, look at Bob Loblaw’s Code from Forumtopics.com, his bit of code can put you in the right direction

Enjoy!!

Facebook Twitter Email Linkedin Digg Delicious
ccm

How to FailOver Crystal Reports Server 2008

  • It is clearly stated on the SAP website, and I’ll quote:

Clustering, load balancing, and failover are not supported.

Well, that doesn’t mean failover won’t work, its just not supported by SAP.  Well, I have a solution for everyone that works beautifully for me, and yes, i’m currently using this setup in a production environment.

I just want to make 2 points, make sure you have the back up of CMS database and Filestore before trying the following steps.  Also, It’s been verified that the query works for ALL versions of Crystal Report Server / Business Objects Enterprise, so even though these instructions are written for CRS 2008, I’m pretty sure you can use this method for the other versions as well. If you do, and it worked for you, please drop a note on the comments section

OK, let’s get going!!

Software

  • Crystal Reports Server 2008 v1 with SP2

Servers

  • Server1 (Windows 2008 / 64bit / 2GB / VM)
  • Server2 (Windows 2008 / 64bit / 2GB / VM)
  • SQL 2005 Back-end
  • IIS7 – 64bit

Before Starting

  • I’m assuming this is a new installation. If you are trying to do this to an existing database it could be possible, but I’m not going to try to tackle that issue in this document
  • I’m assuming this install is being done in a Windows Server, with a MSSQL backend (Not MySQL, not SLQExpress) (Only Step 7 would be different, so if you can rewrite the query for you specific DB then I think this method will work for you)
  • I created one DNS alias, that points to the IP of server1, and it case the server fails, I can change the Alias to the IP of server2 (this way I don’t have to have my clients/users point to a different name or IP, its all done on the back-end)
  • When I installed the Crystal Reports Server, I installed it into its own “Site” and “Application Pool” in IIS7
  • I bound the New Site to the DNS Alias I created above, so the only way to reach the site is by the alias. The way I tested on each server, I added the alias and the loopback IP (127.0.0.1) to the host file on each server.
  • I setup a sync job using Robocopy, so it would keep the “Filestore” folder of the 2 Crystal Servers in sync (%Program Files%\Business Objects\BusinessObjects Enterprise $version$\FileStore)

Prepping the Failover Pair

1) Have both Crystal Reports Servers fully Installed and Service Packed

– Both Pointing to the same database or separate ones, you get errors if you install it to the same server, but that doesn’t matter, since we will be wiping out the configuration,
– Also, It doesn’t matter which order you install the Crystal Servers, since we will wipe out the database and refresh the configuration while setting up the Fail-over pair

2) Launch the CCM (Central Configuration Manager), and Shut down the SIA (Server Intelligence Agent) on both Servers

3) Update the SIA DB settings on both servers (Right Click the SIA, click “Properties” -> Click the “Configuration” Tab-> under the heading “CMS System Database Configuration” click the “Specify” button -> Choose Update Data Source Settings)

4) From Server1, right click on the SIA and Select Properties->Configuration->CMS System Database Configuration-> Specify -> Recreate the current Data Source.  This will wipe our your current CR database and add fresh records.

5) Once this is complete, it will prompt you for CMS, just Hit Ok, after that you will get a window that reads “Adding Servers”, ****It is very important that you allow this process to complete****

6) You will get a prompt titled “Central Configuration Manager with Some Instructions, just hit OK and then start the SIA

7) Wait a minute or two and then hit the “Manage Server” button , it should prompt you for credentials, and since the database has been wiped out, the Administrator password should be blank so just hit “Connect”

8) By now you should see all the services running and enabled. The one we care about the most here is Server1.CentralManagementServer, because we will end up deleting it from the database.

9) Stop the SIA, Fire up your Favorite SQL admin tool, and get ready to delete the Entry for the CMS by running the SQL Query bellow

You can also create a view, which will help in the futre for other “failovers”, I recommend this

10 ) Now after you run the query, find the row with ObjectName = SERVER1.CENTRALMANAGEMENTSERVER, and since we just recreated the Database from SERVER1, the Entry in the database will be SERVER1.CENTRALMANAGEMENTSERVER. Take note of the ObjectID

<img class="aligncenter size-full wp-image-514" title="query" src="http://pinchii.com/home/wp-content/uploads/2010/09/query.png" alt="" width="688" height="364" />

11) Now lets delete the CMS row from the Database, so now run a delete command
Delete from cms_infoobject6 where objectID=”CMS_ID”
********** DO NOT SKIP THIS STEP ****************

12) We are done with Server1, lets move on to adding Server2 to the Fail-over Pair.
First by launching the CCM, then locate the SIA (Server2) service ****DON’T START IT****

13) Click the “Add Server Intelligence Agent” button (server icon with the blue plus)

14) You will get the Add Server Intelligence Wizard, click Next
Name, Choose Server2 (Same Name as the SIA that Already Exists)
Port 6410
Pick “Create default servers on the new node”
Check “Recreate Server Intelligence Agent on local host……”

15) It will then take you to the CMS Configuration, for the New CMS port use port 6400, also specify one more time the DSN connection to the Crystal Server Database, the same database that Server1 is pointing to (uncheck auditing database source)

16) Just hit next until you get to the end, after that you will get a window that reads “Adding Servers”, ****It is very important that you allow this process to complete****

17) Once that is complete, start the SIA (Server2) Service, and wait for it to finish loading

18) Click on the Manage Servers Icon (server with a check mark), when prompted for credentials, just hit ok. You will now see that you have duplicate processes for both of your servers except for the CentralManagementServer, there should only be one named Server2.CentralManagementServer

This is pretty much it; you now have 2 servers that can be failed over to each other. If Server1 is your primary server, then use the Fail-back instructions bellow to get back from Server2.  With these instructions you can probably add more then 2 servers, I would guess you could probably put up to 5 servers or more and have them all as failover servers, but if you need more then 2 servers to fail over, you should probably be talking to SAP / Business Objects about a clustering solution

Failing Over

  1. So we assume that server1 died. It won’t power on, etc. (This means the SIA on server1 is down, obviously)
  2. Go to your SQL Admin tool, run the Query to view the CMS ObjectNames and Delete the Row that has the CMS information (Steps 7, 8 and 9 above)
    If you created the view:
    SELECT * FROM [CMS_ObjectNames6] WHERE (ObjectName LIKE '%CENTRALMANAGEMENTSERVER') OR (ObjectName LIKE '%CMS')
  3. Start up the SIA service on your failover server, give it a few minutes
  4. Update your DNS Alias to the Failover server, and you are good to go

Fail back

  1. In order to fail back to the original server, stop the SIA on your backup server
  2. Go to your SQL Admin tool, run the Query to view the CMS ObjectNames and Delete the Row that has the CMS information (Steps 7, 8 and 9 above)
    If you created the view:
    SELECT * FROM [CMS_ObjectNames6] WHERE (ObjectName LIKE '%CENTRALMANAGEMENTSERVER') OR (ObjectName LIKE '%CMS')
  3. Start up the SIA service on your failover server, give it a few minutes
  4. Update your DNS Alias to the Primary server, and you are good to go

UPDATE:

It seems that you can skip step 2 of the fail-over and the fail-back procedure.  As long as you follow the “setting up the fail-over pair” instructions, you just need to make sure that only One SIA is running at a time, if both are running at the same time, the SIA will start, but your CMS services wont, and you won’t be able to use either.

Facebook Twitter Email Linkedin Digg Delicious

Decoding the Crystal Reports Server 2008 Database – CMS_InfoObjects6

I’ve been looking for a way to run some sort of audit or just a peak at what’s included in the Crystal Reports Server 2008 Database.  This database controls all the aspects of Crystal Reports Server including the parameters that are used to launch the WACS server, SIA, and the CMS (If you use CRS2008 you know what these terms mean, otherwise you wouldnt be looking at this blog right now)

So, in order to run some sort of audit on the database, you have to go through and look at the CMS_InfoObjects6 table.  This table contains all the information on CRS2008 including Nodes, Folders and Reports.

If you look at the database table itself you notice that alot of the fields are in “Binary Data” format which makes it almost impossible to look at, well, almost….

Here is a nice query, that will help you get started in understanding and using the CMS_InfoObjects6 database

There is really no real magic to this query, and nothing really special.  It tells you what the object names are, and converts the lastmodifydate  from binary to human readable format.  I have yet to figure out how to decrypt the SI_GUID, SI_CUID, SI_RUID and the Properties Field, but I will leave that for another day since the query above gives me the information I was looking for

And before I forget, I have to give credit to Andu over at the BOB forums his post is basically where this query comes from

Facebook Twitter Email Linkedin Digg Delicious
fixing infoview

Error 80040154 when using .Net Infoview

So I recently had to install the latest and greatest Crystal Reports Server 2008 v1 on Windows 2008 SP2 64-bit.

I followed the install guide from SAP and thought that all went well until I tried to run the .NET infoview.

as soon as I did, it gave me this error:

Retrieving the COM class factory for component with CLSID {E063B04A-CB8B-460E-99D0-F7D8FA2FAAA2} failed due to the following error: 80040154.                                                                                                               
                                                                                                                                   
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.                                                                   
                                                                                                                                   
Exception Details:
System.Runtime.InteropServices.COMException:                                                                    
Retrieving the COM class factory for component with CLSID {E063B04A-CB8B-460E-99D0-F7D8FA2FAAA2} failed due to the following error: 80040154.                                                                                                                          
                                                                                                                                   
Source Error:                                                                                                                           
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of                                                              
the exception can be identified using the exception stack trace below.
             
Stack Trace:                                                                                                                  
[COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E063B04A-CB8B-460E-99D0-F7D8FA2FAAA2} failed due to the following error: 80040154.]              
                                                                                                                                   
CrystalDecisions.Enterprise.SessionMgr..ctor() +66                                                        
BusinessObjects.Enterprise.Infoview.Common.Utilities.GetInstalledLanguages() +80          
BusinessObjects.Enterprise.Infoview.Common.Utilities..cctor() +83
                                                      
[TypeInitializationException: The type initializer for ‘BusinessObjects.Enterprise.Infoview.Common.Utilities’ threw an exception.]                                                                                            
BusinessObjects.Enterprise.Infoview.Global.Application_Error(Object sender, EventArgs e) +619
System.EventHandler.Invoke(Object sender, EventArgs e) +0                                    
System.Web.HttpApplication.RaiseOnError() +174
 

Following their Knowledge base articles 1310290* and 1487760* is supposed to fix the problem, BUT…..

It just never worked for me no matter what, then something in note 1487760 caught my eye:

By default, IIS6 or IIS7 on 64-bit Windows run with 64-bit W3WP.EXE processes, but as .Net Infoview is implemented through 32-bit ISAPI DLLs, the W3WP.EXE processes for IIS7 cannot load them

So, I used my trusty friend Process Monitor from SysInternals / Microsoft and found that when W3WP.EXE loaded Infoview (you can see this by monitoring while doing an IISreset), it loaded it using the 64-bit .NET framework even though I had specified in the AppPool to use 32-bit

So I did a little poking around and found that the “Application” under the parent Crystal Website I created were not inheriting the Application Pool from the main site, they were all set to Classic .NET AppPool.  After going in, and manually switching them all to my CrystalReports2008 Application pool, Infoview started working again.

So there you have it, if you are running into this issue, now you know how to fix it, or at least what to look for.

I will be making another post later on how to install Crystal Reports Server 2008 v1 on Windows 2008

* those KB’s probably require you to login as a SAP support user, which I am, so I can view them but you may not be able to.  In my installation intructions, I will make sure I include the instructions from the KB’s

Facebook Twitter Email Linkedin Digg Delicious
Go to Top