EA 14 Import DB fails

[Guillaume]

Hi,

I try to run an Import DB Schema in EA from an existing DB via an ODBC data source.
Once the Import starts, I get an 'Error retrieving table list' message when using EA 14 build 1420.

Running the same test in EA13.5 works fine.

[Paolo F Cantoni]

Hi,

I try to run an Import DB Schema in EA from an existing DB via an ODBC data source.
Once the Import starts, I get an 'Error retrieving table list' message when using EA 14 build 1420.

Running the same test in EA13.5 works fine.

Bu**er!  I was going to import a DB tomorrow.  Anyone else seeing this?  What is the DB Technology involved?

Paolo

[Guillaume]

I ran it on an ODBC DSN for a Postgres DB.

[costa2]

I have the same issue when I try to import a SQL Server 2008 R2 database. Honestly it is disappointing - it's basic functionality in my mind.

[Geert Bellekens]

I have been able to successfully import SQL server databases (versions ranging from 2000 to 2016), so it is definitely possible.

Things I would check:

- does the user have enough rights
- is there something wrong with the ODBC (wrong driver)

Geert

[costa2]

The user account I am using to reverse engineer the data is dbo in the database.

 Any other tool I have (Toad Data Modeler, Power Designer Data Architect) works fine when it comes to reverse engineering tables.

[costa2]

I ran a trace and the last query that is executed and which fails in SQL Server 2008 R2 is:

Code: [Select]

Select seq.SEQUENCE_SCHEMA, seq.SEQUENCE_NAME, ISNULL(com.value,'') As remarks,
   ('CREATE SEQUENCE ' + seq.SEQUENCE_SCHEMA  + '.' + seq.SEQUENCE_NAME + CHAR(13) + CHAR(10) +     ' AS ' + seq.DATA_TYPE + CHAR(13) + CHAR(10) +     ' START WITH ' + CONVERT("nvarchar", seq.START_VALUE) + CHAR(13) + CHAR(10) +     ' INCREMENT BY ' + CONVERT("nvarchar", seq.INCREMENT) + CHAR(13) + CHAR(10) +     ' MINVALUE ' + CONVERT("nvarchar", seq.MINIMUM_VALUE) + CHAR(13) + CHAR(10) +     ' MAXVALUE ' + CONVERT("nvarchar", seq.MAXIMUM_VALUE) + CHAR(13) + CHAR(10) + CASE WHEN seq.CYCLE_OPTION = 1 THEN ' CYCLE ' ELSE ' NO CYCLE ' END ) As seq_definition
From INFORMATION_SCHEMA.SEQUENCES seq, (select id, [name] from sys.sysobjects where name like 'seq%') sysseq
Left JOIN ( SELECT major_id, [value] From sys.extended_properties Where name = 'MS_Description' and minor_id = 0 ) com
    ON  ( sysseq.id = com.major_id )
Where seq.SEQUENCE_NAME = sysseq.[name]
And sequence_schema IN ('dbo')
and sequence_schema not in ('','INFORMATION_SCHEMA','sys')

There is no INFORMATION_SCHEMA.SEQUENCES view. I don't have SQL server 2014 or 2016 server to see if the view works but if you go to https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-2017 there is no SEQUENCES view.

So, this looks to me clearly that it's a bug. Please fix it.

Just to make sure there is no confusion, the model I want to import into is a Basic SQL Server 2008 Model.

[hd]

Sequences are not supported in SQL Server 2008.  Set Sequences to False in the Object Filters section of the "Import DB schema from ODBC source" dialog.

[costa2]

Yes, this worked. Perhaps sequences should be set to false for SQL Server?

[costa2]

BTW, I chose Differences under Database Builder and it failed with the same error. In the Differences with Options dialog there is no Sequences option to turn off.

You guys have to do some analysis and identify all the spots where sequences are queried and turned them off for those RDBMSes that don't have sequences. Otherwise it is a mickey mouse functionality.