LINQ“无法检测到以下存储过程的返回类型". (不是临时表) [英] LINQ "The return types for the following stored procedures could not be detected" (NOT temp tables)

问题描述

长期潜伏者，第一次来这里:)

由于我已经厌倦了在Google，SO等中进行搜索，因此我决定在这里提问.将存储过程从SQL 2005 DB导入到我的网站(FW 4.0，C#)LINQ项目时，我遇到此错误:

无法检测到以下存储过程的返回类型"

我有两个查询. 我想指出的是我没有使用临时表.这些查询仅以变量名等进行编辑，以确保安全.无论如何，这是有效的功能:

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

这是没有的那个:

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

这是我尝试并发现的内容，以及一些注释和问题:

• 两个用户(LINQ和远程服务器)都是db_owner.

• 如果我仅使用一个参数(不管是哪个参数)，则该非工作存储过程将起作用.如果我添加一个新的，它将不起作用.

• 如果我用一个简单的select *从任何表中创建一个空的SP，并带有两个或多个参数，它将起作用(因此没有任何意义).

• 比方说问题出在EXEC中.如果是这样，第一个SP的工作原理是什么?好的，问题就不在那里了. (我猜这就是逻辑告诉我的吗?)

• 问题不在于视图，因为如果我使用在有效的SP中访问的视图，则该视图将不起作用.

• 我需要使用提供的查询来执行此操作.我无法使用链接服务器或类似的工具，因为我们需要这样做才能连接到6.5数据库，并且在使用相同方法的同时，它无法解决或解释第一个SP为何有效而第二个SP无效的原因. /p>

• 我试图将一个参数(从非工作SP中获取)添加到工作SP中，并且它可以工作，因此第二个SP中一定有此参数，但是我不知道是什么:/

• 好吧，如果我修改第二个SP并删除@VAR样式的参数并将其硬编码为单个@sql变量，则它可以完美工作.因此，我看到它一定是在动态查询中包含了变量(或其他东西).

我真的是在扯头发，所以欢迎提出任何想法和建议！

提前谢谢！ -DARKGuy

解决方案

ORM代码生成工具(如Linq2Sql函数导入中的工具)通常使用

正如您所发现的那样，这种方法在动态SQL中有局限性，并且经常是使用TempDB或具有返回不同模式的数据的分支的proc的问题.

您的解决方案是一个很好的解决方案，即用一个经过硬编码但会返回代表实际数据的模拟数据的真正proc代替真正的proc.

修改

我看到的另一种解决方案是将硬编码的模式"结果嵌入不可调用的条件，例如:

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

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

解决方案

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

这篇关于LINQ“无法检测到以下存储过程的返回类型". (不是临时表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！