The selected stored procedure returns no columns, when i try to import my stored procedure inside my asp.net mvc

asp.net-mvc asp.net-mvc-5 edmx entity-framework

Question

I have the following stored procedure inside my SQL server 2008 r2 & asp.net mvc web application :-

USE [ITSERres]
GO
/****** Object:  StoredProcedure [dbo].[AdvanceSearchSP]    Script Date: 08/04/2014 16:21:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  StoredProcedure [dbo].[AdvanceSearch2]    Script Date: 07/18/2014 10:37:47 ******/


ALTER PROCEDURE [dbo].[AdvanceSearchSP]
        -- Add the parameters for the stored procedure here

        @SearchType nvarchar(10) = null,
        @CustomerID bigint = null,
        @StringCustomerID nvarchar(50) = null,
        @SiteID bigint = null,
        @StateID bigint = null,
        @PrimarycustomerOnly bit = false,
        @RoleID int = null,
        @TypeID int = null,
        @IsManaged bit = null,
        @Name nvarchar(125) = null,
        @NameSelection nvarchar(10) = null,
        @Tag nvarchar(50) = null,
        @TagSelection nvarchar(10) =null,
        @ServiceTag nvarchar(100) = null,
        @ServiceTagSelection nvarchar(10) =null,
        @SerialNumber nvarchar(50) = null,
        @SerialNumberSelection nvarchar(10) =null,
        @Comment nvarchar(250) = null,
        @CommentSelection nvarchar(10) =null,
        @IP nvarchar(50) = null,
        @IPSelection nvarchar(10) = null,
         @MAC nvarchar(50) = null,
         @MACSelection nvarchar(10) = null
    AS
    BEGIN

        SET NOCOUNT ON;
if(@SearchType = 'name')
Begin 
        -- Insert statements for procedure here
    select t.Tag , r.RESOURCENAME , rs.DISPLAYSTATE as StateName, tt.Name as TypeName , sdo.NAME as sitename , accountdef.ORG_NAME as cusotmername, t.IsManaged as ismanaged ,sysinfo.SERVICETAG,r.SERIALNO
  //code goes here...

I did the following:-

  1. i mapped the SP insdie my .edmx file.

  2. i right click on the .edmx file, click on "Model Browser".

  3. under my Model, i click on function Import.

  4. I right click on the SP , click on edit. then when i click on "Get Column Info" , i got the following message:-

The selected stored procedure returns no columns

so can anyone advice ? Thanks

1
9
8/4/2014 4:30:13 PM

Popular Answer

I also face the same problem. Just FYI, my stored procedures contains Temporary Tables. After googling a bit, found this page that solved my problem :

http://forums.asp.net/t/2000731.aspx?EF+4+0+The+selected+stored+procedure+returns+no+columns+when+i+try+to+import+my+stored+procedure+inside+my+asp+net+mvc

The solution suggested is to add SET FMTONLY OFF in the beginning of the stored procedure and it is working perfectly for my case.

maybe you can have a try on it.

26
9/24/2014 3:47:05 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow