Click here to Skip to main content
13,041,923 members (64,294 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Here's what the query is:

SELECT B.dbname AS [db_name], B.tblname AS [tbl_name], A.idx AS [remoteCol_idx],  A.object_id, AS [column_name], A.column_id, B.idx AS [tableTim_idx],   B.create_date, B.modify_date, B.object_id 
   FROM [linkedserver].[dbo].[remoteColumns] AS A
	JOIN [linkedserver].[dbo].[tableTime] AS B 
		ON (A.object_id = B.object_id)	
			ORDER BY [tbl_name]		

Long story short, the view of the return (part of it, for posting purposes here):

db_name	       tbl_name	remoteCol_idx	object_id	column_name ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ...
CMNQRSVW	V	1580	        613577224	l
CMNQRSVW	V	1582	        613577224	m
CMNQRSVW	V	1581	        613577224	n
CMNQRSVW	V	1586	        613577224	o
CMNQRSVW	V	1585	        613577224	p
CMNQRSVW	V	1584	        613577224	q
CMNQRSVW	V	1583	        613577224	r
CMNQRSVW	V	1594	        613577224	s
CMNQRSVW	V	1593	        613577224	t
CMNQRSVW	V	1592	        613577224	v
CMNQRSVW	V	1591	        613577224	w
CMNQRSVW	V	1590	        613577224	x
CMNQRSVW	V	1589	        613577224	y
CMNQRSVW	V	1588	        613577224	z
CMNQRSVW	V	1587	        613577224	aa
CMNQRSVW	V	2142	        613577224	file

As you'll note looking at the last item in the return "file"; this is the ONLY valid return item. These other [column_name] returns CAN'T exist because database CMNQRSVW table V hasn't any columns named thus! Only one column named "file".

Does [file][nvarchar](4000) NULL have anything to do with it?

These other column headings, by the way, are coming from other databases composing other tables on the same server instance. They have the same object_id; that can't be correct.

What is happening with this JOIN of mine? I don't get it.
Posted 1-Nov-12 11:12am
Andrew Cherednik 1-Nov-12 18:03pm
Can you post separately the returns of these two select statements:

SELECT B.dbname AS [db_name], B.tblname AS [tbl_name], B.object_id
FROM [linkedserver].[dbo].[tableTime] AS B


SELECT A.idx AS [remoteCol_idx], A.object_id, AS [column_name], A.column_id
FROM [linkedserver].[dbo].[remoteColumns] AS A
RedDK 2-Nov-12 13:51pm

Why wouldn't I be able to do that?
This table above is as it appears in the return, exactly. And as you're suggesting by separating the JOIN, the "seam" is there between [column_name] "aa" and "file"

Wikipedia has an entry for JOIN and they define the procedure as "... in a database ..." NOT "in databases". So I think they actually catch the idiomatic sense of the true JOIN.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

There actually is no solution to this "problem".

Attempting to JOIN table data that exists on different databases of a running instance was never an intention of the method known as JOIN.

JOIN can only be deployed on tables that are found in the same database.

But that's a qualification of "JOIN" specifically. I suspect I'll find a way to JOIN linked server table information despite SQL Server's reuse of object_ids.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 2 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100