ExecuteNonQuery Connection Property not Initialized

<< Click to Display Table of Contents >>

Navigation:  Professional > Troubleshooting >

ExecuteNonQuery Connection Property not Initialized

Symptoms: Users receive the below message when attempting to:

alter the DT_LOCATION table within EQuIS Professional or

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/or

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

3.The table 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.

The Professional build used must match the database build.

 

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. To convert from EQuIS 5 auditing to current EQuIS auditing, have a database administrator do the following:

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

2.Drop the RID column from the table

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

 

Applying current EQuIS auditing before dropping the RID column from the table causes the current EQuIS audit triggers to fail. If you then drop the RID column, the audit triggers will still fail. Use either of the following resolutions:

 

1.Manually fix the audit triggers to remove the reference to the RID column. 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

 

2.Current Audit triggers update automatically with the schema update process or with the following stored procedure:

EXEC equis.refresh_schema NULLNULL

 

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.