Professional Web Applications Themes

INSERT INTO ... EXEC sp_who - Microsoft SQL / MS SQL Server

Hi, I know that i can store result of a stored procedure into a table with TSQL command like this: INSERT INTO tblWho EXEC sp_who The problem is INERT INTO needs a pre-created table matches with column name/type returened by stored procedure. Is there any command that create a table and stores stored procedure data into that table automatically? Thanks, Ali...

  1. #1

    Default INSERT INTO ... EXEC sp_who

    Hi,
    I know that i can store result of a stored procedure into a table with TSQL
    command like this:

    INSERT INTO tblWho EXEC sp_who

    The problem is INERT INTO needs a pre-created table matches with column
    name/type returened by stored procedure.

    Is there any command that create a table and stores stored procedure data
    into that table automatically?

    Thanks,
    Ali


    A.M Guest

  2. #2

    Default Re: INSERT INTO ... EXEC sp_who

    You can do:

    EXEC sp_serveroption 'Server', 'Data Access', TRUE
    GO
    SELECT *
    INTO #tbl
    FROM OPENQUERY(Server, 'EXEC sp_who')
    GO

    SELECT *
    FROM #tbl ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: INSERT INTO ... EXEC sp_who

    > Is there any command that create a table and stores stored procedure data 

    If you have a linked server (which can be the local server name), you can
    say:


    SELECT * INTO #who FROM OPENQUERY
    (
    <linked server name goes here>,
    'EXEC sp_who'
    )

    SELECT * FROM #who

    DROP TABLE #who


    Aaron Guest

  4. #4

    Default Re: INSERT INTO ... EXEC sp_who

    Why this one doesn't work :

    SELECT *
    FROM
    OPENQUERY(TORIGHTFAX, 'msdb..sp_help_job ')

    It returns:
    Server: Msg 7357, Level 16, State 2, Line 1
    Could not process object 'msdb..sp_help_job '. The OLE DB provider
    'SQLOLEDB' indicates that the object has no columns.


    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... [/ref]
    data 
    >
    > If you have a linked server (which can be the local server name), you can
    > say:
    >
    >
    > SELECT * INTO #who FROM OPENQUERY
    > (
    > <linked server name goes here>,
    > 'EXEC sp_who'
    > )
    >
    > SELECT * FROM #who
    >
    > DROP TABLE #who
    >
    >[/ref]


    A.M Guest

  5. #5

    Default Re: INSERT INTO ... EXEC sp_who

    You can use:

    SELECT *
    FROM OPENQUERY(Server, 'SET FMTONLY OFF; EXEC msdb..sp_help_job') ;

    The reason why it failed is, sp_help_job internally calls certain other
    stored procedure which has intermingled DDLs for #temp tables & DMLs. These
    #temp tables have different connection specific scope & late bound. Hence it
    will not be known until the #temp table is created. When FMTONLY is set to
    ON, the DDL will not get executed and this causes the error message about
    missing/no columns.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. INSERT...EXEC with multiple results sets
    By Dennis in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 13th, 05:37 PM
  2. Convert Exec (SQL) to Exec sp_executesql 'SQL'
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 9th, 10:20 PM
  3. help with sp_who
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 11th, 02:54 PM
  4. Manipulating the results of sp_who
    By Ioannis Demetriades in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 4th, 10:04 PM
  5. Replies: 0
    Last Post: December 10th, 09:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139