|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
calling a remote SP call that returns a cursorHi,
a SP in my database is calling a SP in external database using a linked server. ie ni my SP: exec YourLink.YourDB.dbo.GetResultsSP @CursorReturn out the SP in the external database must return me a result set OR cursor. I have having issues reading the result set from my SP so have tried implementing logic as a cursor. I get error to do with RPC and cursors not allowed. Is there any way I can get this cursor back to my SP if it is returned as just a default result set from the external SP? or if not then as a defined cursor? Any help would be apprecated, Steve I read this post after reading your later on.
You could try returning table datatype from a user defined function from your remote procedure call. In your calling procedure, you could get resultset from the table datatype returned from user defined function. Please let me know if I misunderstood your question or you need more help on this. Show quote "Steve" wrote: > Hi, > a SP in my database is calling a SP in external database using a linked > server. > ie ni my SP: exec YourLink.YourDB.dbo.GetResultsSP @CursorReturn out > > the SP in the external database must return me a result set OR cursor. I > have having issues reading the result set from my SP so have tried > implementing logic as a cursor. I get error to do with RPC and cursors not > allowed. > > Is there any way I can get this cursor back to my SP if it is returned as > just a default result set from the external SP? or if not then as a defined > cursor? > > Any help would be apprecated, > Steve Hi,
it is a 3rd party vendor db and they have "exposed" a public SP that I can call to get all time and attendance activity for a period of time that I pass as parameters. The issue is that the vendor SP returns the rows as a result set of the SP and I am finding it difficult to load these into my SP for processing. I could potentially contact the vendor and ask them to make some changes but I need to be clear on what I need them to do. thanks, Steve Show quote "ZULFIQAR SYED" wrote: > I read this post after reading your later on. > > You could try returning table datatype from a user defined function from > your remote procedure call. > > In your calling procedure, you could get resultset from the table datatype > returned from user defined function. > > Please let me know if I misunderstood your question or you need more help on > this. > -- > http://zulfiqar.typepad.com > BSEE, MCP > > > "Steve" wrote: > > > Hi, > > a SP in my database is calling a SP in external database using a linked > > server. > > ie ni my SP: exec YourLink.YourDB.dbo.GetResultsSP @CursorReturn out > > > > the SP in the external database must return me a result set OR cursor. I > > have having issues reading the result set from my SP so have tried > > implementing logic as a cursor. I get error to do with RPC and cursors not > > allowed. > > > > Is there any way I can get this cursor back to my SP if it is returned as > > just a default result set from the external SP? or if not then as a defined > > cursor? > > > > Any help would be apprecated, > > Steve Here I got some of the code from BOL (books online) to give you an example
on how to populate a table from a SP resultset. HTH.. use pubs go drop table author_sales go CREATE TABLE author_sales ( data_source varchar(20), au_id varchar(11), au_lname varchar(40), sales_dollars smallmoney ) GO -- ============================================= -- Create procedure basic template -- ============================================= -- creating the store procedure IF EXISTS (SELECT name FROM sysobjects WHERE name = N'mytestprocA' AND type = 'P') DROP PROCEDURE dbo.mytestprocA GO CREATE PROCEDURE dbo.mytestprocA AS INSERT author_sales EXECUTE get_author_sales select * from author_sales GO -- ============================================= -- example to execute the store procedure -- ============================================= EXECUTE dbo.mytestprocA GO Show quote "Steve" wrote: > Hi, > it is a 3rd party vendor db and they have "exposed" a public SP that I can > call to get all time and attendance activity for a period of time that I pass > as parameters. > The issue is that the vendor SP returns the rows as a result set of the SP > and I am finding it difficult to load these into my SP for processing. > I could potentially contact the vendor and ask them to make some changes but > I need to be clear on what I need them to do. > > thanks, > Steve > > "ZULFIQAR SYED" wrote: > > > I read this post after reading your later on. > > > > You could try returning table datatype from a user defined function from > > your remote procedure call. > > > > In your calling procedure, you could get resultset from the table datatype > > returned from user defined function. > > > > Please let me know if I misunderstood your question or you need more help on > > this. > > -- > > http://zulfiqar.typepad.com > > BSEE, MCP > > > > > > "Steve" wrote: > > > > > Hi, > > > a SP in my database is calling a SP in external database using a linked > > > server. > > > ie ni my SP: exec YourLink.YourDB.dbo.GetResultsSP @CursorReturn out > > > > > > the SP in the external database must return me a result set OR cursor. I > > > have having issues reading the result set from my SP so have tried > > > implementing logic as a cursor. I get error to do with RPC and cursors not > > > allowed. > > > > > > Is there any way I can get this cursor back to my SP if it is returned as > > > just a default result set from the external SP? or if not then as a defined > > > cursor? > > > > > > Any help would be apprecated, > > > Steve  |
|||||||||||||||||||||||
Other interesting topics