Linked AS400 stopped working

Question

Post reply

Linked AS400 stopped working

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

John Cooper (5/21/2014)
Did you ever find a working solution for this? I am having the same issue 🙁

Read two posts above yours. Try that to see if it works. It has worked for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Cooper Mr or Mrs. 500 Points: 534 More actions · Answer 2

Thanks Jeff,

That's what I have been doing is opening it in SSMS, clicking on properties and then OK.

I was wondering if someone had found a better way. I manage 45 servers with 5 Linked Servers each so even though the properties

tab thing works, it's not practical.

I wonder if there is a programmatic way of doing it maybe? I'm going to investigate that.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

John Cooper (5/22/2014)
Thanks Jeff,
That's what I have been doing is opening it in SSMS, clicking on properties and then OK.
I was wondering if someone had found a better way. I manage 45 servers with 5 Linked Servers each so even though the properties
tab thing works, it's not practical.
I wonder if there is a programmatic way of doing it maybe? I'm going to investigate that.

I'd do a trace/xevent session against a dev server when opening the linked server properties window to identify what that actually does and then maybe try running a script that duplicates those actions against another dev server to see if it fixes the problem.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

John Cooper Mr or Mrs. 500 Points: 534 More actions · Answer 4

Thanks Jack !

After much trial and error I found the trick.

I set up a scheduled job that runs every 5 minutes that executes this statement:

exec sp_enum_oledb_providers

This is the magic statement executed when you click on properties and then click OK.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

John Cooper (5/22/2014)
Thanks Jack !
After much trial and error I found the trick.
I set up a scheduled job that runs every 5 minutes that executes this statement:
exec sp_enum_oledb_providers
This is the magic statement executed when you click on properties and then click OK.

Haven't done a deep dive on it but that seems like a very nice trick. Thanks for posting it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

btjtaylor Valued Member Points: 53 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

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.

I have nearly identical code on my instances. On the third-party-provided, um..., "appliance" that we have (kind words for a real POS 😀 ), it rarely works during a reboot or even a restart. There's really something wrong with that system but I'm mostly not allowed to touch it to find out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rvidhyas Newbie Points: 3 More actions · Answer 8

Hi,
I am getting the below error when using Oracle linked server. The vendor modified the structure of his DB and now the source(Oracle) has columns as VARCHAR2(32767 Byte), so my select * into [Sqlserver].Table from Openquery (xxxxx,'select * from Oracle.Table') gives me the below error. I tried all the solutions posted here, but nothing works for me. Please help.

OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxx" returned message "The system cannot find message text for message number 0x80040e21 in the message file for OraOLEDB.".
Msg 7340, Level 16, State 2, Line 1
Cannot create a column accessor for OLE DB provider "OraOLEDB.Oracle" for linked server "xxxx".

edgarwalther SSC Rookie Points: 27 More actions · Answer 9

Also got this error in SQL2017.

The "trick" mentioned above, does work:

"simply opening the properties on the link ‘fixes’ the 7340 error. I opened then closed the properties window and the ‘broken’ jobs started to work. "

But that does not feel very comfortable...

camaro305hp Mr or Mrs. 500 Points: 560 More actions · Answer 10

This thread is timeless. Every time I set up a new instance, I forget about this until my AS400 linked server inevitably stops working a few days later. And each time I end up here again. Thanks everyone for helping find the sp_enum_oledb_providers solution, especially John Cooper. This is still an issue on 2019 instances.

This reply was modified 3 years, 4 months ago by camaro305hp.