Geek Answers Handbook

LINQ "Return types for the following stored procedures cannot be detected" (NOT temp tables)

Long term lurker, first poster here :)

I decided to ask here, since I'm tired of searching on Google, and so on. I am experiencing this error when importing a stored procedure from my SQL 2005 database to my site (FW 4.0, C #). LINQ Project:

"The return types for the following stored procedures cannot be detected."

I have two requests. I would like to note that I do not use temporary tables . These queries were edited only in variable names and, for example, for security measures. In any case, it works here:

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 is one that is not :

 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 is what I tried and found, as well as a few notes and questions:

  • Both users (LINQ and the remote server) are db_owner.

  • A non-working stored procedure works if I leave it with only one parameter (no matter which one). If I add a new one, this will not work.

  • If I create an empty SP with a simple selection * from any table and two or more parameters, it works (so it makes no sense).

  • Let's say the problem is in EXEC. If so, how does the first joint venture work? So there is no problem there. (which, it seems to me, is logic, I suppose?)

  • The problem is not the view, since it does not work if I use the view that I get in the working SP.

  • I need to do this using the provided requests. I can’t use linked servers or something like that, which is necessary for connecting to 6.5 databases, and it does not solve and does not explain why the first SP works and the second does not, using the same method.

  • I tried adding a parameter (taken from a non-working SP) to the working SP, and it works, so it should be something with the second SP, but I don't know what: /

  • Well, if I modify the second SP and delete the parameters in the @VAR style and hard code them into a single @sql variable, it works fine. So, I see that it should be something with the inclusion of variables in a dynamic query (or something else).

I literally pull my hair, so any ideas and suggestions are welcome!

Thanks in advance! - DARKGuy

+7
source share
3 answers

ORM code generation tools, such as those imported into Linq2Sql functions, usually run your SPROC with the SHOWPLAN_ALL parameter turned on , without actually executing proc to sniff the result set.

This approach has limitations, as you have discovered, with dynamic SQL, and there are often problems with procs that use TempDB or have branches that return data with different schemas.

Your solution is a good one, that is, replace the real proc with a hard, encoded one, but which returns a mock of data that is representative of the actual data.

Edit

Another template that I saw for this is to insert the result of a hard-coded "scheme" into an unimaginable condition, for example:

 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
+7
source

I had the same problem too. And the solution was http://developwith.net/2012/07/16/unknown-return-type-linq-to-sql/ Checking many sites says about the 3 points mentioned in this link, but the transaction is not mentioned anywhere. causing the problem. In my case, it was a catch catch block raising hell. After removing it, LINQ to SQL behaved normally. Later there was an attempt to catch the attempt.

+1
source

Use the format below to write proc ..

 CREATE PROCEDURE PROC_NAME @variableName nvarchar(150) AS IF 1=0 BEGIN SET FMTONLY OFF END BEGIN SET NOCOUNT ON; DECLARE @Sql nvarchar(MAX) SET @Sql = 'SELECT Column1, Column2, Column3 FROM' ; EXEC sp_executesql @Sql END

Difference in the above format IF 1=0 BEGIN SET FMTONLY OFF END

This helps to solve this problem and determine the type of return.

+1
source

More articles:


All Articles