SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Help Needed: Capture result set of SP
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

sureshchandrab
Starting Member

4 Posts

Posted - 11/10/2006 :  15:03:58  Show Profile  Visit sureshchandrab's Homepage
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

Slovenia
11750 Posts

Posted - 11/13/2006 :  09:04:28  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 11/13/2006 :  09:05:07  Show Profile  Visit SwePeso's Homepage
It depends on which datatype you use.
And stream from a stored procedure?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 11/13/2006 :  09:24:15  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

sureshchandrab
Starting Member

4 Posts

Posted - 11/16/2006 :  16:51:57  Show Profile  Visit sureshchandrab's Homepage
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000