转:使用OpenQuery或OPENROWSET的方法

转: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

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/flyskylf/archive/2007/12/14/1937523.aspx