Linked AS400 stopped working

  • Jeff Moden

    SSC Guru

    Points: 993924

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • John Cooper

    SSC-Addicted

    Points: 454

    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: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • John Cooper

    SSC-Addicted

    Points: 454

    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: 993924

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • btjtaylor

    Valued Member

    Points: 53

    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: 993924

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • rvidhyas

    Newbie

    Points: 3

    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: 26

    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...

     

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

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