请问是否没有办法使用linkedserver获取远程服务器的视图信息

Question

例如下面的SQL

 SELECT  CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme ,
                        p.partition_number ,
                        CONVERT(VARCHAR(MAX), ds2.name) AS filegroup ,
                        CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary ,
                        STR(p.rows, 9) AS rows ,
                        GETDATE()
                FROM    [ip,端口].[数据库].sys.indexes i
                        JOIN[ip,端口].[数据库].sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
                        JOIN [ip,端口.[数据库].sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
                        JOIN [ip,端口.[数据库].sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
                        JOIN [ip,端口].[数据库].sys.partitions p ON dds.destination_id = p.partition_number
                                                 AND p.object_id = i.object_id
                                                 AND p.index_id = i.index_id
                        JOIN [ip,端口].[数据库].sys.partition_functions pf ON ps.function_id = pf.function_id
                        LEFT JOIN [ip,端口].[数据库].sys.Partition_Range_values v ON pf.function_id = v.function_id
                                                              AND v.boundary_id = p.partition_number
                                                              - pf.boundary_value_on_right
                WHERE   i.object_id = OBJECT_ID('表')
                        AND i.index_id IN ( 0, 1 )
                ORDER BY p.partition_number

Answer 1

Should work if linked server set properly.

Answer 2

我是这样设置的，但是不行

Answer 3

Tested on my sql2k8r2 servers and worked fine. What's sql version on your servers? 

Answer 4

我的机器是sqlserver2008r2，而远程机器是sqlserver2008

Answer 5

你是得不到数据, 并没有报错吧? 从语句上来看, 应该是这个函数导致的

WHERE   i.object_id = OBJECT_ID('表')

这个是取当前数据库的表的 object_id

Answer 6

考虑用 openquery, 在链接服务器上去执行查询

select * from openquery([ip,端口], N'SELECT  CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme ,
                         p.partition_number ,
                         CONVERT(VARCHAR(MAX), ds2.name) AS filegroup ,
                         CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary ,
                         STR(p.rows, 9) AS rows ,
                         GETDATE()
                 FROM    [数据库].sys.indexes i
                         JOIN [数据库].sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
                         JOIN [数据库].sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
                         JOIN [数据库].sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
                         JOIN  [数据库].sys.partitions p ON dds.destination_id = p.partition_number
                                                  AND p.object_id = i.object_id
                                                  AND p.index_id = i.index_id
                         JOIN  [数据库].sys.partition_functions pf ON ps.function_id = pf.function_id
                         LEFT JOIN  [数据库].sys.Partition_Range_values v ON pf.function_id = v.function_id
                                                               AND v.boundary_id = p.partition_number
                                                               - pf.boundary_value_on_right
                 WHERE   i.object_id = OBJECT_ID(''[数据库]..表'')
                         AND i.index_id IN ( 0, 1 )
                 ORDER BY p.partition_number')

Answer 7

还是不行

SELECT  *
FROM    OPENQUERY([xxx],
                  N'SELECT  CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme ,
                         p.partition_number ,
                         CONVERT(VARCHAR(MAX), ds2.name) AS filegroup ,
                         CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary ,
                         STR(p.rows, 9) AS rows ,
                         GETDATE()
                 FROM    [Barefoot.Archives].sys.indexes i
                         JOIN [Barefoot.Archives].sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
                         JOIN [Barefoot.Archives].sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
                         JOIN [Barefoot.Archives].sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
                         JOIN [Barefoot.Archives].sys.partitions p ON dds.destination_id = p.partition_number
                                                  AND p.object_id = i.object_id
                                                  AND p.index_id = i.index_id
                         JOIN  [Barefoot.Archives].sys.partition_functions pf ON ps.function_id = pf.function_id
                         LEFT JOIN  [Barefoot.Archives].sys.Partition_Range_values v ON pf.function_id = v.function_id
                                                               AND v.boundary_id = p.partition_number
                                                               - pf.boundary_value_on_right
                 WHERE   i.object_id = OBJECT_ID(''Archives'')
                         AND i.index_id IN ( 0, 1 )
                 ORDER BY p.partition_number')

Answer 8

这个错误提示很明显了, 单引号的问题

CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary ,

这句里面的单引号, 变成双份的, 也就是

CONVERT(VARCHAR(MAX), ISNULL(v.value, ''''), 120) AS range_boundary ,

Answer 9

另外, 除非你链接服务器连接的默认数据库是你查询中指定的 [Barefoot.Archives]

否则, 你应该为 OBJECT_ID(''Archives'') 指定数据库名 object_id('数据库名.架构名.表名')

Answer 10

看漏了

SELECT  *
FROM    OPENQUERY([xxx],
                  N'SELECT  CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme ,
                         p.partition_number ,
                         CONVERT(VARCHAR(MAX), ds2.name) AS filegroup ,
                         CONVERT(VARCHAR(MAX), ISNULL(v.value, ''''), 120) AS range_boundary  ,
                         STR(p.rows, 9) AS rows ,
                         GETDATE()
                 FROM    [Barefoot.Archives].sys.indexes i
                         JOIN [Barefoot.Archives].sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
                         JOIN [Barefoot.Archives].sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
                         JOIN [Barefoot.Archives].sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
                         JOIN [Barefoot.Archives].sys.partitions p ON dds.destination_id = p.partition_number
                                                  AND p.object_id = i.object_id
                                                  AND p.index_id = i.index_id
                         JOIN  [Barefoot.Archives].sys.partition_functions pf ON ps.function_id = pf.function_id
                         LEFT JOIN  [Barefoot.Archives].sys.Partition_Range_values v ON pf.function_id = v.function_id
                                                               AND v.boundary_id = p.partition_number
                                                               - pf.boundary_value_on_right
                 WHERE   i.object_id = OBJECT_ID(''[Barefoot.Archives]..[xx]'')
                         AND i.index_id IN ( 0, 1 )
                 ORDER BY p.partition_number')