Resolving "Cannot create an instance of OLE DB provider" Databridge Error

This issue is resolved in Databridge version 1.9.0, please ensure you have this update installed.

Pre Databridge 1.9.0 workaround

When using newer versions of MSSQL you could encounter the following error whilst attempting to setup a linked server:

To resolve this error, you must make the linked server manually through SQL Server Management Studio. You can use the following query to create the linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'bksblink', @srvproduct=N'', @provider=N'SQLOLEDB', @datasrc=N'SQL_SERVER_NETWORK_PATH', @catalog=N'DATABASE_NAME'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'bksblink',@useself=N'False',@locallogin=NULL,@rmtuser=N'bksb',@rmtpassword='bksb'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server = N'bksblink', @optname=N'remote proc transaction promotion', @optvalue=N'true'

You must replace "SQL_SERVER_NETWORK_PATH" this needs to be your SQL Server Network Name and Instance (if applicable). For example "bksbsqlserver" or "bksbsqlserver\instance1".

You should also replace DATABASE_NAME, specifying which Database the linked server should be pointed towards, an example Database name would be "ProSolution".

Please ensure all other values such as "@server" are left the same, otherwise the linked server will not show up in the Databridge Configuration tool.

Once you have executed this query, restart the Databridge Configuration tool and the linked server status will show as connected if valid information has been provided.