Linq dbml show int return type for an SP which SP return some fields from temporary table.

Question

I have written a Stored procedure which return some fields from a temporary table which i create in this stored procedure. but when I include it in my dbml file it show return type of my stored procedure as int. which should be not as am returning field from table although its temporary.

Answer 1

Hi - there are a few suggestions as to how to work around this problem here.

Answer 2

Linq-to-SQL uses the SQL Server "fmtonly" setting to determine return type from stored procedures. This is to avoid having stored procedures that make changes to the database do so inadvertedly when getting their signature.

If your stored procedure is safe to execute with no param values etc, you can simply add "set fmtonly off;" in the beginning of the procedure. Linq-to-SQL will then be able to correctly identify the return type from the stored proc.