Post reply

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

  • January 9, 2007 at 9:17 am

    #176421

    frnz i need a help..pretty urgent..

    I need to write a procedure which calls other procedures dynamically..and gets the result set in a temp table

    /**************************************************/

    create SPMain...

    @variableprocedure

    as

    begin

             HELP NEEDED HERE ---code to run the SP and get the result into a temp table.

    end

    @variableprocedure is an variable into which we can pass various procedures

    some execute stmts are,

    SPMain "sptest1 10,'Y'" --this returns 2 columns

    SPMain "sptest2 10,'Y','more'" -- this returns 3 columns

    Irrespective of number of column names, the temp table should be created dynamically.

    The SPs which are passed (sptest1,sptest2) can have temp tables implemented.

    please advice

     

  • January 9, 2007 at 9:21 am

    #682057

    i used this for the above problm

    declare @string varchar(1000)

    set @string = '' 

    set @string = ' select * into ##tmptesting FROM OPENROWSET("SQLOLEDB","ServerName";"UID";"PWD","' + @variableprocedure+ '") AS a '

     exec(@string)

     

    But this wont work when i have a temp table in the procedure i pass into the variable @variableprocedure....

  • January 9, 2007 at 9:21 am

    #682056

    Can we know the rest of the process?  I'm not sure you are using the right tool for the job.

  • January 9, 2007 at 9:24 am

    #682059

    well,we are not using a job as such..

     

    All i am trying to do is,get the result set of an SP and put into a temp table for further processing.

    In this case,the SP happens to be dynamic...So temp table cant be pre-defined.

    ....pls lemme know if v need more info..

  • January 9, 2007 at 9:24 am

    #682058

    Can you print the @string command and post it here?

    What error are you getting when executing?

  • January 9, 2007 at 9:29 am

    #682061

    In case you missed that message?!?!

    I'd really like to have those answers.

  • January 9, 2007 at 9:34 am

    #682063

    When i print @string this is wht i see

    select * into ##tmptesting FROM OPENROWSET("SQLOLEDB","ServerName";"UID";"PWD","sptest1 10,'Y'") AS a

    And the error i get is,

    Invalid object name '#tmpFirst'. Where #tmpFirst' is the temp table used in sptest1

    I also read somewhere that OPENROWSET doesnt allow temp tables....probably when we need to check for other option..

  • January 9, 2007 at 9:47 am

    #682067

    Never had that problem.  Have you tried " EXEC dbo.sptest1 10,'Y' "

    I found this old thread but I'm not sure it's gonna be much use to you at this point...

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=206787

     

    Continuing on your information... Can you make a new sp that uses a table variable instead and see if that will work?

  • January 9, 2007 at 9:49 am

    #682068

    I knew I was missing something... Check this version of the code... maybe that'll solve it :

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=99415#bm99871

  • January 9, 2007 at 10:06 am

    #682078

    Hello,my SP works fine if it doesnt have a temp table...Only problem is i cant pass a procedure to OPENROWSET command which has a temp table..

    meanwhile,i am trying if i can get somethng frm the post u gave me

     

    Thanks for the help so far

  • January 9, 2007 at 10:07 am

    #682080

    i am using sql server 2000...so not sure i can use table variable..

  • January 9, 2007 at 10:21 am

    #682088

    I don't know what else to tell you...

    this compiles fine on my computer :

    select a.* into ##tmptesting FROM OPENROWSET('SQLOLEDB','ServerName;UID;PWD','sptest1 10,''Y''') AS a

    But I have no way of executing it so for the moment there's not much more I can suggest.

     

    BTW I was refferring to a table variable inside the remote procedure. You just can't do select * into @TableVariable FROM...   I guess I misunderstood you on that part.

     

    BTW.  What happens if you execute the result of the print statement you posted directly in QA?

  • January 9, 2007 at 10:31 am

    #682092

    wen i run the printed statement

    select * into ##tmptest FROM OPENROWSET("SQLOLEDB","Server";"sa";"PWD","SPTest 9661,'N'") AS a

     

    i still get

    Server: Msg 208, Level 16, State 1, Line 2

    Invalid object name '#tmp'.

    #tmp is the temp table in SPTest

  • January 9, 2007 at 10:37 am

    #682095

    Have you tried the 2nd version of the code (2nd link, 5th message).

     

    You'll nothing that he is using set fmtonly on.  I don't remember why exactly he used it but IIRC it was to work around a bug similar to yours.

    Also you might want to convert that remote sp to use a table variable just to see if it works that way... assuming you wouldn't have to rewrite a lot of other sps, it might be a workable solution.

  • January 9, 2007 at 11:58 am

    #682132

    Thanks Dear frnd..ii tried it.but get the below error

    Server: Msg 7357, Level 16, State 1, Line 2

    Could not process object 'SET FMTONLY OFF exec dbo.spPAWTest2 9661,'N''. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

    OLE DB error trace [Non-interface error:  OLE DB provider unable to process the objectroviderName='SQLOLEDB', Query=SET FMTONLY OFF exec dbo.spPAWTest2 9661,'N''].

    The statmt i used is..

    select * into ##tmptest FROM OPENROWSET("SQLOLEDB","Server";"sa";"PWD","SET FMTONLY OFF exec dbo.SPTest 9661,'N'") AS a

     

    pls advice..if any one can solve it.

Viewing 15 posts - 1 through 15 (of 24 total)

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