During our last project we decided to use Linq to SQL context for object mapping as we needed to use only few stored procedures from MSSQL database.
But when I tried to map required procedures I'd got an error: Error: Unknown Return Type, The return types for the following stored procedures could not be detected.
At glance research I found following articles according the issue: Unknow return type error, How to get multiple result set of procedure using LINQ to SQL.
I knew exactly that there weren't declared any temporary tables in these stored procedures. But I wasn't quite sure about multiply results. In fact, it were very big stored procedures, about 2000 rows in each, moreover I didn't write the SPs by myself, so it was T-SQL encapsulation in action :). As a result of this I decided to find out how Linq to SQL gets metadata information about SP result fields. Via Mr. Google I found following: Getting Return Metadata from Stored Procedures
I tried it and you can see what I've got:
But it wasn't last problem, during the query's execution I got this nice message:
After a few seconds I found out that the problem comes from external CLR library that our database actually use. As a matter of fact there was a following scenario: when the Linq to SQL tried to get stored procedure metadata it called all sp's inner functions and procedures and as you can see from the picture above when FMTONLY mode set some arguments to the CLR function "RegexGetMatchGroupValue" it returns exception.
When I fixed multiply results set and this error in CLR everything had started to work perfectly.
So the main idea is: if you have got error when you're adding stored procedure to Linq to Sql data context. Firstly, try it with FMTONLY mode. It will show you all errors that can be reason of the headache.
But when I tried to map required procedures I'd got an error: Error: Unknown Return Type, The return types for the following stored procedures could not be detected.
At glance research I found following articles according the issue: Unknow return type error, How to get multiple result set of procedure using LINQ to SQL.
I knew exactly that there weren't declared any temporary tables in these stored procedures. But I wasn't quite sure about multiply results. In fact, it were very big stored procedures, about 2000 rows in each, moreover I didn't write the SPs by myself, so it was T-SQL encapsulation in action :). As a result of this I decided to find out how Linq to SQL gets metadata information about SP result fields. Via Mr. Google I found following: Getting Return Metadata from Stored Procedures
I tried it and you can see what I've got:
But it wasn't last problem, during the query's execution I got this nice message:
When I fixed multiply results set and this error in CLR everything had started to work perfectly.
So the main idea is: if you have got error when you're adding stored procedure to Linq to Sql data context. Firstly, try it with FMTONLY mode. It will show you all errors that can be reason of the headache.
