Help Needed: Capture result set of SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Help Needed: Capture result set of SP

Author

Topic

sureshchandrab
Starting Member

4 Posts

Posted - 2006-11-10 : 15:03:58

Hi,

I had SP and the o/p is XML stream. For my SP there is no o/p parameter. SP has, select XX,xx from YY for XML auto,elements. I need to grab the output with out modifying the SP. i can create dummy SP.
I tried to insert the o/p of SP to dummy table, but it was not allowed because select statement has for clause. Please help me how to grab the output stream. Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-13 : 09:04:28

well into what do you want to output it in?
front end app or in query analyzer/SSMS?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 09:05:07

It depends on which datatype you use.
And stream from a stored procedure?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-13 : 09:24:15

for xml returns data as image datatype in sql server 2000.
here you can see how to put text stored in image datatype into a ntext datatype:
http://groups.google.si/group/microsoft.public.sqlserver.programming/browse_thread/thread/1a40f3739f7a1b61/a062c097154de090?lnk=st&rnum=1&hl=sl#a062c097154de090

about the
"The FOR XML clause is not allowed in a INSERT statement." error
you can get the stored procedure result with a little help from a self-linked server:


use northwind
create procedure getXMLdata
as
select top 10 orderid, customerid from orders for XML auto,elements
go

if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

create table #temp(col1 image)
insert into #temp
select * from openquery(loopback, 'exec northwind.dbo.getXMLdata') 
select * from #temp
drop table #temp

go
drop proc getXMLdata

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

sureshchandrab
Starting Member

4 Posts

Posted - 2006-11-16 : 16:51:57

Thanks you guys, I tried spirit1 code but it is giving some hexadecimal values, i tested in SQL analyzer.

I need to call SP in Informatica tool. The limitation of Informatica is, you can pass input values and get ouput values through parameters only. There is no chance to modify the SP's according to Informatica limitation becuase those are tested and ready to prod. so i have to try some thing in SQL server database by using some temp tables or sp's. If i pass the SP's XML o/p to some temp table it will be easy for me. thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources