How to work with SQL Server databases which are configured as case sensitive database?

Description:

When Reverse Engineering (RE) from a SQL Server database that is case sensitive, erwin gives an error "Invalid object name 'SYS.SEQUENCES'".   This is because the database is case sensitive, and the system table is created in lower case, but RE script is trying to use upper case.  How to work with SQL Server databases which are configured as case sensitive database using erwin 9.8?

Solution:

It is because erwin is set to use case insensitive configuration of SQL Server as that is the most used configuration.  Under this configuration, everything is set as UPPER case in the database.  So, by default erwin query is set to UPPER case object names, e.g. ‘SYS_SEQUENCES’.  However, the RE query file which is used during the process to send queries to the database is available with the installation.  That file can be edited to change the query to lower or Mixed case as needed.  Here are the steps to do so.:

1. In the folder where erwin is installed, go the folder named ‘Queries’ It has a few files for databases that are supported natively.
2. For SQL Server specific queries, open the file ‘SQLServer2016.erwin_re_queries’ using any text editor, e.g. Notepad.
3. Search for the section for the desired objects, e.g. SEQUENCES in this case:

                FROM SYS.SEQUENCES

     4. Modify this to FROM SYS.sequences  ---> note that the lower case is important

     5. Save the file with the changes.

Now when you RE for sequences, it will generate the query using the lower case (or as in the same case as typed). Please note that this is literal and is used within single quotes while sending it to the database, so make sure that it is typed in correct case.  The example above is for SEQUENCES but can be applied for other objects as found in the same file.