All the databases are not visible in the SQL Server Data Connection dropdown

All the databases are not visible in the SQL Server Data Connection dropdown

Sometimes when you connect to a SQL Server using a Data Connection, you may not see all the databases in the dropdown even though the corresponding user has appropriate permissions. The same user can see all the databases including the missing one in SSMS. This user can create a custom query to a missing database in Spotfire and still can fetch the data.
  This issue is generally caused by the compatibility level for the missing databases being too low. You can find the following log entry in the Analyst client logs:
 

2019-05-22T11:48:48,335-04:00 2019-05-22 15:48:48,335 D02188@bbtnet.com [35] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - ConnectAndGetDatabaseNames: Database "XYZ" has compatibility level 80. Skipping.

The compatibility level for the missing database is 80 (which corresponds to SQL Server 2000) and levels below 90 are not supported in Spotfire.

To solve this issue, the compatibility level for the missing databases should be increased to at least 90 for them to show up in Spotfire.

Note: The user needs to investigate which compatibility level that is appropriate and its implications. For instance, existing custom queries targeting these databases, or queries used in other tools might need to be revised. Also, existing queries might need to be changed in order to work on a higher compatibility level. See the following links for how to change the level:

• https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/bb510680(v=sql.100)#remarks
• https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/bb510680%28v%3dsql.100%29