My Qlik Cloud Connection to SQL Server behavior changed over the weekend
This morning a number of dashboards that have reloaded successfully for years did not reload. When troubleshooting, I discovered a couple of things.
1) This dashboard connected to an on-premesis SQL Server database server via a data gateway. When pulling data from a view that existed in one database but referenced tables from another database, the call to the view failed. I managed to fix this issue by opening up the definition of the connector to the SQL Server database engine (via data gateway) and removing the name of the database in the connection. I kept everything else the same - database engine was the same, etc. I only removed the name of the database. This allowed the connection to pull data from views that referenced tables in multiple databases.
2) Some of the views or queries that are executed in data loads reference tables from synonyms. Trying to pull data from queries or views that refence tables through synonyms now seems to be broken. In particular, I have some queries that reference tables in databases that exist on the same database server, but reference that second database through synonyms to tables that reside in databases on the same server. Queries that use those synonyms now fail. I have not found a way to modify the data gateway that allows those queries to succeed.
Has anybody else encountered this issue? Have you found a fix that allows the use of synonyms inside of queries for views that are called by a data gateway connection to SQL Server?
Here is an additional note. This change also impacts synonyms that reach across linked servers to pull data from databases / database tables that exist on other servers. I was able to get these queries working by replacing the synonyms with direct calls to tables (database.schema.table) rather than tablesynonym.
This worked for both tables in other databases on the same server, and tables reached across linked servers. For example: LinkedServerName.database.schema.table worked after I replaced the synonym with a direct call to the table.
Here is an additional note. This change also impacts synonyms that reach across linked servers to pull data from databases / database tables that exist on other servers. I was able to get these queries working by replacing the synonyms with direct calls to tables (database.schema.table) rather than tablesynonym.
This worked for both tables in other databases on the same server, and tables reached across linked servers. For example: LinkedServerName.database.schema.table worked after I replaced the synonym with a direct call to the table.
