How to reload a linked server?

Question

I am using Microsoft SQL Server 2014 Enterprise Edition. A problem occurs with the linked servers where it is necessary to restart the server, or to stop the MSSQLSERVER service. When the server is running again, the linked servers (to DB2) don't work properly and the SQL Server shows this error:

Msg 7302, Level 16, State 1, Line 10
Cannot create an instance of OLE DB provider "DB2OLEDB" for linked server "Airspe".

Only after restarting the server several times does the linked server start working.

1. Why is necessary to restart the server several times to get up linked servers?
2. Are there other solutions?

This is the script to create one of the linked servers:

EXEC master.dbo.sp_addlinkedserver 
@server = N'AIRS', 
@srvproduct=N'Microsoft OLE DB Provider for DB2', 
@provider=N'DB2OLEDB', 
@datasrc=N'###.###.###.##',@provstr=N'Provider=DB2OLEDB;
    Data Source=#####;Persist Security Info=True;Password=**********;
    User ID=######;Initial Catalog=######;
    Network Address=###.###.###;Package Collection=AICOLDP;DBMS Platform=DB2/AS400',
@catalog=N'#####'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AIRS',@useself=N'False',@locallogin=NULL,@rmtuser=N'#####',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'remote proc transaction promotion', @optvalue=N'true'

This is the provider configuration:

Answer 1

I believe something is missing in the configuration done for DB2 Link.

the connection string should looks like this First.

Provider=DB2OLEDB; User ID=db2user; Password=mypassword; Initial Catalog=mydatabase;
Network Transport Library=TCP; Host CCSID=37;PC Code Page=1208;
Network Address=111.111.111.333; Network Port=446; Package Collection=MSDB2COL; 
Process Binary as Character=False; Connect Timeout=15; Units of Work=RUW;
DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0; Max Pool Size=100;
Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;

Please add missing parameters, so system will not set Automatic/rando parameters, that should solve the issue.

Answer 2

You might want to check that the linked server query is created or run under the context of a user who is a local administrator on the OS, to rule out this issue

https://support.microsoft.com/en-us/help/2647989/error-message-when-you-try-to-create-an-instance-of-an-ole-db-provider-in-sql-server-cannot-create-an-instance-of-ole-db-provider

Answer 3

_{Community wiki answer:}

The problem was the architecture. We had 32 bits; we have now migrated our system to another server with SQL Server 2014 Enterprise 64-bit and all our problems are over - MelgoV (question author).

---

_{Other suggestions left in comments in case they help others:}

It's not normal behaviour to have to restart except during configuration changes. You should disable the in process option if you care about your core database stability - otherwise errors in the linked server driver can cause your engine to crash.

It isn't trivial to get working and has a slew of knock-on effects, for example in how and what it uses to present security credentials across the network. Expect to spend a day trying to disentangle that if you go down that path.

---

The problem may have to do with the Microsoft driver itself. Use an IBM provided driver instead. Instructions to install this can be found here. Vendor-supplied drivers are often far superior: more stable and with higher performance, compared with those provided out of the box by Microsoft.