Posts tagged crystal reports

noprinter

Error Adding Reports To Crystal Reports Server

So I was Trying to add reports to Crystal Reports Server through the CMC, and I was getting an error that was not letting me upload the reports.  Adding the report via InfoView did not work either, InfoView only gave you a “time out” error but I suspect in the background, it was the same as the error I was getting through the CMC

The Error Reads:

So of course, I check out the SAP support forums, and most questions, if not all questions that I found went unanswered and unresolved.  The SAP Notes (The Paid Support) were no help either.

After much troubleshooting, this is what I came up with to fix the issue:

  • Open up the report in Crystal Reports 
  • From the Menu pick File -> Page Setup
  • Select the check box that says “No Printer (Optimized for screen display)”

And that’s all there is to it, once you do that, “reportadd.exe” wont error out anymore.  I guess this is some issue with the Crystal Report File format, but whatever the case, this is a quick and painless fix for me.

 

Facebook Twitter Email Linkedin Digg Delicious
CrystalAD1

Query Active Directory With Crystal Reports

Thought i’d share how to use Crystal Reports to Query AD.  Not only can you query AD, but you can combine it with SQL to write some nifty little reports.

Here are the steps:

  1. Create a Blank Report
  2. When prompted to create a connection, choose OLE DB (ADO)
  3. Select OLE DB Provider for Microsoft Directory Services
  4. Under Data Source, enter “LDAP://domain.com/OU=SOMEOU, check “Integrated Security”

    Domain = the domain for your company (“mycompany.com, mycompany.local, whatever)
    OU = This is the OU you want to start in, I like to use it because it makes queries faster, so usually “Users” or “Computers”, depending on what you are looking up
    Integrated Security = Why Integrated? Well, Crystal ONLY supports DB Logins, what that means is that Crystal will only prompt / save you for DB passwords, but for LDAP passwords, it wont, it actually leaves it blank, so the report will work the first time, but if you try to run it again, it wont work, and you will end up locking your AD account
  5. Under Advanced Information just leave it as-is
  6. Now the connection comes up, but there are no tables.  You have to create a custom command, so double click “Add Command”
  7. Add the query command to the window that pops up.  Almost all LDAP fields are queryable
  8. Once you hit OK, the command will appear as a table called “command”. (You can rename this to whatever you want)

 

From this point on, you just Hit OK, and all the fields that were “Selected” in the command window will show up in Crystal, and you can use them to write your report.

I hope this helps someone, please feel free to leave a comment if you like.

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
Go to Top