Error When Printing Crystal Report That Uses SQL Stored Procedure

Question

I have a Crystal Report created that uses a SQL Server stored procedure as the data source. The stored procedure has one parameter. When the code below runs I get "Database logon failed" at the obj.PrintToPrinter line.

If I remove the stored procedure from the report and replace it with the same tables used in the stored procedure and use the same parameter, no error. The report prints with no issues!

Any help would be appreciated, I'd really like to use stored procedures for our reports. Thanks!

        ReportDocument obj = new ReportDocument();


        string m_rptname = "C:\\Reports\\MyReport.rpt";
        obj.Load(m_rptname);

        obj.SetParameterValue(0, "123456");

        foreach (Table tbl in obj.Database.Tables)
        {
            TableLogOnInfo tli = tbl.LogOnInfo;
            tli.ConnectionInfo.ServerName = "myServer";
            tli.ConnectionInfo.UserID = "myUser";
            tli.ConnectionInfo.Password = "myPass";
            tli.ConnectionInfo.DatabaseName = "myDB";

            tbl.ApplyLogOnInfo(tli);
        }

        //The line below is where the error happens for stored proc only
        obj.PrintToPrinter(2, false, 0, 0);
        obj.Close();
        obj.Dispose();

Answer 1

Here are a couple of things that you may or may not have already tried...

1. In order to isolate the problem to Crystal Reports, have you tried writing a function to execute the procedure with a direct SQL connection? This would allow you to test your credentials and SQL permissions.

2. I don't see anything in your connection settings about Integrated Security, you may want to double check that. Since you are passing a username/password, I am assuming you are NOT using Windows Authentication and may need to Integrated Security = false

3. Does your report have any sub reports? If so, you will need to set the connection info like you did with your tables.

4. Check to see if your data source is out of sync with your database. To do this from the report designer, select Database -> Verify Database.

If these options do not work, let me know and I can do some more digging. Crystal reports are great once you get the "gotchas" out of the way.