Answered by:
SQL 2008 Linked Server Issue

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 0Message
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
-
Related links confirming the problem:
http://www.sqldev.org/sql-server-database-engine/linked-server-stuck-in-zombie-state-93899.shtml
http://www.sqlteam.com/FORUMS/topic.asp?TOPIC_ID=90712
http://www.ibprovider.com/forum/site/viewtopic.php?f=4&t=690
You can contact Microsoft Support.
You can also file a bug report at Connect: https://connect.microsoft.com/SQLServer?wa=wsignin1.0
Let us know what is the resolution.
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAMMonday, May 24, 2010 8:46 AM