转:http://topic.csdn.net/t/20040428/12/3016902.html
如果能够方便的得到存储过程结果集的表结构。那存储过程的使用就方便了很多了。比如:
insert into #tmp exec sp_who
要执行这一句,前提必须知道sp_who的结果集的表结构。
这样的语句又不能运行:select * into #tmp from exec sp_who
所以不知道大家对这样的问题怎么处理,欢迎发言,来者有分!
SELECT a. *
FROM OPENROWSET ( ' SQLOLEDB ' , ' 127.0.0.1 ' ; ' sa ' ; '' ,
' SET FMTONLY OFF exec KPOS..sp_helptext '' USP_U_UpdateOneOrder ''' ) AS a
-- 关键在SET FMTONLY OFF
解决这个问题,推荐使用OpenQuery或OPENROWSET的方法.
首先要说的是,这是一种非常规的方法,有一些性能上的缺陷.
OpenQuery,OPENROWSET允许用户在链接服务器上查询.通过这种方法来得到查询的结果集.
1.在创建存储过程中,必须设置
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
(在查询分析器中执行,将默认激活这些设置)
2.定义链接服务器(必须有sysadmin权限)
exec sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
@provider = 'SQLOLEDB', @datasrc = @@servername
@server是自定义链接服务器的名称
(如果不指定,将默认为master)
3.这个时候就可以使用
eg:SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC MyStoreProc')
来得到存储过程返回的结果集.
3.1
但是存储过程MyStoreProc不能访问临时表,
eg:SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC pubs..sp_fkeys authors')
将报以下错误:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#fkeysall'.
#fkeysall是在存储过程中使用到的临时表
3.2
如果使用了临时表,必须如下调用
SELECT * FROM OPENQUERY(LOCALSERVER,'SET FMTONLY OFF EXEC pubs..sp_fkeys authors')
通常来说,OPENQUERY只是作为一个快捷的远程数据库访问,它必须跟在select后面,也就是说需要返回一个recordset.
而加上set fmtonly off用来屏蔽默认的只返回列信息的设置之后
select * from openrowset(sqloledb,server;sa;,set fmtonly off
exec ...)
,这样返回的output集合就会提交给前面的select显示。
如果采用默认设置,会返回空集合导致select出错,命令也就无法执行了
--------------------------------------------------------------------------------------------------------------
30 楼pbsql(风云)回复于 2004-04-29 10:12:53 得分 5
Yang_(扬帆破浪)、j9988(j9988)、zjcxc(邹建):
还是会报错啊,测试如下:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t]
GO
create table t(name varchar(10))
insert into t (name) values('a')
insert into t (name) values('b')
insert into t (name) values('c')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_1]
GO
CREATE PROCEDURE [dbo].[sp_1] @name varchar(10)
AS
--select * from t where name=@name--不用临时表没有问题
--return
select identity(int,1,1) id,* into #tem from t where name=@name
select * from #tem--用了临时表就报错
drop table #tem
GO
select * into #z from OPENROWSET(
'SQLOLEDB',
'SERVER=server;uid=sa;pwd=123;Database=test','SET FMTONLY OFF
exec sp_1 ''a''') as a
select * from #z
drop table #z
加了SET FMTONLY OFF还是报错:
服务器: 消息 7357,级别 16,状态 1,行 2
未能处理对象 'SET FMTONLY OFF
exec sp_1 'a''。OLE DB 提供程序 'SQLOLEDB' 指出该对象中没有任何列。
是不是还有别的原因?
31 楼zjcxc(邹建)回复于 2004-04-29 10:45:50 得分 10
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t]
GO
create table t(name varchar(10))
insert into t (name) values('a')
insert into t (name) values('b')
insert into t (name) values('c')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_1]
GO
CREATE PROCEDURE [dbo].[sp_1] @name varchar(10)
AS
set nocount on --加上这个,防止其他信息影响
--select * from t where name=@name--不用临时表没有问题
--return
select identity(int,1,1) id,* into #tem from t where name=@name
select * from #tem--用了临时表就报错
drop table #tem
GO
select * into #z from OPENROWSET(
'SQLOLEDB',
'SERVER=zj;uid=sa;pwd=;Database=northwind','SET FMTONLY off;exec sp_1 ''a''') as a
select * from #z
drop table #z
go
--删除测试
drop proc sp_1
drop table t