BDSSA: ETL fails in SQL Server Environment with: "DB Link db_site1_link between DW and OM is not valid"

Version 2
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    BladeLogic Decision Support for Server Automation (5 Report Viewers, 1 Query Studio)


    APPLIES TO:

    BMC BladeLogic Decision Support for Server Automation (5 Report Viewers, 1 Query Studio)



    PROBLEM:

     

    The BDSSA ETL job fails in a SQL Server environment with the following stack trace in Metadata Navigator:

    50000 : S0001 : com.microsoft.sqlserver.jdbc.SQLServerException: DB Link db_site1_link between DW and OM is not valid. SQLServer Error Number: 7202. SQLServer Error Message: Could not find server 'db_site1_link' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
    com.microsoft.sqlserver.jdbc.SQLServerException: DB Link db_site1_link between DW and OM is not valid. SQLServer Error Number: 7202. SQLServer Error Message: Could not find server 'db_site1_link' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
    at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlS.treatTaskTrt(SnpSessTaskSqlS.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandScenario.treatCommand(DwgCommandScenario.java)
    at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
    at com.sunopsis.dwg.cmd.e.i(e.java)
    at com.sunopsis.dwg.cmd.e.y(e.java)
    at com.sunopsis.dwg.DwgJv.treatCmd(DwgJv.java)
    at com.sunopsis.dwg.DwgJv.main(DwgJv.java)
    at oracle.odi.Agent.main(Agent.java)

    or with error

    ETL Procedure CHECK_CONFIGURATION Check DB Link between OM and DW failed with error 
    MICROSOFT_SQL_SERVER connection BSA_phy_dw_svr_site1. 
    Caused By: com.microsoft.sqlserver.jdbc.SQLServerException: Could not find server 'BSA_SITE1_84' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

     


    CAUSE:

    This error message suggests that the db_site1_link linked server seems to no longer be working and is likely no longer present in the BSARA_DW_DB DB instance.


    SOLUTION:

     

    This error message suggests that the db_site1_link linked server seems to no longer be working and is likely no longer present in the BSARA_DW_DB DB instance.

    The linked server would have been present and working when the product was first installed in order for the installation to complete so it may have subsequently been removed or invalidated.

    Login to SQL Server Metadata Navigator and look under linked_servers to see if there is an entry for db_site1_link.

      

    If it does not exist, it implies it has been removed and it must be recreated using the create_linked_server_for_om.sql script which ships in the BSARA\Db_Scripts\sqlserver\schema folder of the BDSSA external files zip file.

    See the BDSSA documentation for steps on how to manually create a linked server.

    If the link does already exist, right click it and do a Test Connection. If this fails, ask the DBA to examine why the linked server is not able to access the bladelogic db.

    There is also, a chance that the Databases are clustered and the secondary/HA instance is not correctly synced with primary or vice versa. In one of the cases, it was observed that the link was missing from failover instance as the sys.servers table was not synced up correctly. Further it was identified that this table is not automatically synced but has to be done manually.

     


    Article Number:

    000083784


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles