How to connect an external database in Sage Enterprise Intelligence with an identifier containing a dot ?

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

Leave a Reply

Your email address will not be published.