Retrieving the results of a stored procedure - nested procedures

Question

Post reply

Retrieving the results of a stored procedure - nested procedures

Andrew Westgarth

Mr or Mrs. 500

Points: 576
More actions
July 7, 2005 at 7:07 am

#68153

Hi,
   I have a procedure which needs to pull in the results of a another stored procedure (from a linked server) into a temp table.
The general syntax looks a little like this
INSERT INTO
  #tempSearch
  (
  [sort_order],
  [rank],
  [app_code],
  [application],
  [path],
  [filename],
  [data_pk],
  [summary],
  [authorised_date],
  [audience]
&nbsp
execute websearch @searchPhrase

Can anyone help me in writing the correct syntax which will pull the information from the returned recordset and puts it into the table?
Thanks
Andrew

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

That should be it... Do you get an error when executing this?

Andrew Westgarth Mr or Mrs. 500 Points: 576 More actions · Answer 2

hi the query is

Declare @searchPhrase nvarchar(4000)

SET @searchPhrase = 'Select * From openQuery(WebIndex, ''Select Characterization, Path, DocTitle from Scope() Where FREETEXT(Contents,''''council'''') ORDER BY Rank'')'

INSERT INTO

#tempSearch

(

[sort_order],

[rank],

[app_code],

[application],

[path],

[filename],

[data_pk],

[summary],

[authorised_date],

[audience]

&nbsp

execute websearch @searchPhrase

the error message is

Server: Msg 7391, Level 16, State 1, Procedure websearch, Line 7

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Can't you try it like this?

Select * fro LinkedServerName.DbName.dbo.TableName where ...

Andrew Westgarth Mr or Mrs. 500 Points: 576 More actions · Answer 4

no because it is a linked index server via a link to another sql server

SQL1 -> SQL-2 -> Index

Thr current method returns a recordset it's just I can't get it into a table to perform more sql on

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 5

It seems we have the same kind of problem - see Insert into problem with linked server.

What happens on your server if you enclose the call in a distributed transaction?

Andrew Westgarth Mr or Mrs. 500 Points: 576 More actions · Answer 6

I get the same error - I am looking towards a firewall issue seeing as the linked server is in a DMZ