January 9, 2007 at 9:17 am
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
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
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
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
Can you print the @string command and post it here?
What error are you getting when executing?
January 9, 2007 at 9:29 am
In case you missed that message?!?!
I'd really like to have those answers.
January 9, 2007 at 9:34 am
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
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://www.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
I knew I was missing something... Check this version of the code... maybe that'll solve it :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=99415#bm99871
January 9, 2007 at 10:06 am
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
i am using sql server 2000...so not sure i can use table variable..
January 9, 2007 at 10:21 am
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
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
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
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