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