I am unable to get the column information from the function import in Model Browser even though I could see the results in SQL Server Management Studio. I added StoredProcedure to the StoredProcedures section in Model Browser and when I am adding the function import, I am hitting the GetColumnInformation, I am getting this "The selected stored procedure does not return no columns". But I am executing the stored procedure I could see the results. My project is a ASP.NET MVC2 one. I am using ADO.NET Entity Model.
This is the stored proc I created
USE [MyDatabase Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[My Stored Proc Name]
@startDate datetime,
@endDate datetime,
@accountId int,
@healthPlanId int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @variableId as int;
CREATE TABLE #TempAuditEvent(
[AuditEvent_Id] [int] NOT NULL,
[TableId] [int] NOT NULL,
[Original_record] [xml] NOT NULL,
[Current_record] [xml] NOT NULL,
[ActionId] [int] NOT NULL,
[OccuredOn] [datetime] NOT NULL,
[TriggeredBy] varchar NOT NULL,
[IsActive] [tinyint] NOT NULL,
[DateTime_Added] [datetime] NOT NULL,
[Added_By] varchar NOT NULL,
[DateTime_Updated] [datetime] NULL,
[Updated_By] varchar NULL,
);
DECLARE variable_cursor CURSOR FOR
select distinct bao.ba_Object_id from ProductInfo p
join baSelection bas on p.Product_Id= bas.ba_BLID
join baObject bao on bas.ba_variable_Id=bao.ba_Object_id
where (bao.ba_Object_Type_Id = 4 or bao.ba_Object_Type_Id = 6 or bao.ba_Object_Type_Id = 22)
and p.AccountId = @accountId and p.IsProduct = 1
and bas.Health_Plan_ID = @healthPlanId;
OPEN variable_cursor;
FETCH NEXT FROM variable_cursor INTO @variableId;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TempAuditEvent
SELECT * FROM AuditEvent WHERE AuditEvent_Id in
(
(SELECT ae.AuditEvent_Id FROM vwProductChangesReport ae
WHERE ae.DateTime_Added >=@startDate AND ae.DateTime_Updated < @endDate
AND ((ae.TableId >=12 AND ae.TableId<=14) OR ae.TableId=9 OR ae.TableId=40 OR ae.TableId=50 OR ae.TableId=64 OR ae.TableId=65)
AND (ae.strCurrent LIKE '%Variable_Id="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%VariableId="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%BaObjectId="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%ParentBaObjectId="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%ObnId="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%Benefit_Variable_Id="'+ CAST(@variableId AS varchar(20)) + '"%'
))
) FETCH NEXT FROM variable_cursor INTO @variableId; END CLOSE variable_cursor; DEALLOCATE variable_cursor;
SELECT * FROM #TempAuditEvent; DROP TABLE #TempAuditEvent;
END
