LINQ "The return types for the following stored procedures could not be detected" (NOT temp tables)

Question

Long-time lurker, first-time poster here :)

I've decided to ask here since I've grown tired of searching in Google, SO and such. I'm experiencing this error when importing a Stored Procedure from my SQL 2005 DB to my Website (FW 4.0, C#) LINQ project:

"The return types for the following stored procedures could not be detected"

I have two queries. I'd like to note that I'm not using temporary tables. These queries have only been edited in variable names and such, for security measures. Anyways, here's the one that works:

ALTER PROCEDURE [dbo].[spMyWorkingProc]
@OS numeric
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql varchar(5000)

set @sql = '
        SELECT
        *
        FROM MYDB.dbo.MYVIEW 
        WHERE OS = ' + CONVERT(varchar, @OS)

EXEC('
SET NOCOUNT ON
SELECT * FROM
OPENROWSET(''SQLOLEDB'', ''MYDB''; ''user''; ''password'', ''' + @sql + ''')')

END

And here's the one which doesn't:

ALTER PROCEDURE [dbo].[spNotWorking]
@IDCLIENT int,
@PPNO char(10)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql varchar(5000)

set @sql = '
        SELECT
        *
        FROM MYDB.dbo.MYOTHERVIEW
        WHERE ID = ' + CONVERT(VARCHAR, @IDCLIENT) + ' AND PASSPORTNO = ' + @PPNO + ' ORDER BY AGE'

EXEC('
SET NOCOUNT ON
SELECT * FROM
OPENROWSET(''SQLOLEDB'', ''MYDB''; ''user''; ''password'', ''' + @sql + ''')')  

END

Here's what I've tried and found, along with a few notes and questions:

• Both users (LINQ and Remote Server) are db_owner.

• The non-working stored procedure works if I leave it with only one parameter (doesn't matter which one). If I add a new one, it doesn't work.

• If I make an empty SP with a simple select * from any table, and two or more parameters, it works (so it doesn't make sense).

• Let's say the problem is in EXEC. If so, how come the first SP works?. Ok, the problem isn't there then. (that's what logic tells me, I guess?)

• The problem is not the view, as it doesn't work if I use the view I access in the working SP.

• I need to do this using the queries provided. I can't use linked servers or anything similar as we need to do this to connect to 6.5 databases, and it doesn't solve or explain why the first SP works and the second one doesn't, while using the same method.

• I tried to add a parameter (taken from the non-working SP) to the working SP and it works, so it must be something with the second SP, but I dunno what :/

• Okay, if I modify the 2nd SP and remove the parameters in the style of @VAR and hardcode them into a single @sql variable, it works perfectly. So, I see it must be something with including variables into a dynamic query (or something).

I'm literally pulling my hair off, so any ideas and suggestions are welcome!

Thanks in advance! - DARKGuy

Answer 1

ORM code generation tools such as those in Linq2Sql function imports usually run your SPROCs with the SHOWPLAN_ALL setting turned on, without actually executing the proc, in order to 'sniff' the result set.

This approach has limitations, as you've found, with dynamic SQL, and also often are issues with procs which use TempDB or have branches which return data with different schemas.

Your solution is a good one, i.e. to replace the real proc with one which is hard coded, but which returns mock data which is representative of the actual data.

Edit

Another pattern which I've seen to address this is to embed the hard coded 'schema' result into an uncallable condition, like so:

ALTER PROCEDURE [dbo].[someProc]
AS
BEGIN
  SET NOCOUNT ON;
  IF (1 = 0)
    BEGIN
      -- "Cheat" the ORM resultset sniffing by returning an example of the schema.
      -- Casting and name aliasing to ensure the ORM derives the correct types
      SELECT CAST('Hello' AS NVARCHAR(50)) AS Name, CAST (1 AS BIT) AS IsOnline, ...
      RETURN;
    END
    .. rest of the REAL proc goes here

Answer 2

I too had same issue. And solution was at http://developwith.net/2012/07/16/unknown-return-type-linq-to-sql/ Checking many sites says about those 3 points mentioned in this link but nowhere mentioned about transaction causing the problem. In my case, it was try catch block raising the hell. After removing it, LINQ to SQL behaved normal. Later introduced the try catch back.