Post reply

Help: Ways of persisting the resultset from SP?

  • 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 12 posts - 1 through 13 (of 13 total)

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