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:

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,
dbo.Cus_Platforms.PlatformName, dbo.Cus_Platforms.PlatformLabel,
dbo.Cus_Orders.BR_CommodityPart1 AS [Part Number],
dbo.Cus_Orders.BR_PVI AS [PVI]

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

OEEConfigEvent.sDescription AS sEventDesc, 
OEEEventFlexData.sFlex1String AS VIN, 
OEEEventFlexData.sFlex2String AS CSN, 
OEEEventFlexData.dFlex3Numeric AS [Tracking Number], 
dbo.Cus_Orders.BR_BroadcastTimestamp AS BroadcastTime,
dbo.Cus_Platforms.PlatformName, dbo.Cus_Platforms.PlatformLabel,
dbo.Cus_Orders.BR_CommodityPart1 AS [Part Number],
dbo.Cus_Orders.BR_PVI AS [PVI]
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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
You should alias these tables when you use them so that they can be referenced with little trouble.

So if you had this:
FROM [MyLinkedServer].[ArchiveDB].[ArchiveUser].OEEEventFlexData

Then you would do this:
FROM [MyLinkedServer].[ArchiveDB].[ArchiveUser].OEEEventFlexData AS LinkedOEEEventFlexData

Then you would reference the columns as

Then the query will understand what you are trying to accomplish.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdallen75Author Commented:
Thanks! For some reason, I was trying to assign the alias in the SELECT clause rather than the FROM clause :-(
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.