Crystal Reports and Azure - Stored Procedure trouble

Look at the stored procedures in the current environment, they need to be added to the azure based DB.
The error you have is the table is does not exist.
The table 'prReleaseMarkup;1' could not be found.


Look on the current environment the definition of this table, view....

Hi Arnold, The stored procedure is there.  I can see it through SQL Management Studio and the report sees it when I do a print preview using the ODBC 13 drivers.  
The report doesn't see it when using the data connections through our reporting service.

Double check, confirm the odbc connectio. Defined in the report is the one used.
Try to explore data in the report using crystal reports.

The message you posted points to a missing table.

Are there other reports that work, or none of the reports work?

Yes, I'm sure the stored procedure is there.  The message of a missing table is the error message that Crystal Reports gives because it can't see the Stored Proc.  
As I tried to lay out in my original post, there seems to be only one connection type that allows Crystal to see Stored Procedures on Azure, and that's the ODBC 13.  When I do a print preview using that ODBC connection, everything works fine. The report finds the stored procedures and fully generates.

Unfortunately, it seems that my custom report service isn't using that ODBC connection type. It's probably using an ADO.Net connection.

What I'd like to know is - are there any code changes or settings that we can change in our custom report service to change how it's connecting to SQL Server on Azure to allow it to see Stored procedures?

unable to see a table and unable to run a stored procedure

can you confirm that the user/credentials under which crytal reports accesses the DB has rights to run the SP/access the tables.?

Yes, I can confirm that they do have access.  Next thing we are going to try is to change our report application to have it use the ODBC connection that works instead of ado.net

Changing code to ODBC connection is still causing issues.  From my developer:

I pulled the relevant code into a class that can be sent for review.  The issue we are seeing is an error that occurs when we set the log on info for a subreport when the table references a stored procedure instead of a view.  I added a comment above that line in the code, and tried to add as much additional information as I thought may be helpful.  Of course I may have missed something, so let me know if I can add more detail.

using System;
using CrystalDecisions.Shared; //CrystalDecisions.Shared.dll - Runtime Version v2.0.50727
using CrystalDecisions.CrystalReports.Engine; //CrystalDecisions.CrystalReports.Engine.dll - Runtime Version v2.0.50727

namespace CrystalAutomator
{
    class TestCode
    {
        protected bool ConnectDatabase(ReportDocument report, string dbDll, string dsnName, string dbName, string dbUserId, string dbPassword, out string errorMessages)
        {
            errorMessages = "";

            //Assert assumptions
            if (null == report)
            {
                errorMessages = "Report is not initialized.";
            }
            else
            {
                try
                {
                    TableLogOnInfo logonInfo;

                    // Set the logon information for each table.
                    foreach (Table table in report.Database.Tables)
                    {
                        // Get the TableLogOnInfo object.
                        logonInfo = table.LogOnInfo;
                        // Set the server or ODBC data source name, database name, user ID, and password.
                        logonInfo.ConnectionInfo.ServerName = dsnName;
                        logonInfo.ConnectionInfo.DatabaseName = dbName;
                        logonInfo.ConnectionInfo.UserID = dbUserId;
                        logonInfo.ConnectionInfo.Password = dbPassword;

                        // Apply the connection information to the table.
                        table.ApplyLogOnInfo(logonInfo);
                        table.Location = table.Location;
                    }

                    // Now set logon information for each of the sub reports
                    foreach (Section crSection in report.ReportDefinition.Sections)
                    {
                        foreach (ReportObject crObject in crSection.ReportObjects)
                        {
                            if (crObject.Kind == ReportObjectKind.SubreportObject)
                            {
                                var crSubReport = (SubreportObject)crObject;

                                var crSubRepDoc = crSubReport.OpenSubreport(crSubReport.SubreportName);

                                // Set the logon information for each table.
                                foreach (Table table in crSubRepDoc.Database.Tables)
                                {

                                    // Get the TableLogOnInfo object.
                                    logonInfo = table.LogOnInfo;
                                    // Set the server or ODBC data source name, database name, user ID, and password.
                                    logonInfo.ConnectionInfo.ServerName = dsnName;
                                    logonInfo.ConnectionInfo.DatabaseName = dbName;
                                    logonInfo.ConnectionInfo.UserID = dbUserId;
                                    logonInfo.ConnectionInfo.Password = dbPassword;

                                    // Apply the connection information to the table.
//This next line is where we see an error when the table references a stored procedure instead of a view                          
                                    table.ApplyLogOnInfo(logonInfo);

                                    table.Location = table.Location;
                                }
                            }
                        }
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    errorMessages = "Could not connect to the database. \n\nDetails:  " + ex;
                }
            }
            return false;
        }
    }
}

Moved from Azure SQL as a service to an Azure VM with SQL on it. That fixed the issue.