btjtaylor (7/14/2015)

---

I was working on this problem for a client and came across this thread. I experienced the issue when using a 2-node SQL 2012 cluster, when the clustered instance's node changed the jobs relying on the AS400 linked database would stop working.

The following code resolved the issue by adding a startup procedure, allowing the clustered instance to move freely:

USE [master]

GO

SET ANSI_NULLS ON

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE

EXEC sp_configure 'scan for startup procs', '1';

RECONFIGURE

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[usp_enum_oledb_providers]

AS

exec sp_enum_oledb_providers

GO

sp_procoption 'usp_enum_oledb_providers', 'startup', 1

This was cobbled together from a couple of blog entries I found so i'm afraid i'm not sure all the code is needed (my expertise lies mostly in network infrastructure and virtualization) but it works!

Hope this helps someone.