Crystal 8.5 report based on SQL 2000 linked to SQL 2005 error

klemm_it_team
klemm_it_team used Ask the Experts™
on
I have a Crystal 8.5 report that references a SQL Server 2000 stored proc for its data. A request was made to add some additional columns to the report. The additional fields are in a SQL Server 2005 database (which is located in a different domain). I created a linked server from my 2000 DB to the 2005 DB. I then created a simple view on the table in the 2005 DB that I need to reference.

I then rewrote my stored proc joining my 2000 table to the view going to the 2005 DB. The stored proc runs fine when I call it from Query Analyzer. When I try to update the CR 8.5 report by 'Verify Database' to reference the updated stored procedure, I get the error: General SQL Server error: Check messages from the SQL Server.

If I upgrade the report to CR version 11, it works fine. But, I do not want to upgrade the report just yet because our user's CR viewers are still using the 8.5 libraries.

If I comment out the part of my stored proc that uses the join to the 2005 DB table then the error goes away.

Is there a way to make this work without having to upgrade the report?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you build a new report from the stored procedure?

mlmcc

Author

Commented:
Good question. I will try that first thing in the am and post the results.

Thanks for the suggestion.

Author

Commented:
I tried building a new CR from scratch and I still got the same error.

Any ideas?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
It may be that CR8.5 can't use SQL2005 evven though you are linking through SQL2000

mlmcc
I changed the datasource to an odbc file dsn and that worked. Not the ideal solution as I now have to distribute the file dsn to everyone who uses the report. Eventually I will upgrade our Crystal Viewers to use the lastest version and then convert the reports.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You might be able to use a system dsn and avoid the need to install the dsn on all machines.

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial