To avoid creating a table, execute a stored procedure to insert a temporary table

In general, when we want to insert the result set of a stored procedure into a temporary table, the following steps are required

Create table #temptable (column) ....

INSERT INTO #temptable

EXEC YOURSP

This is cumbersome, and if the result set has many columns, it is more painful.

Introduce a flexible approach today

The script is as follows:

exec sp_configure'Show advanced Options',1reconfigureexec sp_configure'Ad Hoc Distributed Queries',1Reconfigure--SET fmtonly OFF IF object_id ('tempdb: #t') is not a NULL DROP TABLE #tGOSELECT*Into #tFROM OPENROWSET ('SQLNCLI10','Driver={sql Server}; Server=dbserver; Uid=userid; Pwd=password;initial Catalog=dbtrain','exec dbtrain. Sp_depends Multiresult') SELECT* FROM #t

This allows you to insert the result set into the staging table without the need to create a new table structure.

It's not over yet, if we execute the following statement

EXEC (' DBCC IND (dbtrain,department8,-1) ')

If it is normal to execute in Transact-SQL, but put in OpenRowset,

The error message will appear:

MSG 7357, Level A, state 2, line 2
Cannot process the object "EXEC (' DBCC IND (dbtrain,department8,-1) ')". The OLE DB provider "SQLNCLI10" for linked server "(NULL)" indicates that either the object have no columns or the current User does not has permissions on this object.

To solve this problem, we need to use

SET fmtonly OFF;

 select  *    #t  from  openrowset  ( sqlncli10   ' ,  " driver={sql Server}; server=shasapp62; Uid=sasalesbudget; Pwd=sanofi2011;initial catalog=dbtrain  ,    SET fmtonly OFF; EXEC (  " dbcc IND (dbtrain,department8,- 1)   )  ) 

When SET fmtonly is on, the row is not processed, and the row is not sent as the result of the request to the client, only metadata describing the column information is returned;

In OLE DB source, it is necessary to explicitly set fmtonly = OFF to return the result row to the client

To avoid creating a table, execute a stored procedure to insert a temporary table