Change the Data Source on multiple reports 1

[CameronMadman]

Hello,

I am currently in the process of migrating a Crystal Reports XI server to a new SQL server. I've successfully imported all the report object into the new Crystal Reports Server installation, and I can see all the scheduled instances.

My problem now is changing the Data Source locations for these reports. All the data sources I want to change are MS SQL Server connections. I can see the ability to change these en-mass using the Schedule Manage tool, however this also changes the Database in use, not just the server. As Crystal Reports calls the connection the server name, when you go to change these you end up overwriting the database name as well.

So my question is, how can I go about changing these reports to point to a new SQL Server name, but the same database name?

Thanks,

Cameron

 

[hilfy]

There are several third-party tools available that may help you with this. Take a look at

http://www.kenhamady.com/bookmarks.html

for a good list to get you started.

Or, if you can program in either .NET or Java, there is a very rich SDK available that you could use to create a program to do this for you. I have sample tutorial code at

http://www.geocities.com/geekgurl7/sample_code.html.

Look for the tutorial at the bottom of the page. This was written for BusinessObjects Enterprise, but the code for connecting to the server and accessing infor about Crystal Reports should work for Crystal Server as well.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[CameronMadman]

Thanks for that Hilfy,

Those tools look interesting I'll look into them, but I doubt I'll be making any purchases as this is just a temporary situation (will be migrating to Reporting Services ASAP!)

I was afraid I'd have to get into the API's to do this. I already use the Java API to schedule reports en-mass so I'm a little familiar with it, the trick will be deciphering the InfoStore fields that hold the info I need to change I think.

 

[hilfy]

You're looking for the .ReportLogons property of the report. For each entry in the collection (I think there's one for each table/query in the report) set UseOriginalDataSource to false and then set the CustomServerName, CustomDatabaseName, CustomUserName, and CustomPassword properties. You can get the original database name from the DatabaseName property and the original user id from the UserID property. The data in the original properties is all read only, that's why you have to go the custom route and once you start using the Custom logon properties, you have to fill them all out - it won't let you use some custom and some original.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[CameronMadman]

Thanks Hilfy!

Now next problem...... So I can change the details of all the reports now, but this does not change the scheduled instances. Looking through the SDK I can't see how to access the scheduled instance of a report (as a report, I can return the objects but it's usually something like CrystalDecisions.Excel or .PDF). I can see in the XIR2 there is an interface specifically for this, but I can't find a way to do it in XI.

Any thoughts?

 

[hilfy]

You can't change them - you have to reschedule them. However, if you use the "reschedule" functionality you will still have the old copy of the report, so you actually have to schedule them from scratch.

This, too, is not hard to do in code. The basic steps are:

1. Find all recurring instances (SI_SCHEDULE_STATUS = 9)
2. Get the SchedulingInfo values.
3. Use the information from step 2 to schedule a new report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[CameronMadman]

Thanks for the quick reply Hilfy.

I've been able to re-schedule these reports, however there seems to be a lot of information that isn't contained in the IInfoObject. I've also brought across the IProcessingInfo properties, but I'm still missing things like re-occurrences, report formats, non-default destinations. I can't see anything within the IInfoObject that would contain this information either.

This does seem odd to me, because looking at the ISchedulingInfo object the destination and re-occurrences should be there.

Unfortunately without bringing across these pieces of information re-scheduling them is reasonably useless in my situation.

Any thoughts on what could be going wrong with the ISchedulingInfo object? Or where the report formats can be obtained (and set)?

 

[CameronMadman]

Ok, so that last message can probably be (mostly) ignored. Turns out I had a typo in my InfoStore query so it wasn't actually returning the Scheduling info.

It looks like it lists the format as a crystal report, even if it is set to something like excel or PDF, speculating, this might be because it has only been scheduled, and has not run yet (and therefore isn't actually a PDF et al yet?)

The destination problem appears to be because there isn't a simple setDestination method, I've got to go through the IDestinationPlugin hoops. But it appears to have worked.

 

[CameronMadman]

One new problem has cropped up that's stumped me now. It's failing any scheduled job now with the error "File ~ce7a061784291a8d9c.rpt not found on File Repository Server." (or some other report name).

Now I see 2 problems here, firstly, well, the file isn't there and the report doesn't actually get e-mailed to anyone, and secondly, it's a .rpt file still.

I had a quick brows in Query Builder on the filed reports and it looks like it's trying to access this .rpt file in the Output FRS folder structure, but in a folder that doesn't exist. When I compare this to a report I manually run, the filename it's looking for is of the extension you run the report with (eg, .xls), the output folder hierarchy also exists.

So right now I'm still concerned about the output file format, and how to ensure it remains the same. I'm also wondering why it's having issues with the output file location as these are new schedules, I'm wondering if the old output folder is stored somewhere that's being copied across (I'm not doing it deliberately).