SET FMTONLY ON/OFF Resolution For : Unknown Return Type Errors using C#.Net

Agenda :
            Dear all In this article i would like to emphasize the essence of SET FMTONLY ON/OFF which will be an  Resolution For widely faced Unknown Return Type Errors.This was demonstrated with the help of C#.Net using Linq To SQl.

What was the Source of This Error :
            In General if we want to Work with Either Entity Framework or Linq To SQL if we return the data through temporary table using Stored Procedures we may end up this type error.

Description :
          In previous articles I explained different articles related to C#.Net,Asp.Net,Sql Server,XML and DotNetInterviewQuestions Related articles.Let's know how we will work with SET FMTONLY ON/OFF Resolution For : Unknown Return Type Errors




Steps to Implement:

• Create the Table Structure and Insert the Respective Data in to that table
• Implement a Stored Procedure With Temp Table Return the Temp Table as a Result Set
• Create a Console Application
• Drag and Drop the Stored Procedure
• You Will Find the Error
• Now fix that error by Altering the Stored Procedure
• Read the data from the stored Procedure

Create the Table Structure and Insert the Respective Data in to that table :

Table Creation Script :

CREATE TABLE [dbo].[tbl_Branch](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [BranchName] [varchar](150) NULL,

      [CreatedBy] [varchar](150) NULL,

      [CreatedDate] [datetime] NULL DEFAULT (getdate()),

      [ModifiedBy] [varchar](150) NULL,

      [ModifiedDate] [datetime] NULL,

      [IsBtech] [bit] NULL,

      [IsMtech] [bit] NULL,

      [IsDiploma] [bit] NULL,

      [IsActive] [bit] NULL DEFAULT ((1))

) ON [PRIMARY]

GO

Data Insertion Script :

INSERT [dbo].[tbl_Branch] ([ID], [BranchName], [CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate], [IsBtech], [IsMtech], [IsDiploma], [IsActive]) VALUES (1, N'Computer Science and Engineering', N'Admin', CAST(0x0000A7F0010252B6 AS DateTime), N'Admin', CAST(0x0000A7F00102663C AS DateTime), NULL, NULL, NULL, 1)

INSERT [dbo].[tbl_Branch] ([ID], [BranchName], [CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate], [IsBtech], [IsMtech], [IsDiploma], [IsActive]) VALUES (2, N'Electronics and Communication And Engineering', N'Admin', CAST(0x0000A7F001027A8A AS DateTime), N'Admin', CAST(0x0000A7F001029678 AS DateTime), NULL, NULL, NULL, 1)

INSERT [dbo].[tbl_Branch] ([ID], [BranchName], [CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate], [IsBtech], [IsMtech], [IsDiploma], [IsActive]) VALUES (3, N'Electrical and Electronics And Engineering', N'Admin', CAST(0x0000A7F2017E30F1 AS DateTime), NULL, NULL, NULL, NULL, NULL, 1)

INSERT [dbo].[tbl_Branch] ([ID], [BranchName], [CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate], [IsBtech], [IsMtech], [IsDiploma], [IsActive]) VALUES (4, N'Civil Enginering', N'Admin', CAST(0x0000A7F2017E4B60 AS DateTime), NULL, NULL, NULL, NULL, NULL, 1)

INSERT [dbo].[tbl_Branch] ([ID], [BranchName], [CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate], [IsBtech], [IsMtech], [IsDiploma], [IsActive]) VALUES (5, N'Mechanical Engineering', N'Admin', CAST(0x0000A7F2017E5448 AS DateTime), NULL, NULL, NULL, NULL, NULL, 1)

Implement a Stored Procedure With Temp Table Return the Temp Table as a Result Set :

CREATE PROCEDURE USP_GET_TEST

AS

BEGIN

      SELECT * INTO #tbl FROM tbl_Branch 

END

Create a Console Application :

• Go To Visual Studio Click on New-->Project-->Select Console Application
• Name the Application as FMTDemo and click on Ok
• Go to Solution Explorer and Right Click on the Project -->Click on Add New Item -->Select Data -->Linq To XML-->Name the File as Data.dbml
• Go To Server Explorer .Right Click on Data Connection and provide the DB Credentials and Click on Connect
• Expand the Databases and Expand the Stored Procedures and Simply Drag and Drop on to the solution

You Will Find the Error :

Unknown Return Type : The return types for the following stored procedures could not be detected.Set the return type for each stored procedure in the 

Properties window.

Now fix that error by Altering the Stored Procedure :

ALTER PROCEDURE USP_GET_TEST

AS

BEGIN

      SET FMTONLY OFF

      SELECT * INTO #tbl FROM tbl_Branch

      SELECT *FROM #tbl

END

Read the data from the stored Procedure :

DataClasses1DataContext db = new DataClasses1DataContext();

var courses = db.USP_GET_TEST();

Console.Write("ID\tBranchName");

Console.WriteLine();

foreach(var item in courses)

{

  Console.WriteLine(item.ID+"\t"+item.BranchName);

}

Console.ReadLine();