Link to home
Create AccountLog in
Avatar of jdallen75
jdallen75Flag for Canada

asked on

Using Linked Servers in multi-part identifiers

Below is a stored procedure that existed prior to an archiving strategy being implemented.
Now after archiving, two of the tables (OEEEvent and OEEEventFlexData) now exist on an archive server, set up as a Linked Server.

For my reporting on archived items, I need it revised to:

SELECT    
[MyLinkedServer].[ArchiveDB].[ArchiveUser].OEEEvent.tStart,
OEEConfigEvent.sDescription AS sEventDesc,
[MyLinkedServer].[ArchiveDB].[ArchiveUser].OEEEventFlexData.sFlex1String AS VIN,
[MyLinkedServer].[ArchiveDB].[ArchiveUser].OEEEventFlexData.sFlex2String AS CSN,
[MyLinkedServer].[ArchiveDB].[ArchiveUser].OEEEventFlexData.dFlex3Numeric AS [Tracking Number],
dbo.Cus_Orders.BR_BroadcastTimestamp AS BroadcastTime,
[MyLinkedServer].[ArchiveDB].[ArchiveUser].OEEEvent.dStart,
dbo.Cus_Platforms.PlatformName, dbo.Cus_Platforms.PlatformLabel,
dbo.Cus_Orders.BR_CommodityPart1 AS [Part Number],
dbo.Cus_Orders.BR_PVI AS [PVI]
etc....

I get an error of the form "The multi-part identifier "MyLinkedServer.ArchiveDB.ArchiveUser.Table.Column" could not be bound.

Suggestions?
SELECT     
OEEEvent.tStart, 
OEEConfigEvent.sDescription AS sEventDesc, 
OEEEventFlexData.sFlex1String AS VIN, 
OEEEventFlexData.sFlex2String AS CSN, 
OEEEventFlexData.dFlex3Numeric AS [Tracking Number], 
dbo.Cus_Orders.BR_BroadcastTimestamp AS BroadcastTime,
OEEEvent.dStart,
dbo.Cus_Platforms.PlatformName, dbo.Cus_Platforms.PlatformLabel,
dbo.Cus_Orders.BR_CommodityPart1 AS [Part Number],
dbo.Cus_Orders.BR_PVI AS [PVI]
FROM         BizwareUser.OEEEvent WITH (READUNCOMMITTED)
LEFT OUTER JOIN BizwareUser.OEEEventFlexData ON OEEEvent.lOEEEventId = OEEEventFlexData.lOEEEventId 
LEFT OUTER JOIN BizwareUser.OEEConfigEvent ON OEEConfigEvent.lOEEConfigEventId = OEEEvent.lOEEConfigEventId 
LEFT OUTER JOIN dbo.Cus_Orders ON (OEEEventFlexData.dFlex3Numeric = dbo.Cus_Orders.TrackingNumber)
AND (dbo.Cus_Orders.BR_VIN = OEEEventFlexData.sFlex1String)
AND (dbo.Cus_Orders.CSNNumber = OEEEventFlexData.sFlex2String)

--Modified Aug 14/07
--LEFT OUTER JOIN dbo.Cus_PlatformID_By_PartID ON dbo.Cus_PlatformID_By_PartID.BroadCastPartNumber = dbo.Cus_Orders.BR_CommodityPart1
LEFT OUTER JOIN dbo.Cus_PlatformID_By_PartID ON (dbo.Cus_Orders.BR_CommodityPart1 = dbo.Cus_PlatformID_By_PartID.BroadCastPartNumber) and (dbo.Cus_Orders.Make = dbo.Cus_PlatformID_By_PartID.Make or dbo.Cus_PlatformID_By_PartID.Make is null)	

LEFT OUTER JOIN dbo.Cus_Platforms ON (dbo.Cus_PlatformID_by_PartID.PlatformID = dbo.Cus_Platforms.PlatformNumber)
WHERE (OEEConfigEvent.sFolder = 'Torque Wrench Data') 
AND (OEEEventFlexData.sFlex2String = @CSN) 
AND (OEEEvent.loeeconfigworkcellid <> 1000004)
AND (OEEEvent.loeeconfigworkcellid <> 1000005)
GROUP by tStart,OEEConfigEvent.sDescription, sFlex1String, sFlex2String, dFlex3Numeric, dbo.Cus_Orders.BR_CommodityPart1, 
dbo.Cus_Orders.BR_PVI, PlatformName, PlatformLabel,  dStart, dbo.Cus_Orders.BR_BroadcastTimestamp
ORDER BY sEventDesc

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jdallen75

ASKER

Thanks! For some reason, I was trying to assign the alias in the SELECT clause rather than the FROM clause :-(