IF OBJECT_ID('ta','u') IS NOT NULL DROP TABLE ta GO IF OBJECT_ID('pp','p') IS NOT NULL DROP PROC pp GO CREATE TABLE ta (id INT,name VARCHAR(10)) GO CREATE PROC pp AS SELECT * FROM ta GO/* 假设以上过程使用的表和选择的列都不知道的。 上面只是用来模拟测试 */SELECT a.* INTO tx FROM OPENROWSET('SQLOLEDB','.';'fc';'bd5178', 'EXEC db2.dbo.pp') AS a GOSELECT name FROM syscolumns WHERE id=OBJECT_ID('tx','u') /* id name*/ GODROP TABLE tx GO
这样试下。 USE test GO IF OBJECT_ID('ta','u') IS NOT NULL DROP TABLE ta GO IF OBJECT_ID('pp','p') IS NOT NULL DROP PROC pp GO CREATE TABLE ta (id INT,name VARCHAR(10)) GO INSERT ta SELECT 1,'aa' GO CREATE PROC pp AS SELECT * FROM ta GO/* 假设以上过程使用的表和选择的列都不知道的。 上面只是用来模拟测试 */ SELECT b.v FROM ( SELECT x=(SELECT TOP 1 * FROM OPENROWSET('SQLOLEDB','.\sqlexpress';'sa';'bd5178', 'EXEC test.dbo.pp') AS a FOR XML PATH(''),TYPE ) ) a CROSS APPLY ( SELECT v=x.value('local-name(.)','varchar(100)') FROM x.nodes('/*') AS t(x) ) b/* id name */ GO
SELECT a.* INTO tx FROM OPENROWSET('SQLOLEDB','.';'fc';'bd5178', 'EXEC db2.dbo.pp') AS a GOSELECT name FROM syscolumns WHERE id=OBJECT_ID('tx','u') --這個不就是返回字段列表?
USE test GO IF OBJECT_ID('ta','u') IS NOT NULL DROP TABLE ta GO IF OBJECT_ID('pp','p') IS NOT NULL DROP PROC pp GO CREATE TABLE ta (id INT,name VARCHAR(10)) GO INSERT ta SELECT 1,'aa' GO CREATE PROC pp AS SELECT * FROM ta GOSELECT * FROM OPENROWSET('SQLOLEDB','.\sqlexpress';'sa';'bd5178','EXEC test.dbo.pp') AS a WHERE 1=2/* id name ----------*/ GO
这个需要通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'功能, 一般不建议这么做,性能比较低。对这类问题似乎没有很好的解决方案。 建议LZ改变一下思路,比如用一个表值函数来代替存储过程, 或者创建相应字段的临时表来接收存储过程返回的结果集。
DROP TABLE ta
GO
IF OBJECT_ID('pp','p') IS NOT NULL
DROP PROC pp
GO
CREATE TABLE ta (id INT,name VARCHAR(10))
GO
CREATE PROC pp
AS
SELECT * FROM ta
GO/*
假设以上过程使用的表和选择的列都不知道的。
上面只是用来模拟测试
*/SELECT a.* INTO tx
FROM OPENROWSET('SQLOLEDB','.';'fc';'bd5178',
'EXEC db2.dbo.pp') AS a
GOSELECT name FROM syscolumns WHERE id=OBJECT_ID('tx','u')
/*
id
name*/
GODROP TABLE tx
GO
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html
USE test
GO
IF OBJECT_ID('ta','u') IS NOT NULL
DROP TABLE ta
GO
IF OBJECT_ID('pp','p') IS NOT NULL
DROP PROC pp
GO
CREATE TABLE ta (id INT,name VARCHAR(10))
GO
INSERT ta SELECT 1,'aa'
GO
CREATE PROC pp
AS
SELECT * FROM ta
GO/*
假设以上过程使用的表和选择的列都不知道的。
上面只是用来模拟测试
*/
SELECT b.v FROM
(
SELECT x=(SELECT TOP 1 * FROM OPENROWSET('SQLOLEDB','.\sqlexpress';'sa';'bd5178',
'EXEC test.dbo.pp') AS a
FOR XML PATH(''),TYPE
)
) a
CROSS APPLY
(
SELECT v=x.value('local-name(.)','varchar(100)') FROM x.nodes('/*') AS t(x)
) b/*
id
name
*/
GO
FROM OPENROWSET('SQLOLEDB','.';'fc';'bd5178',
'EXEC db2.dbo.pp') AS a
GOSELECT name FROM syscolumns WHERE id=OBJECT_ID('tx','u')
--這個不就是返回字段列表?
GO
IF OBJECT_ID('ta','u') IS NOT NULL
DROP TABLE ta
GO
IF OBJECT_ID('pp','p') IS NOT NULL
DROP PROC pp
GO
CREATE TABLE ta (id INT,name VARCHAR(10))
GO
INSERT ta SELECT 1,'aa'
GO
CREATE PROC pp
AS
SELECT * FROM ta
GOSELECT *
FROM OPENROWSET('SQLOLEDB','.\sqlexpress';'sa';'bd5178','EXEC test.dbo.pp') AS a
WHERE 1=2/*
id name
----------*/
GO
一般不建议这么做,性能比较低。对这类问题似乎没有很好的解决方案。
建议LZ改变一下思路,比如用一个表值函数来代替存储过程,
或者创建相应字段的临时表来接收存储过程返回的结果集。