locked
SQL 2008 Linked Server Issue RRS feed

  • Question

  • I have the following script which runs on Windows 2003 server with SQL 2008 and merges data from a SQL 2000 server, the script runs fine for a few weeks then I receive notification e-mails that the job has failed. 

     ------------------------------
    -- Description: Merge of tbl_0000000019, tbl_0000000021, tbl_0000000022
    --  to process calibration data for yield charts
    --
    -- Date: 02-06-2008
    -- Author: Jonathan Chambers
    -------------------------------

    -----------------------------
    -- Select the Data and Insert
    -----------------------------
    INSERT INTO tbl_CalMerge
    (
    PanelID,
    PanelLoadTime,
    PanelEjectTime,
    PlacementOK,
    Line_number,
    Machine_number,
    FK_AS0000000039,
    FK_AS0000000057,
    PCBID,
    PCBPosition,
    FK_AS0000000028,
    FK_AS0000000030,
    ASICRowid,
    ASICID,
    ASICOffset,
    ASICSpan,
    ASICFailure,
    ASICCalibDate,
    SleepCurrent,
    Rejected,
    Customer,
    Containment,
    WCID
    )
    SELECT
    Panel.PanelID,
    Panel.PanelLoadTime,
    Panel.PanelEjectTime,
    Panel.PlacementOK,
    Panel.Line_number,
    Panel.Machine_number,
    Panel.FK_AS0000000039,
    Panel.FK_AS0000000065,
    PCB.PCBID,
    PCB.PCBPosition,
    PCB.FK_AS0000000028,
    PCB.FK_AS0000000030,
    ASIC.ASICRowid,
    ASIC.ASICID,
    ASIC.ASICOffset,
    ASIC.ASICSpan,
    ASIC.ASICFailure,
    ASIC.ASICCalibDate,
    ASIC.SleepCurrent,
    ASIC.Rejected,
    ASIC.Customer,
    ASIC.Containment,
    ASIC.WCID
    FROM
    selcsql02.RDB_SEL050050D.dbo.tbl_0000000019 As Panel,
    selcsql02.RDB_SEL050050D.dbo.tbl_0000000021 As PCB,
    selcsql02.RDB_SEL050050D.dbo.tbl_0000000022 As ASIC
    WHERE
    ASIC.ASICrowid = PCB.FK_AS0000000030 and
    Panel.PanelID = PCB.FK_AS0000000028 and
    ASIC.ASICrowid >
    (select Max(asicrowid) from tbl_CalMerge)

    -----------------------------
    -- Clean Up the data older than 28 days
    -----------------------------
    Delete from tbl_CalMerge where asiccalibdate < (getdate()-28)

    Here's the error information, i don't understand why the error is containing aliases that are not being reference or even used in the script, I has this same issue last month which went away on its own - but I would like to find the root cause in-order to eliminate any future occurances.

    Date  5/24/2010 8:45:00 AM
    Log  Job History (Merge_TX_Data)

    Step ID  1
    Server  SELCMON01
    Job Name  Merge_TX_Data
    Step Name  Merge_TX_Data
    Duration  00:00:00
    Sql Severity  16
    Sql Message ID  107
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    Executed as user: ANTRIM\seladmin. Statement(s) could not be prepared. [SQLSTATE 42000] (Error 8180)  The column prefix 'Tbl1006' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1006' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1006' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1006' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1004' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1004' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1004' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1004' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1004' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1004' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1004' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107)  The column prefix 'Tbl1004' does not match with a table name or alias name used in the query. [SQLSTATE 42000] (Error 107).  The step failed.

    Monday, May 24, 2010 7:58 AM

Answers