Post reply

How to SELECT * INTO [temp table] FROM [Stored Procedure]?

  • July 21, 2017 at 6:52 am

    #331891

    I am learning "How to SELECT * INTO [temp table] FROM [Stored Procedure]"

    SELECT * INTO #TempSpWho2
    FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')

    But got an error said

    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'
    because this component is turned off as part of the security configuration for this server.
    A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
    For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

    Is there another way to do it? Or, need IT guy to remove security configuration for SQL server?

  • July 21, 2017 at 7:40 am

    #1952084

    You're going to need a sysadmin to something for you, whether that's enabling ad hoc distributed queries, or setting up a linked server object.  There are other options, as long as you're happy to go outside of T-SQL, such as SSIS, the import/Export wizard, bcp and so on.

    John

  • July 21, 2017 at 7:44 am

    #1952086

    Also, sp_who2 returns 2 columns named "SPID" and that will cause an error during the SELECT INTO.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply