Rotating: Using OPENQUERY or OPENROWSET methods

Ext: http://topic.csdn.net/t/20040428/12/3016902.html

If you can easily get the table structure of the stored procedure result set. The use of the stored procedure is much more convenient. Like what:
INSERT INTO #tmp exec sp_who
To execute this sentence, the prerequisite must know the table structure of the sp_who result set.
Such statements cannot be run: SELECT * into #tmp the from exec sp_who
So I do not know how to deal with such a problem, welcome to speak, there are points. --Success Code
SELECT A. *
From OPENROWSET (' SQLOLEDB ', ' 127.0.0.1 '; ' Sa '; '' ,
' SET fmtonly off exec kpos. Sp_helptext ' Usp_u_updateoneorder ') as a

--Key to set Fmtonly off

To solve this problem, it is recommended to use OPENQUERY or OpenRowset method.
The first thing to say is that this is an unconventional approach with some performance flaws.
Openquery,openrowset allows users to query on linked servers. This method is used to get the result set of the query.
1. In the creation of a stored procedure, you must set the
SET ANSI_NULLS on
SET ansi_warnings on
(Executed in Query Analyzer, these settings will be activated by default)
2. Define a linked server (must have sysadmin permissions)
exec sp_addlinkedserver @server = ' LocalServer ', @srvproduct = ',
@provider = ' SQLOLEDB ', @datasrc = @ @servername
@server is the name of the custom linked server
(if not specified, the default is master)
3. This is the time to use
Eg:select * from OPENQUERY (LocalServer, ' EXEC mystoreproc ')
To get the result set returned by the stored procedure.
3.1
However, the stored procedure Mystoreproc cannot access the temporary table.
Eg:select * from OPENQUERY (localserver, ' EXEC pubs. Sp_fkeys authors ')
The following error will be reported:
Server:msg, level, State 1, line 1
Invalid object name ' #fkeysall '.
#fkeysall是在存储过程中使用到的临时表
3.2
If you are using a temporary table, you must call the
SELECT * from OPENQUERY (LocalServer, ' SET fmtonly off EXEC pubs. Sp_fkeys authors ')
Typically, OpenQuery is only accessed as a fast remote database and must follow the Select, which means that a recordset needs to be returned.
And with set fmtonly off to mask the default settings that only return column information
SELECT * FROM OPENROWSET (Sqloledb,server;sa;,set fmtonly off
exec ...)
So that the returned output collection is submitted to the previous select display.
If the default setting is used, an empty collection is returned that causes a select error and the command is not executed

--------------------------------------------------------------------------------------------------------------

30/F Pbsql (Wind and cloud) reply to 2004-04-29 10:12:53 score 5

Yang_ (sailing), j9988 (j9988), ZJCXC (Jiangjian):
Still have the error, the test is as follows:

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-no problem with temporary tables
--return
Select Identity (int,1,1) id,* into #tem the from T where Name= @name
SELECT * from #tem-errors are made with temporary tables
drop table #tem
Go

SELECT * Into #z the 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

Add set Fmtonly off or error:
Server: Message 7357, Level 16, State 1, line 2
Failed to process object ' SET fmtonly off
exec sp_1 ' a '. OLE DB Provider ' SQLOLEDB ' indicates that there are no columns in the object.

Is there another reason.

31/F ZJCXC (Jiangjian) reply to 2004-04-29 10:45:50 score 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--plus this prevents other information from affecting
--select * from T where name= @name-no problem with temporary tables
--return
Select Identity (int,1,1) id,* into #tem the from T where Name= @name
SELECT * from #tem-errors are made with temporary tables
drop table #tem
Go

SELECT * Into #z the From OPENROWSET (
' SQLOLEDB ',
' Server=zj;uid=sa;pwd=;D atabase=northwind ', ' SET fmtonly off;exec sp_1 ' ' a ') as a
SELECT * FROM #z
drop table #z
Go

--Delete test
drop proc Sp_1
drop table T