'Invalid column name 'is_change_feed_enabled'.'; detailedError - A database operation failed with the following error: 'Invalid column name 'is_change_feed_enabled'.'

Question

When I tried to setup link connection between on-prem sql server and Azure sql dedicated pool, took the below error.

Failed to enable Synapse Link on the source due to 'Failed to enable the source database: Some internal error happened due to 'Calling internal service failed: Failed to execute non query on change publisher with status code 400 and error Fail to non-query change publisher with error: 'sqlErrorCode - 22301; exceptionCode - TransferServiceUnknowError; error - A database operation failed with the following error: 'Invalid column name 'is_change_feed_enabled'.'; detailedError - A database operation failed with the following error: 'Invalid column name 'is_change_feed_enabled'.' One or more errors occurred. Invalid column name 'is_change_feed_enabled'., SqlErrorNumber=207,Class=16,State=1, ;'.'.'. Failed to disable Synapse Link on the source due to 'Failed to drop the link topic in the source database: Some internal error happened due to 'Calling internal service failed: Failed to execute non query on change publisher with status code 400 and error Fail to non-query change publisher with error: 'sqlErrorCode - 22301; exceptionCode - TransferServiceUnknowError; error - A database operation failed with the following error: 'Invalid column name 'is_change_feed_enabled'.'; detailedError - A database operation failed with the following error: 'Invalid column name 'is_change_feed_enabled'.' One or more errors occurred. Invalid column name 'is_change_feed_enabled'., SqlErrorNumber=207,Class=16,State=1, ;'.'.'. View details

Answer 1

Hi @AdmKaraH ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

I understand that you are trying to establish link connection between SQL Server and Azure Synapse Analytics dedicated SQL pool.

is_change_feed_enabled indicates whether the current database is enabled for Azure Synapse Link for SQL . It's one of the columns present in sys.databases(T-SQL).

However, If a database isn't ONLINE, or AUTO_CLOSE is set to ON and the database is closed, the values of some columns can be NULL. If a database is OFFLINE, the corresponding row isn't visible to low-privileged users. To see the corresponding row if the database is OFFLINE, a user must have at least the ALTER ANY DATABASE server-level permission, or the CREATE DATABASE permission in the master database.

To make sure column 'is_change_feed_enabled' is present in the SQL server, kindly run the below query in onprem sql:

SELECT name, is_change_feed_enabled FROM sys.databases;

Additionally , kindly be mindful of the below known limitations when creating Synapse link between DedSQL pool and SQL Server:

• Azure Synapse Link for SQL can't be enabled on databases that are transactional replication publishers or distributors.
• With asynchronous replicas in an availability group, transactions must be written to all replicas before publishing to Azure Synapse Link for SQL.
• Azure Synapse Link for SQL isn't supported on databases with database mirroring enabled.
• Restoring an Azure Synapse Link for SQL-enabled database from on-premises to Azure SQL Managed Instance isn't supported.

Azure Synapse Link for SQL is not supported on databases that are also using Azure SQL Managed Instance Link. Caution that in these scenarios, when the managed instance transitions to read-write mode, you might encounter transaction log full issues.

I tried to create synapse link for onprem sql to my dedSQLPool and it worked without any error. So , kindly check if the above limitations pertain in your case .

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou