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

Source: Internet
Author: User
Tags ole

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


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.