How to populate Temp table from Stored Procedure

  • rkordonsky 63916

    Mr or Mrs. 500

    Points: 587

    I am trying to put the result of Stored Procedure execution into Temp Table:

    SELECT *
    INTO #tmpTable
    FROM
    OPENROWSET('SQLNCLI','Server=(local)\SQL2014;Trusted_Connection=yes;','EXEC myDB.dbo.sp_write_Data @Start_Date=N2020-01-01, @End_Date=N2020-01-22')

    select * from #tmpTable

    I received the following errors:

    OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

    Msg -1, Level 16, State 1, Line 0

    SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    It means I am doing something wrong. I replaced (local) by actual Server Name and got the same errors. Please tell me what am I doing wrong? Thank you

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    Try replacing "(local)" with ".".  Is (local)\SQL2014 the same instance you're running this code on?

    John

  • rkordonsky 63916

    Mr or Mrs. 500

    Points: 587

    Yes it is

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    OK, good.  Did my suggestion work?  Can I ask why you're using OPENROWSET instead of just running the EXEC statement?

    By the way, I think you need to enclose your date values in quotes.  If you're using OPENROWSET you'll also need to escape the quotes by doubling them, since the whole statement is already enclosed in quotes.

    John

  • rkordonsky 63916

    Mr or Mrs. 500

    Points: 587

    Thank you John. I am wondering why  should I specify my version of SQL Server or it should be the Database Name?

  • Phil Parkin

    SSC Guru

    Points: 243998

    rkordonsky 63916 wrote:

    Thank you John. I am wondering why  should I specify my version of SQL Server or it should be the Database Name?

    Why are you using OPENROWSET in this case?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    rkordonsky 63916 wrote:

    Thank you John. I am wondering why  should I specify my version of SQL Server or it should be the Database Name?

    You don't need to specify the database name in the connection string, since you've provided the full path to the stored procedure in your EXEC statement.  When you say version, I assume you mean the "SQL2014" in the connection string?  That is, I assume, the name of the instance, so yes, you do need to specify that.

    John

  • rkordonsky 63916

    Mr or Mrs. 500

    Points: 587

    Unfortunately it did not work.

    I tried this way before:

    SELECT *
    INTO #tmpTable
    FROM
    EXEC iMIS_MCLE_Prod.dbo.sp_isg_write_Firm_Data @Start_Date=N2020-01-01, @End_Date=N2020-01-22

    and I received this error:

    Msg 156, Level 15, State 1, Line 12

    Incorrect syntax near the keyword 'EXEC'.

    Msg 102, Level 15, State 1, Line 12

    Incorrect syntax near '-'.

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    You need to create the temp table first, then use an INSERT...EXEC to insert the rows.  That applies whether you use OPENROWSET or not.  And don't forget to put those quotes in like I suggested earlier.

    John

  • rkordonsky 63916

    Mr or Mrs. 500

    Points: 587

    I created temp table and put single quotations:

    SELECT *
    INTO #tmpTable
    FROM
    EXEC iMIS_MCLE_Prod.dbo.sp_isg_write_Firm_Data @Start_Date=N'2020-01-01', @End_Date=N'2020-01-22'

    Msg 156, Level 15, State 1, Line 12

    Incorrect syntax near the keyword 'EXEC'.

  • Phil Parkin

    SSC Guru

    Points: 243998

    Use INSERT rather than SELECT ... INTO

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • rkordonsky 63916

    Mr or Mrs. 500

    Points: 587

    Insert INTO does work. Thank you.

Viewing 12 posts - 1 through 12 (of 12 total)

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