SEI – How to connect an external database in Sage Enterprise Intelligence with an identifier containing a dot ?
Sometimes your database contains a dot in the identifier name.
This is allowed by SQL server : https://docs.microsoft.com/fr-be/sql/relational-databases/databases/database-identifiers?view=sql-server-2017
« When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets. »
When you use SQL query you can call this database like that : [external.db] or « external.db »
Unfortunatly brackets are not allowed while in environement declaration tab of Sage Enterprise Intelligence.
If you try to use double quotation marks or leave the identifier with markers then Sage Enterprise Intelligence will confuse the schema name and the second part of the identifier name, probably due to a parsing order on SEI side.
The workaround in this situation is to use the power of SQL server and the views.
1/ Create another database ientifier with a name without any special character
2/ In this database create a view based on the other database.
CREATE VIEW Example_Table_Threw_View AS SELECT * FROM [external.db].dbo.Example;
3/ Connect now your new external Database [externaldbview] as the new Datasource in Sage Enterprise Intelligence
4/ Check with a simple process