January 21, 2009 at 3:15 pm
I would like to "Select * from" the recordset output of a Stored procedure.
Here's an example of the SP CALL which results in over 16,000 rows:
"dbo"."ProjectCostSpreadReport" NULL, 'CO/1/08/01/01', '1=1'
In Sybase SA it's very simple. Example:
Select * from "dbo"."ProjectCostSpreadReport" (NULL, 'CO/1/08/01/01', '1=1') where OwnerCode = '01'
For SQL Server I have tried the following in various permutations:
select * from openrowset('SQLOLEDB', 'Trusted_Connection=Yes;Server=(columbia);Database=MyDb, 'exec "dbo"."ProjectCostSpreadReport" NULL, ''CO/1/08/01/01'', ''1=1''')
result is always an error:OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
The server is configured to allow remote connections, BTW.
Is there a simpler way to query results from an SP? If not, what can I do to get OPENROWSET to work for me?
Joel
Takauma
January 21, 2009 at 3:18 pm
Why not define a temporary table and the use INSERT INTO #TempTable EXEC dbo.MyStoredProc. Then use the temporary table for what ever you need to do?
January 21, 2009 at 3:28 pm
OPENROWSET needs to be allowed through on 2005 and later. You access that through the SQL Server Surface area config tool.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply