General SQL Server Error with Crystal Rpts

[WDfog]

I have the same database on two different servers. The only difference between the two is the data in the tables. (One is production, one is development.) I have several reports that are based on stored procedures. Suddenly, after being in production for a week, the production reports return the error, "General SQL Server Error - Check SQL Server for error messages." There are no errors recorded with the server. I can run the stored procedures within the SQL Server environment and produce results; however, the Crystal reports no longer will. They just produce the aforementioned error. The development copy is still whistling right along. I can find no discrepancies with the production server.

Copying the production version to the development server retains this 'general SQL error.' The development copy is unaffected and continues to work.

Has anyone had any experience with this?

Thanks for you help,
-Wendy [sig][/sig]

 

[Hyde]

What version of SQL are you using? If it is MS SQL 7.0, you might want to try this . . .
Change your database driver an ODBC driver. This worked for me. Sometimes I get the same message and other times I just can't see any data - changing the database driver has always worked. I had gotten this tip from Crystal's tech support. I am using CR8 with MS SQL 7.0.

Suzanne [sig][/sig]

 

[WDfog]

Thank you for your response! Am using SQL 7.0.

After two hours on the phone with Crystal's tech support, I found that when one receives a message saying 'check SQL Server for error messages' it means to check a file in the Crystal reports directory called sqlsvr.err (or something close to that).

My error turned out to be that I did not have my ANSI null setting on one of the databases that I was linking to. Even though the linked server was another SQL Server database, it was still being considered as heterogenous (not homogenous) data that required the ANSI null setting to be on. Go figure.

And to follow up with your suggestion, the ODBC driver does work, but according to the lead programmer on that particular project, its not a feasible option. So thankfully, the database setting change has worked out. [sig][/sig]

 

[MalcolmW]

If you are using SQL Server and stored procedures, a couple of practices that I use that improve performance and reduce bogus errors.
CREATE PROCEDURE ... AS
SET NOCOUNT ON -- stops SQL Server from communicating row counts to the client, which is not a benefit(!)
SET ANSI WARNINGS OFF -- might help your problem
The BOL has more info about both these switches. [sig][/sig]