I am getting this error from Entity Framework while creating a complex type. May of them are telling to user SET FMTONLY OFF but for me it is not working. The stored procedure is returning the column but the columns it returns are not fixed. it sometimes returns a table with 2 columns or 4 columns. I am using Entity Framework 5.0. Any help will be much appreciated
SET FMTONLY OFF
GO
ALTER PROCEDURE [dbo].[sp_hcube_rack]
@hcapmaxmodule int
AS
BEGIN
DECLARE @col_name varchar(1055)
DECLARE @count int, @cnt int
DECLARE @col varchar(255), @cmd nvarchar(1000)
DECLARE @ParmDefinition nvarchar(500);
DECLARE getinfo CURSOR FOR
SELECT c.name
FROM sys.tables t
JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'Hydrcube_rack_mapping'
OPEN getinfo
FETCH NEXT FROM getinfo INTO @col
CREATE TABLE #column_tbl (ID INT, tempUserName varchar(MAX))
SET @cnt = 1
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd = N'SELECT @count = COUNT(*)
FROM Hydrcube_rack_mapping
WHERE Hcap_modules = ' + CAST(@hcapmaxmodule AS nvarchar(20)) +
' AND ['+@col+'] IS NULL'
EXEC sp_executesql @cmd, @ParmDefinition = N'@count INT OUTPUT', @count = @count OUTPUT;
IF (@count = 0)
BEGIN
SET @col_name = CONCAT(@col, ',', @col_name)
INSERT INTO #column_tbl (ID, tempUserName)
VALUES (@cnt, @col_name)
SET @cnt = @cnt + 1
END
FETCH NEXT FROM getinfo into @col
END
CLOSE getinfo
DEALLOCATE getinfo
SELECT TOP 1 @col_name = tempUserName
FROM #column_tbl
ORDER BY ID DESC
SELECT @cmd = 'SELECT ' + LEFT(@col_name, LEN(@col_name) - 1) +
' FROM Hydrcube_rack_mapping ' +
' WHERE Hcap_modules = ' + CAST(@hcapmaxmodule AS nvarchar(20))
EXEC sp_executesql @cmd
END
GO
Output from the stored procedure:
hcuberack|kit14_1|kit13_1|kit12_1|kit7_2|kit6_2|kit4_3|kit3_4|Hcap_mod|id
24 |1 |1 |1 |1 |1 |0 |0 |24 |24