Post reply

Help: Ways of persisting the resultset from SP?

  • November 5, 2003 at 7:04 pm

    #176935

    is there anyway to insert the results from a stored procedure into a table/temptable

    without first knowing what's the columns returned.

    i know it works by first creating a temptable, but this only works if i know the columns.

    I am looking at something like

    Select (exec sp) into #temptable.

    i am actually trying to call a system stored procedure and i want to keep the results in a table from a stored procedure itself.

    Any help will be greatly appreciated

  • November 6, 2003 at 3:54 am

    #481026

    you could use a select into ...

    SELECT *

    INTO #WrkTable

    FROM OPENROWSET('SQLOLEDB','server';'user';'pass','master..sp_who')

    This would fail with sp's that return more than 1 result set as output

  • November 6, 2003 at 4:00 am

    #481027

    quote:

    This would fail with sp's that return more than 1 result set as output

    And those that use temp tables, e.g. sp_helpdb

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 6, 2003 at 4:14 am

    #481030

    Sorry ,

    yes that is correct , you should probably use "SET FMTONLY OFF EXEC master..sp_who" , this would avoid the temp table problem

  • November 18, 2003 at 5:39 pm

    #482530

    if it's not too much i would like ur help again, the openrowset or openquery doesn't work properly on this system sp

    sp_sproc_columns, it keeps giving me error

    Syntax error converting the nvarchar value '%' to a column of data type int.

    PLS help me thanks!

  • November 18, 2003 at 11:55 pm

    #482543

    try this

    SELECT *

    INTO #WrkTable

    FROM OPENROWSET('SQLOLEDB','server';'user';'pass','SET FMTONLY OFF EXEC master..sp_sproc_columns')

  • November 19, 2003 at 12:35 am

    #482551

    hi GCN, thanks for ur help, but this is not what i wanted, i want the recordset not the columns only

  • November 19, 2003 at 12:58 am

    #482555

    Hi , this should return the recordset not just columns

  • November 19, 2003 at 6:53 am

    #482622

    Try 

    

    -- Generic example ...

    If Object_ID('TempDB..#Temp') Is Not NULL Drop Table #Temp

    

    select * Into #Temp from OpenRowset('SQLOLEDB', 

    'Server=(local);Trusted_Connection=yes', 

    'Exec Master.dbo.sp_help ')

    

    Select * from #Temp

    

    If Object_ID('TempDB..#Temp') Is Not NULL Drop Table #Temp

    This returns a record set. Some issues though:

    If SP deals with #Temp tables, results will not be returned.

    Also may try 'SET FMTONLY OFF EXEC theSP'.

    SP should have SET NOCOUNT ON, and no PRINTs ect. for best results.



    Once you understand the BITs, all the pieces come together

  • November 19, 2003 at 3:33 pm

    #482760

    You can select the results of the procedure into a global temp table.

    create proc #temp as

    select *

    into ##so

    from sysobjects

    go

    I can then

    exec #temp

    select * from ##so

    drop table ##so

    Brian

  • November 19, 2003 at 5:34 pm

    #482773

    this query doesn't work if i pass in the parameters(example a sp name).. any ideas? it just return no recordsets..

    but if i were to run just EXEC master..sp_sproc_columns spname

    it will return a recordset

    SELECT *

    INTO #WrkTable

    FROM OPENROWSET('SQLOLEDB','server';'user';'pass','SET FMTONLY OFF EXEC master..sp_sproc_columns spname')

  • November 19, 2003 at 11:49 pm

    #482787

    try replacing <master> with whatever database that has sp_name...

    'SET FMTONLY OFF EXEC MyDatabase..sp_sproc_columns spname'

  • November 20, 2003 at 1:58 am

    #482798

    it works!!! thanks..

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

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