在表JOIN期间，特别是在[object_id]上，系统对象不少于...返回荒谬的返回值 [英] During table JOIN, specifically ON [object_id] a system object no less ... getting absurd return

问题描述

查询内容如下:

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, A.name 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

正如您将要注意的那样，注意返回的文件"中的最后一项；这是唯一有效的退货项目.这些其他[column_name]返回的值不存在，因为数据库CMNQRSVW表V没有由此命名的任何列！只有一列名为文件".

[file] [nvarchar](4000)NULL与它有什么关系吗?

顺便说一下，这些其他列标题来自组成同一服务器实例上其他表的其他数据库.它们具有相同的object_id；那是不正确的.


我的这个JOIN发生了什么事?我不明白.

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.

推荐答案

实际上没有解决此问题"的方法.

尝试联接存在于正在运行的实例的不同数据库上的表数据从来不是使用称为JOIN的方法的意图.

JOIN只能部署在同一数据库中的表上.

但这是"JOIN"的限定条件.我怀疑尽管SQL Server重用了object_ids，我仍会找到一种方法来联接链接的服务器表信息.

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.

这篇关于在表JOIN期间，特别是在[object_id]上，系统对象不少于...返回荒谬的返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！