none
Help! I am trying to copy the results from SQL Stored procedure to a temp table but i have not been able to do so. . Below is the code and error.

    Question

  • SELECT * INTO #TempTable EXEC [dbo].[NA_WEB_Application_Availibility]
    @InitialDate = '2014-07-07 00:00:00.000',
    @FinalDate = '2014-07-07 05:00:00.000'

    -----------------------------------------Error---------------------------------------------

    Msg 263, Level 16, State 1, Line 1
    Must specify table to select from.
    Msg 1038, Level 15, State 5, Line 1
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

    Monday, July 07, 2014 3:37 PM

Answers

  • You can use EXEC with SELECT like this

    for that you need to use either of the two approaches

    1. Create temporary table prior to EXEC call with same structure as EXEC output

    Then use

    INSERT INTO #TempTable
     EXEC [dbo].[NA_WEB_Application_Availibility]
    @InitialDate = '2014-07-07 00:00:00.000',
    @FinalDate = '2014-07-07 05:00:00.000'

    2. If you want table to be created on the fly then use this

    SELECT * INTO #TempTable 
    FROM OPENROWSET('SQLOLEDB','Data Source=Server_name;Trusted_Connection=yes;
              Integrated Security=SSPI','EXEC [dbo].[NA_WEB_Application_Availibility] @InitialDate = ''2014-07-07 00:00:00.000'', @FinalDate = ''2014-07-07 05:00:00.000'')'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, July 07, 2014 4:14 PM
  • Looks like OPENROWSET command in my company is listed as an unsecured command but i found a way around. Thanks Man. 
    Monday, July 07, 2014 6:13 PM

All replies

  • You can use EXEC with SELECT like this

    for that you need to use either of the two approaches

    1. Create temporary table prior to EXEC call with same structure as EXEC output

    Then use

    INSERT INTO #TempTable
     EXEC [dbo].[NA_WEB_Application_Availibility]
    @InitialDate = '2014-07-07 00:00:00.000',
    @FinalDate = '2014-07-07 05:00:00.000'

    2. If you want table to be created on the fly then use this

    SELECT * INTO #TempTable 
    FROM OPENROWSET('SQLOLEDB','Data Source=Server_name;Trusted_Connection=yes;
              Integrated Security=SSPI','EXEC [dbo].[NA_WEB_Application_Availibility] @InitialDate = ''2014-07-07 00:00:00.000'', @FinalDate = ''2014-07-07 05:00:00.000'')'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, July 07, 2014 4:14 PM
  • Looks like OPENROWSET command in my company is listed as an unsecured command but i found a way around. Thanks Man. 
    Monday, July 07, 2014 6:13 PM