Changing the Database Connection Programmatically

[former_member967079]

Hi Guys

We have written a program that uses SAP Crystal Reports. The machine and database that we wrote the report against, the system works perfectly, but when we change the database, we are having a few issues where the system says "Error in File Report_###.rpt: Failed to load database information."

We then proceeded to debug this and picked up something strange.

Here is our code:

TableLogOnInfo tableLogOnInfo = GetSQLTableLogOnInfo(server, DataUtility.DatabaseName, integratedLogin, userID, password);
for (int i = 0; i < report.Database.Tables.Count; i++)
{
    Table table = report.Database.Tables[i];
    table.ApplyLogOnInfo(tableLogOnInfo);
}

        private static TableLogOnInfo GetSQLTableLogOnInfo(string serverName, string databaseName, bool integratedLogin, string userID, string password)
        {
            CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag connectionAttributes = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
            connectionAttributes.Add("Initial Catalog", databaseName);
            connectionAttributes.Add("Data Source", serverName);
            connectionAttributes.Add("Initial Catalog", databaseName);
            connectionAttributes.Add("User ID", userID);
            connectionAttributes.Add("Integrated Security", integratedLogin);


            DbConnectionAttributes attributes = new DbConnectionAttributes();
            attributes.Collection.Add(new NameValuePair2("QE_DatabaseName", databaseName));
            attributes.Collection.Add(new NameValuePair2("QE_LogonProperties", connectionAttributes));
            attributes.Collection.Add(new NameValuePair2("QE_ServerDescription", serverName));
            attributes.Collection.Add(new NameValuePair2("SSO Enabled", integratedLogin));


            ConnectionInfo connectionInfo = GetConnectionInfo(serverName, databaseName, integratedLogin, userID, password);
            connectionInfo.Attributes = attributes;
            connectionInfo.Type = ConnectionInfoType.SQL;


            TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();
            tableLogOnInfo.ConnectionInfo = connectionInfo;
            return tableLogOnInfo;
        }

        public static ConnectionInfo GetConnectionInfo(string serverName, string databasename, bool integratedLogin, string UserID, string passWord)
        {
            ConnectionInfo connectionInfo = new ConnectionInfo();
            connectionInfo.AllowCustomConnection = true;
            connectionInfo.ServerName = serverName;
            connectionInfo.DatabaseName = databasename;
            connectionInfo.IntegratedSecurity = integratedLogin;
            if (!integratedLogin)
            {
                connectionInfo.UserID = UserID;
                connectionInfo.Password = passWord;
            }


            return connectionInfo;
        }

Now, when this code runs, it shows that the integrated login on "tableLogOnInfo" = true but when "ApplyLogOnInfo" to the table, the reportdocument shows the integrated login to false. We also tried to use a SQL user, but using the SA user, and again, the tableLogOnInfo shows a password for the SA user, but the ApplyLogOnInfo shows no password, so we can never change the database on the report.

Please help, as we don't know what more to try.

Thanks in advance

Bradley Wheeler

[former_member11696]

Thanks, I changed the Tag to CR for VS.

When you say changed the database do you mean the structure or to a different database Server?

CR doesn't like it when you change the database structure because we save the info in the RPT file. So you need to verify the report in CR Designer if you changed the structure.

If you are pointing the data source from say a QA DB server to a Production DB make sure the permissions are configured, User has rights to access the tables etc. CR fully supports DB Security.

Are you using SSO, WinAD or ??? to log onto the DB?

If you hard code the values does it get past the error?

Do you have subreports, if so you need to log on for each subreport also.

What happens in CR Designer when you change to the new Server?

Search for this KBA and sample app - 1553921 - Is there a utility that would help in writing database logon code?

Compare the code in the before and after Reports you manually updated the connection info.

Don

[former_member967079]

When you say changed the database do you mean the structure or to a different database Server?

It can be a different database on the same server or a different server but the structures are the same between all the database, we have checkes in our application for this.

If you are pointing the data source from say a QA DB server to a Production DB make sure the permissions are configured, User has rights to access the tables etc. CR fully supports DB Security.

We tried this with dbo access and got the same results.

Are you using SSO, WinAD or ??? to log onto the DB?

We have tried both Windows Active Directory Authentication and SQL Server Authentication and both did not work.

If you hard code the values does it get past the error?

Instead of using the variable "integratedLogin" I tried to hardcoded the value in, and got the same results when it got to the "ApplyLogOnInfo" step. It keeps over riding the integrated security to false. Admittedly, I did not try to hardcode the password for the SQL Authenticated user, but I don't see how this result would be different. At the end of the day, we need to cater for both.

Do you have subreports, if so you need to log on for each subreport also.

I do have reports with subreports as well, but to get it working, I am trying to keep it simple by using a single uncompleted report for now that gets its data from a stored procedure.

Search for this KBA and sample app - 1553921 - Is there a utility that would help in writing database logon code?

I will get my guys to search this and try it, but at the end of the day, we already have our application logging onto the database, so we expect the report to run without logging on again.

Thanks

Bradley Wheeler