Accessing SQL Database w/Stored Procedure and Parameters

[cfriedberg]

I have a complex report with several sub reports developed outside of VB. It accesses a Stored Procedure in SQL Server 2000. Now I want it to be viewed with the Crystal Report Viewer.

This is the code:

Report.Database.LogOnServer "P2ssql.dll", "mysrv", "MyDataBAse", "MyUserID", "MyPassWord"

CRViewer1.ReportSource = Report
CRViewer1.ViewReport

The dialog box prompting for the date parameter comes up and I can enter the date. After that I get a Crystal Reports: Database Error, saying "General SQL Server error: Check messages from the SQL Server." There seem to be no messages looged into the SQL Server Logs.

 

[skuhlman]

I'm not sure where it comes from, but on my machine there's a C:\WINNT\Crystal\sqlsvr.err file which logs those errors. Try checking that out. I usually verify my SPs through SQL Query Analyzer. If they work through that, but not through crystal, it usually turns out to be a security issue.

 

[rscero]

skulman is right on track here. What you need to determine is where is the report being executed from, your workstation or a server. If the execution happens on a server, then the SQLSVR.err file will be on that box. This may be why your not seeing it on your workstation.

Once you locate the err file, it will give a very good indication of where the problem lies, either in the execute permissions of the stored procedure, or in the parameters being passed in.

If the stored procedure has say 3 parameters, and they do not have defaults set in the stored procedure, be sure your passing all 3 in your call.

 

[cfriedberg]

Thank you for your help. I did not know about the SQLSVR.err file. It was a great help. I also had issues with selecting some of the options in the report since it had been created outside the report, the stored procedure option needed to be de-selected to have the report work properly that did not use it.

It seemed as if I could not make very many adjustments to the options of any kind without something going wrong. In fact, I had to completely rebuild one of the reports before I could get it to work.