ExecuteNonQuery Connection Property not Initialized

<< Click to Display Table of Contents >>

ExecuteNonQuery Connection Property not Initialized

Symptoms: The error message below was received by users when attempting to alter the DT_LOCATION table within EQuIS Professional or when attempting to load an EDD through EQuIS Data Processor (EDP). If a different table is referenced in the error message received, replace DT_LOCATION and DT_SAMPLE in this document with the table names from the error message.

 

ExecuteNonQuery: Connection property has not been initialized.

 

Cause/Details: The error shown above may have multiple causes, but many of those causes1 are related to the following scenario:

 

1.The table has current EQuIS auditing applied (equis.add_audit) and the audit trigger(s) fail**; and

2.The table does NOT have ***_ID as part of the primary key; and

3.The table satisfies either (1) or (2), or it is loaded in an EDD after a table that satisfies (1) and (2); and

4.The table already contains the data being loaded in the EDD

 

1See additional possible causes in the Notes below.

 

Given these conditions, you have the error reported for DT_LOCATION and also DT_SAMPLE when loading an EDD with existing data and committing via Insert & Update. DT_LOCATION would satisfy (1) and (2), so it would report the error. DT_SAMPLE would also report the error because of (3) - it is committed after DT_LOCATION.

 

Notes:

This error has also been caused by having an invalid member_code (a member_code that does not exist in RT_GROUP.GROUP_CODE) in the EDD. In order to resolve this error, the user must remove the invalid member_code in the EDD.

This error has also been caused by attempting to commit data using Professional EDP Version 6.3 into a version 6.4 database. To address this, the Professional version used must match the database version.

 

Resolution/Workaround: As noted above, the error may only occur if the current EQuIS audit trigger fails. The audit trigger may fail for either or both of the following reasons:

 

1.The audit trigger was created prior to EQuIS Version 6.2.2 and the xact_abort hotfix has not been applied.

2.Current EQuIS auditing was turned on (e.g. "exec equis.add_audit 'DT_LOCATION'") before disabling EQuIS 5 auditing.

 

EQuIS 5 auditing uses an identity (autonumber) field called RID. The current EQuIS auditing does not use the RID field, and the RID field will prevent the current EQuIS audit triggers from working correctly. The correct way to convert from EQuIS 5 auditing to current EQuIS auditing is:

 

Delete the EQuIS 5 audit trigger named TRG_AUDIT_{tableName} (e.g. TRG_AUDIT_DT_LOCATION or TRG_AUDIT_DT_SAMPLE)

Drop the RID column from the table

Apply curent EQuIS auditing by calling the equis.add_audit procedure (e.g. "exec equis.add_audit 'DT_LOCATION'")

 

If you apply current EQuIS auditing before dropping the RID column from the table, the current EQuIS audit triggers will fail. If you then drop the RID column, the audit triggers will still fail. You need to use either of the following resolutions:

 

1.Manually fix the audit triggers to remove the reference to the RID column. You can use this query to find affected triggers.

 

                   select distinct

                          o.name as object_name

                         ,o.type_desc

                     from sys.sql_modules m

                            inner join sys.objects o on m.object_id=o.object_id

                    where m.definition like '%rid%'

                      and o.type_desc = 'SQL_TRIGGER'

                    order by 2,1

 

Current Audit triggers are updated automatically with the schema update process or can be done with the following stored procedure call:

 

EXEC equis.refresh_schema NULL, NULL;

 

Passing in NULL for the @schema_name parameter and NULL for the @table_name parameter causes the updates to be performed on all tables in the database update process.