SQL Server SELECT INTO Using OPENROWSET With Variables?

Question

I have a temp table where I want to store the results of a stored procedure. To execute the stored procedure, I need to use variable values for the Database name, Database Server, and Stored Procedure name that are stored in a table. I need to work out how to write the SELECT * INTO using the OPENROWSET with my variables. This is what I have so far but there are syntax errors:

SELECT * INTO #tmpAccountsRetrieved 
    FROM OPENROWSET(@TempDbName, 'Server=' + @TempDbServer + ';Trusted_Connection=yes;', 'EXEC ' + @TempStoredProcName)

I'm pretty rusty on SQL so any help is much appreciated!

Could you provide the syntax errors that you encountered. Also not sure what you are passing in for **`@TempDbName`**, as per `OPENROWSET` it should be `provider name`. Also you cannot use expressions in `OPENROWSET`.Try creating a dynamic sql to pass the parameters. — Abhishek, Sep 08 '15 at 14:45

score 0 · Accepted Answer · edited May 23 '17 at 11:58

0

Try to put these configurations in place before using openRowset:

   -- FOR USING OPENROWSETS
        EXEC sp_configure 'Ad Hoc Distributed Queries'
            ,1
        RECONFIGURE

I think your syntax is correct. So just Try to Go through this POST for more help

edited May 23 '17 at 11:58

Community

1
1

answered Sep 08 '15 at 15:31

ProblemSolver

636
1
8
16

Thank you, this is exactly what I needed. – sheldor Sep 09 '15 at 13:12

SQL Server SELECT INTO Using OPENROWSET With Variables?

1 Answers1