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

In some cases Verify Database is not sufficient. Stored procedures tend to be "sticky" with Crystal Reports. When adding or updating a stored procedure I usually have to reset the Datasource Location. This is found at Database > Set Datasource Location.

See this answer