Issue with MS SQL "sys" schema tables

[Former Member]

Hi,

we have an issue with one of our reports (CR 2008, .NET 2.0, C#). The report references a table called "Events", which is present in dbo.Events and sys.events on the SQL server. The report works fine in the designer, but when showing it in our own application (and changing the database connection infos to a different SQL server) the following error occurs (translated):

---------------------------
Crystal Report Windows Forms Viewer
---------------------------
This field name is unknown.

Details: errorKind

Error in file ""Events-Tabelle {1CAFDF4D-625B-4B1B-AFEB-CFF0450DD78E}.rpt"":

Error in record selection formula:

'{Events.cID} = {?Pm-Tasks.lEventID}'

This field name is unknown

Details: errorKind.
---------------------------
OK
---------------------------

Apparently, it accesses the name in the wrong schema. How can we prevent that?
I use the following code to change the database connection to a new SQL server name (which works fine except in this case):

DbConnectionAttributes dbAttributes = new DbConnectionAttributes();

dbAttributes.Collection.Set("Auto Translate", "-1");

dbAttributes.Collection.Set("Connect Timeout", "15");

dbAttributes.Collection.Set("General Timeout", "0");

dbAttributes.Collection.Set("Integrated Security", false);

dbAttributes.Collection.Set("Locale Identifier", "5129");

dbAttributes.Collection.Set("OLE DB Services", "-5");

dbAttributes.Collection.Set("Provider", "SQLOLEDB");

dbAttributes.Collection.Set("Tag with column collation when possible", "0");

dbAttributes.Collection.Set("Use DSN Default Properties", false);

dbAttributes.Collection.Set("Use Encryption for Data", "0");

dbAttributes.Collection.Set("Data Source", serverName);

dbAttributes.Collection.Set("Initial Catalog", dbName);

ConnectionInfo connectionInfo = new ConnectionInfo();

connectionInfo.DatabaseName = dbName;

connectionInfo.ServerName = serverName;

connectionInfo.UserID = "...";

connectionInfo.Password = "...";

connectionInfo.Attributes.Collection.Set("Database DLL", "crdb_ado.dll");

connectionInfo.Attributes.Collection.Set("QE_DatabaseName", dbName);

connectionInfo.Attributes.Collection.Set("QE_DatabaseType", "OLE DB (ADO)");

connectionInfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);

connectionInfo.Attributes.Collection.Set("QE_ServerDescription", serverName);

connectionInfo.Attributes.Collection.Set("SSO Enabled", false);

connectionInfo.LogonProperties = dbAttributes.Collection;

table.LogOnInfo.ConnectionInfo = connectionInfo;

table.ApplyLogOnInfo(table.LogOnInfo);

[Former Member · Accepted solution]

Use a multi part name

a table/view is

Database.Schema.table

if you are jsut using events each login could use a different schema

so use dbo.Events ( or sys.events) or even

database.dbo.events

BTW it's bad practice to use table name like "events" on for this reason.

[Former Member · Accepted solution]

Hi,

thanks to both of you for your comments.

> Use a multi part name a table/view is Database.Schema.table

I know that "dbo" needs to be prefixed. The problem is just that it doesn't work. I'd need to use "table.Location = dbName + ".dbo." + table.Location" to adjust the table location, unfortunately, it throws an error "cannot open connection" in this case. And that is after I called ApplyLogOnInfo() and table.TestConnectivity() returns true.

> BTW it's bad practice to use table name like "events" on for this reason.

There are quite a lot of tables in "sys.*", I don't think it's reasonable not to use any of these names. In particular since you should always have the possibility to use "dbo" only (except CR so far).

> If you are not trying to alter that parameter

I'm not doing that in code. This fragment is part of the report I process only to change the database connection.

> indicates you did not set the log on info in code for your subreports also.

Unfortunately, I'm setting the connection infos for both for the main and subreport.

[former_member11696]

Hi Florian,

Moved to .NET SDK forums.

'{Events.cID} = {?Pm-Tasks.lEventID}' indicates you have a subreport. Do not ever try to use the subreport parameter link directly. The one you can and should use will be in the Parameter collection, CR will handle and parameters that begin with "?PM-"

If you are not trying to alter that parameter then the error indicates you did not set the log on info in code for your subreports also.

Something like this, there are other ways to log, use your same method as you do in the main report object for each subreport:

CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects;

CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject;

CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument;

CrystalDecisions.CrystalReports.Engine.Database crDatabase;

CrystalDecisions.CrystalReports.Engine.Tables crTables;

TableLogOnInfo crTableLogOnInfo;

CrystalDecisions.Shared.ConnectionInfo crConnectioninfo = new CrystalDecisions.Shared.ConnectionInfo();

//pass the necessary parameters to the connectionInfo object

crConnectioninfo.ServerName = "192.168.43.128";

crConnectioninfo.UserID = "sb";

crConnectioninfo.Password = "pw";

crConnectioninfo.DatabaseName = "xtreme";

//set up the database and tables objects to refer to the current report

crDatabase = rpt.Database;

crTables = crDatabase.Tables;

//loop through all the tables and pass in the connection info

foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)

{

    crTableLogOnInfo = crTable.LogOnInfo;

    crTableLogOnInfo.ConnectionInfo = crConnectioninfo;

    crTable.ApplyLogOnInfo(crTableLogOnInfo);

}

//set the crSections object to the current report's sections

CrystalDecisions.CrystalReports.Engine.Sections crSections = rpt.ReportDefinition.Sections;

//loop through all the sections to find all the report objects

foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)

{

    crReportObjects = crSection.ReportObjects;

    //loop through all the report objects to find all the subreports

    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)

    {

        if (crReportObject.Kind == ReportObjectKind.SubreportObject)

        {

            //you will need to typecast the reportobject to a subreport

            //object once you find it

            crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;

            //open the subreport object

            crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

            //set the database and tables objects to work with the subreport

            crDatabase = crSubreportDocument.Database;

            crTables = crDatabase.Tables;

            //loop through all the tables in the subreport and

            //set up the connection info and apply it to the tables

            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)

            {

                crConnectioninfo.ServerName = "192.168.43.128";

                crConnectioninfo.UserID = "sb";

                crConnectioninfo.Password = "pw";

                crConnectioninfo.DatabaseName = "xtreme";

                crTableLogOnInfo = crTable.LogOnInfo;

                crTableLogOnInfo.ConnectionInfo = crConnectioninfo;

                crTable.ApplyLogOnInfo(crTableLogOnInfo);

            }

        }

    }

}

Don

[Former Member]

Is there no solution to the issue? Do we need to open a paid support case?

[Former Member]

table.Location = dbName + ".dbo." + table.Location

come up with error "cannot open connection"

I would look to see what table.location is after this update and try to connect using the multipart name

with regards to not using sys.table names, you could prefix all table names UT_weroifwoeji

I assume you are already doing this for Stored procs and views.

[Former Member]

> I would look to see what table.location is after this update and try to connect using the multipart name

After this line of code table.Location remains the same.

> with regards to not using sys.table names, you could prefix all table names UT_weroifwoeji

Renaming files is out of the question currently.

[Former Member]

Yes renameing is more of a goal, it's just a way of always getting round these issues.

I can't find how you set the schema in crystal.

however there is a plan B

the account you are using ( either AD or SQL) make it use the dbo schema by default I assume it's using sys as default.

[Former Member]

I thought this as well. I believed that if I set the default schema for this particular user to "dbo" (it wasn't "sys" btw but in fact one that does not exist at all), it would work. It does not, however. Same error.

[Former Member]

"in fact one that does not exist at all"

set up a new user account ( SQL or AD) setup access on SQL as needed and use this account for CR

get the "SQL command" that CR issues and logon with account to SSMS and try to exec.

[Former Member]

Using a different new SQL user causes the same issue.

I also used the SQL profiler to check the statements being used. The only statement the profiler logs that happens in the affected DB, is the following statement:

SELECT	Events.sCalcLabel, DefTaskTypes.sName, Tasks.dzCompleteUntil, Tasks.lPriority, Tasks.lTaskFor, Tasks.dzRemindAt, Tasks.lSendUndoneNotificationTo, ...
FROM	Tasks AS Tasks INNER JOIN
	Events AS Events ON Tasks.lEventID = Events.cID INNER JOIN
	DefTaskTypes AS DefTaskTypes ON Tasks.eTaskType = DefTaskTypes.eTaskType
WHERE	(Tasks.cID = 10)

It works fine when executed manually. Most likely, this statement is not actually the cause of the error, but a later statement (in a subreport I suppose). The SQL profiler doesn't generate any information on that though.

[Former Member]

take a copy and delete all subreports see if there is still an issue