Microsoft Cursor Engine [-2147217887] Multiple-step operation generated errors.

[BruceTOGAF2]

We recently migrated our Sparx Repository from Oracle to SQL Server. I should have tested more thoroughly before testing the improved performance.

I am getting the following error message when I use Sparx EA to try and create a new package.

Microsoft Cursor Engine [-2147217887] Multiple-step operation generated errors. Check each status value.

[Paolo F Cantoni]

We recently migrated our Sparx Repository from Oracle to SQL Server. I should have tested more thoroughly before testing the improved performance.

I am getting the following error message when I use Sparx EA to try and create a new package.

Microsoft Cursor Engine [-2147217887] Multiple-step operation generated errors. Check each status value.

Did you run an integrity check over the transported repository?  If not, do so.  It may help.

Paolo

[BruceTOGAF2]

Before migration I ran a 'Project Integrity Check - Report Only' over the Oracle repository. The report listed 2 proposed actions
•delete one invalid Association connector
•delete 167 orphaned stereotypes

Before migration I ran a 'Project Integrity Check - Recover/Clean. The process
•deleted one invalid Association connector
•deleted 167 orphaned stereotypes

The Oracle Production DB was ready. I ran the migration. The log file reported
98 table(s) successfully   migrated
0 table(s) partially migrated.
0 table(s) failed to migrate.

In the transported SQL Server Repository I ran a Project Integrity Check - Report Only. The report listed 1 proposed action
•delete 12 orphaned stereotypes

I ran a Project Integrity Check - Recover/Clean. The process
•deleted 12 orphaned stereotypes

I then tested creating a new package or diagram. The error 'Microsoft Cursor Engine [-2147217887]' occurs every time I try to create a new package or diagram.

[philchudley]

This is what is says in the Microsoft web site
Symptoms

If you use ADO to insert a new record through a client-side recordset into a SQL Server table that has a non-nullable datetime field with a default value, you receive the following error message if you do not supply a value for the datetime field:
Run-time error '-2147217887 (80040e21)': Multiple-step operation generated errors. Check each status value.
This error occurs whether you use the OLE DB Provider for SQL Server or the OLE DB Provider for ODBC Drivers. The error message may differ when you use Microsoft Data Access Components (MDAC) version 2.5 Service Pack 1 (SP1) or earlier. This error does not occur with a server-side cursor.
Cause

This error occurs in the Client Cursor Engine when it attempts to convert the value of type DBTYPE_DBTIMESTAMP to DBTYPE_VARIANT.
Resolution

There are several ways to work around this problem:
Use a server-side cursor for the recordset.
Remove the default value that is specified for the field in the database.
Always specify a value for the field when you add a new record.
Status

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

The workarounds probably won't help.

It might be worth having a look at the tables in SQL Server Management Studio and seeing if any date fields are non-nullable, and then making them nullable

That may work

We have the same problem with updating diagrams, but after ignoring the message a few times, it goes away.

Phil

[BruceTOGAF2]

Hi Phil,

All tables, constraints and indexes must be created by Sparx SQL scripts EASchema_1220_SQLServer.sql and EASchema_1220_SQLServer_Update1.sql. Failure to do this causes error "Microsoft Cursor Engine [-2147217887] Multiple-step operation generated errors. Check each status value" when I try to create packages or diagrams in Sparx.

I have discovered that Microsoft Sql Server Migration Assistant (SSMA) destroys many of the constraints, primary keys and indexes that were created by EASchema_1220_SQLServer.sql. Because of problems with Sparx Project Transfer Tool, we are keen to use SSMA which we have installed on the same machine as the target SQL Server DB.

There are various stages in the SSMA process. SSMA stage 'Convert Schema' leads to the destruction of objects previously created by EASchema_1220_SQLServer.sql. After the execution of those Sparx scripts, Microsoft SSMA is altering some objects, creating new objects and deleting more than 140 objects in SQL Server.

In SSMA I have experimented with avoiding SSMA stage 'Convert Schema'. If I jump straight to SSMA stage 'Migrate Data' I get error 'The given key was not present in the dictionary'

[BruceTOGAF2]

I have added a new post specifically about the SSMA error "The given key was not present in the dictionary".

[Colin Coates]

Hi Bruce.

If you are using Cloud Services to access your EA repository, maybe you could try migrating your Oracle hosted repository into a FEAP?

It depends on what your reasons were for migrating from Oracle to SQL Server...

[BruceTOGAF2]

The reason for migrating from Oracle to SQL Server is to improve Sparx performance in a WAN Architecture. I have successfully migrated to SQL Server by locating Sparx Project Transfer on same machine as SQL Server DB server. I was given this recommendation by Sparx Systems Support in April, but my company told me that was not permitted. Having tried other avenues and failed, I was given permission to temporarily locate Sparx EA on same VM as DB server. Instead of Sparx Project Transfer taking 5 days, it took 5 hours.  Our Sparx Production Repository is now on SQL Server. Performance is much better.