Ask Reuben

SQL Server Compatibility

Why do I get a runtime error when I first attempt to connect to my new SQL Server database?

When customers first attempt to connect to a new version of Microsoft SQL Server database, there is a common error that is being reported.  The error occurs because although you are using a new version of SQL Server, in your database configuration you have it configured it to act as if it is an older version of SQL Server.

The error occurs on the DATABASE or CONNECT statement, and if you use FGLSQLDEBUG to look at the logs you might see output like …

| S:\gsb\...\ODI_common_msv.h:00373(3) : Diagnostic info:
| S:\gsb\...\ODI_common_msv.h:00374(3) : SQL State: 01000
| S:\gsb\...\ODI_common_msv.h:00375(3) : SQL code : 5701
| S:\gsb\...\ODI_common_msv.h:00376(3) : Message : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'databasename'.
| snc.c:00505(3) : ODBC Driver = 'msodbcsql17.dll'
| S:\gsb\...\ODI_common_msv.h:00078(1) : Server version = 14.0.1000.169
| S:\gsb\...\ODI_common_msv.h:00079(1) : DB Compat version= 100
| S:\gsb\...\ODI_common_msv.h:00090(1) : Normalized vers. = 1000
| S:\gsb\...\ODI_common_msv.h:00092(1) : DB Collation = SQL_Latin1_General_CP1_CI_AS
| S:\gsb\...\ODI_common_msv.h:00093(1) : UTF-8 DB Collat. = no
| S:\gsb\...\ODI_common_msv.h:00101(1) : Unsupported SQL Server version
| sqlcode : -6319
| sqlstate : HY000
| sqlerrd2 : -1
| sqlerrd3 : 0
| sql message : Internal error in the database library. Set FGLSQLDEBUG to get more details.

I have bolded the key things we look for when someone reports this error.  The key clue being “DB Compat version = 100”.

SQL Server has a concept called compatibility level and if you look at their documentation there is a table that explains what the number 100 refers to.  100 = SQL Server 2008.  So when your code checks to see what version of SQL Server you are using, the database is telling us that it is SQL Server 2008 !!!

What you need to do is to get your database administrator to alter the database configuration via the “ALTER DATABASE … SET COMPATIBILITY_LEVEL = … ” statement or other techniques, so that your database acts like a newer version of SQL Server that we support.