Troubleshooting

Failed to enable constraints error

In most cases, the schema definitions in a database must be read in order to determine the types of data to be retrieved for reporting. There may be instances however, when multiple tables are being accessed which have conflicting schema definitions. In such cases, you can tick the check box to ignore the schema definitions stored for those tables and allow Pitram Portal to determine data types using its own in-built logic.

The Ignore schema option may also need to be set in order to resolve a known issue when upgrading to Microsoft SQL 2005 serve (and above). If the following error is reported for one or more existing commands:

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

at System.Data.DataSet.EnableConstraints()

To resolve, add the following line into the command body:

<m_ignoreSchema>true</m_ignoreSchema>

<?xm version="1.0" encoding="utf-8"?>

<PITRAMCommandDefinition xmlns:xsd="http://www.w3.org/2001/XMLScema" xml

<m_commandName>AvailabilityByPeriod</m_commandName>

<m_ignoreSchema>true</m_ignoreSchema>

<m_queryTemplate>

<![CDATA[

...

Query does not compile

If you receive a compile error when running a report, check your query for correct syntax.

When an SQL statement is wrapped automatically, the SQL statement may not compile correctly. Also, some SQL implementations may not permit a statement or clause to be split over more than one line.

Expected data is not being returned

The most common cause of data not being returned is when a link in the reference data hierarchy is missing. If the query contains links to all reference data views, missing data can be located by changing the view joins to left joins incrementally.

For example, the results returned by a query were unexpected, so an INNER JOIN on V_CREWS was changed to a LEFT JOIN:

INNER JOIN	V_OPERATORS
	ON
	ALLOCTNTIMESTAMP.Operator = V_OPERATORS.OperatorCode
	AND [WhereCriteria:Operator]
LEFT JOIN	V_CREWS
	ON
	V_OPERATORS.Operator_Crew_Code = VCREWS.CrewCode
	AND [WhereCriteria:Crew]

If an operator does not have a valid crew assigned, the link between V_CREWS and V_OPERATORS will exclude that operator from being returned. Using a LEFT JOIN the operator will be returned, indicating that it needs to be assigned a valid crew. This can be applied to any of the views joined to the alloctntimestamp and measuretimestamp tables.

Temp Table Collation Error

On occasion you may encounter a collation error in the support log.

For example:

System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This error occurs when the SQL Server collation differs from the database collation when temporary tables are being used in the query. For example, this query fails:

SELECT	...
FROM	Table
JOIN	#temp
	ON Table.Column = #temp.column

To resolve the problem, add COLLATE database_default to the join that involves a temporary table and the query will execute:

SELECT	...
FROM	Table
JOIN	#temp
	ON Table.Column COLLATE database_default = #temp.column COLLATE