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?