We have many situation where we run into this issue using an old "API" that used rowsets for results from stored procs.

Our solution, which does not require a connection string, is as follows:

use AdventureWorks2000

go

if exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P')

drop procedure dbo.proc_simple

go

create procedure dbo.proc_simple

as

begin

select LocationId, [Name], rowguid from dbo.Location

end

go

select * from openrowset('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=AdventureWorks2000', 'exec dbo.proc_simple')

go

-- Declare target variable

DECLARE @LocationID smallint;

-- Create Temp table to receive resultset (Table variables will not work)

CREATE TABLE #TmpProcSimple (

LocationIdsmalllint,

[Name] varchar(50),

rowguid uniqueidentifier

)

-- Execute into Temp table

INSERT INTO #TmpProcSimple

EXECUTE dbo.proc_simple;

-- Select value from Temp table

SELECT @LocationID = LocationID

FROM #TmpProcSimple;

GO