Need help please... SP,temp tables...

  • can someone tell me if i cant get the expected number of columns for an SP,with out actually running it..like the sql server internally compiles and tells us wht is the expected number of columns..

    if i get this,i will be able to break my logic and look for a different work arnd.

     

  • No you can't do that... because the sp can have more than 1 result set at the same time, also it can have different results set at each run.

     

    The only way I could figure out some sort of solution would be to use the same openrowset on the remote server into a local table.  Then search the tempdb..syscolumns table for the temp object.

  • ok, any idea abt this?

    "if i cant get the expected number of columns for an SP,with out actually running it"

    i explained this in previous post..if u hav missed it..

  • That's what I said.  You can't do that without running it.  Maybe you can do a general table with 100 varchar columns and insert into that... but that won't come without its load of problems.

     

    PS I e-mailed the expert of this site on remote query.  I just hope he'll be able to check it out soon.

  • Dear frnd,any luck with this?

  • Not really.  The guy I e-mail didn't even read the message yet and there's nothing else I can think of so looks like you are on your own at the moment.

  • hm..ok ..i will try for some workaround.

    Thanks anywyz..

  • Hi,

     

    The INSERT command supports calling a stored procedure to supply rows for insertion into a table

    CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname NULL,

    indid int, type char(4), resource char(15), mode char(10), status char(6))

    INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status)

    EXEC dbo.sp_lock

    SELECT * FROM #locks

    DROP TABLE #locks

    This is a handy way of trapping the output of a stored procedure in a table so that you can manipulate it or retain it for later use. Prior to the advent of cursor OUTPUT parameters, this was the only way to perform further work on a stored procedure's result set within Transact-SQL.

    Note that INSERT...EXEC works with extended procedures that return result sets as well

    Reference  for detail information

    http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=6&rl=1

    Hope this will help you.

    Redards,

    Baliram Suryawanshi

     


    Regards,

    Baliram Suryawanshi

  • @Baliram.

    thanks for u r suggestion...But the SP that i execute is dynamic..

    So before executing we really dont know wht would be the number of columns..

    so temp table cannot be created..prior to execution..

    in u r example, EXEC dbo.sp_lock..

    sp_lock is dynamic for me..each time the SP may return any number of columns..

    lemme know if u have any solution

  • This is a "WORKING" example... do your thing and Enjoy!

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[proc1]

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[proc2]

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE procedure dbo.proc1 @param1 int, @param2 int

    as

    begin

    set nocount on

    create table #T1( data1 int, data2 int)

    insert into #T1 ( data1 , data2 )

    select top 10 @param1 , @param2

    from sysobjects a cross join sysobjects b

    select * from #T1

    end

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE procedure dbo.proc2 @param1 int, @param2 int, @param3 int

    as

    begin

    set nocount on

    create table #T2( data1 int, data2 int, data3 int)

    insert into #T2 ( data1 , data2, data3 )

    select top 10 @param1 , @param2, @param3

    from sysobjects a cross join sysobjects b

    select * from #T2

    end

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    IF OBJECT_ID('tempdb..##tmptesting') IS NOT NULL

    DROP TABLE ##tmptesting

    GO

    select * into ##tmptesting FROM OPENROWSET('SQLOLEDB','SERVERNAME';'UID';'PWD','SET FMTONLY OFF; exec devdba_data.dbo.proc1 1,1') AS a

    select * from ##tmptesting

    IF OBJECT_ID('tempdb..##tmptesting') IS NOT NULL

    DROP TABLE ##tmptesting

    GO

    select * into ##tmptesting FROM OPENROWSET('SQLOLEDB','SERVERNAME';'UID';'PWD','SET FMTONLY OFF; exec devdba_data.dbo.proc2 2,2,2') AS a

    select * from ##tmptesting

    Warning... What you are trying to do does *not* seems like a good design but that is a subject for another thread

    Cheers!


    * Noel

Viewing 10 posts - 16 through 24 (of 24 total)

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