轉:使用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