Kbase 21086: MS SQL Server, ODBC DataServer: 'Invalid Cursor State' Error - Creating insert trigger to maintain p
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/3/2008 |
|
Status: Verified
FACT(s) (Environment):
MS SQL Server DataServer
ODBC DataServer
SYMPTOM(s):
Can not insert or load records into SQL / ODBC server database
Error occurs when inserting or creating records in SQL / ODBC database
24000: [Microsoft][ODBC SQL Server Driver] Invalid cursor state
Schema holder is created via a pull of SQL Server / ODBC schema
SQL server / ODBC databases are not created by protomss / protoodb migration utility
PROGRESS_RECID and PROGRESS_RECID_IDENT columns are present in SQL Server table
PROGRESS_RECID and _PROGRESS_RECID_IDENT columns were manually created in SQL Server table
Trigger "_TI_<table-name>" is not present in the table.
CAUSE:
The insert trigger to maintain progress_recid column _TI_<table-name> is not present within the SQL Server table.
The DataServer expects a trigger called "_TI_<table-name>" upon record creation and insertion when a table has the PROGRESS_RECID column. This trigger is an object that depends on the table.
The trigger is automatically created when the schema migration tool (protomss / protoodb) is run. The migration tool creates PROGRESS_RECID column, PROGRESS_RECID_IDENT_ column, and insert trigger "_TI_<table-name>" object.
However, during a schema pull (when original tables are created in SQL Server or ODBC), this does not occur. Even though PROGRESS_RECID and PROGRESS_RECID_IDENT_ columns have been manually created within SQL Server, the insert trigger to maintain the columns are not created.
FIX:
1. Create trigger called "_TI_<table-name>" in SQL Server table
The syntax is
create trigger _TI_table ON table for insert as
if ( select PROGRESS_RECID from inserted) is NULL
begin
update t set PROGRESS_RECID = i.IDENTITYCOL
from table t JOIN inserted i ON
t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
select convert (int, @@identity)
end
2. Change the nonunique indexes so that they include a PROGRESS_RECID column as the last component:
create index table##index on table (column, PROGRESS_RECID)
3. If you have already created your schema holder, delete and recreate it.