使用相同参数时，存储过程如何在从 BIDS 调用时不返回行，但在从 SSMS 调用时返回行？

Question

使用 SSRS 2008R2 和 SQL Server 2005 数据库作为数据源。

我有一个在 Business Intelligence Development Studio (VS 2008) 中构建的报告（.rdl 文件）。它使用存储过程作为数据源。该过程采用一个参数，即一个 INT，它是过程中一个 SELECT 中主表的唯一键。

对于某些参数值，从 BIDS 的查询设计器中执行过程不会返回任何行。使用相同参数值从 SSMS 执行相同过程确实会返回行。无论调用是从 BIDS 还是 SSMS 发出，Profiler 都会显示相同的 SELECT。他们怎么会得到不同的结果呢？

这是过程：

CREATE PROCEDURE [Report_BatchEdit]
    @BatchAltId INT
AS
   SELECT   b.[BatchAltId]
            , fs.[Name] AS [FundingSource]
            , b.[StartDate] AS [StartDate]
            , b.[StopDate] AS [StopDate]
            , b.[Description]
            , b.[Created]
            , bl.[BillId]
            , COALESCE(c.[Name], bill.RecipientCustomerName) AS [CustomerName]
            , COALESCE(ri.[Identifier], bill.[RecipientIdentifier]) AS [MedicaidNumber]
            , bill.[NetTotal] AS [ClaimAmount]
            , bl.[BillingCodeCode] AS [BillingCode]
            , bl.[BillingCodeDescription] AS [BillingCodeDescription]
            , bl.[StartDate] AS [FromDate]
            , bl.[StopDate] AS [ToDate]
            , bl.[UnitSizeName] AS [UnitSize]
            , bl.[CalculatedUnits] AS [Units]
            , bl.[Rate]
            , bl.[Amount]
            , fs.UsesModifiers
            , CASE WHEN fs.UsesModifiers = 1 THEN rm1.[Code] ELSE NULL END AS [RateModifier1Code]
            , CASE WHEN fs.UsesModifiers = 1 THEN rm2.[Code] ELSE NULL END AS [RateModifier2Code]
            , CASE WHEN fs.UsesModifiers = 1 THEN rm3.[Code] ELSE NULL END AS [RateModifier3Code]
            , CASE WHEN fs.UsesModifiers = 1 THEN rm4.[Code] ELSE NULL END AS [RateModifier4Code]
    FROM    [Batch] AS b 
            JOIN [Bill] AS bill ON b.[BatchId] = bill.[BatchId] 
            JOIN [BillLine] AS bl ON bill.[BillId] = bl.[BillId]
            JOIN [Customer] AS fs ON b.[PayerCustomerId] = fs.[CustomerId]
            LEFT JOIN [Customer] AS c ON bill.[RecipientCustomerId] = c.[CustomerId]
            LEFT JOIN [RecipientIdentifier] AS ri ON b.[PayerCustomerId] = ri.[PayerCustomerId] AND bill.[RecipientCustomerId] = ri.[RecipientCustomerId]
            LEFT JOIN [RateModifier] AS rm1 ON bl.[RateModifier1Id] = rm1.[RateModifierId]
            LEFT JOIN [RateModifier] AS rm2 ON bl.[RateModifier2Id] = rm1.[RateModifierId]
            LEFT JOIN [RateModifier] AS rm3 ON bl.[RateModifier3Id] = rm1.[RateModifierId]
            LEFT JOIN [RateModifier] AS rm4 ON bl.[RateModifier4Id] = rm1.[RateModifierId]
   WHERE    BatchAltId = @BatchAltId 
ORDER BY    [FundingSource], b.[StartDate], b.[Description], [CustomerName], [BillingCode], [FromDate], [ToDate]

Answer 1

这听起来很愚蠢，但您确定 BIDS 包引用与运行 SSMS 查询的同一服务器上的数据库吗？当我遇到这样的事情时，我几乎总是在包的配置中发现错误，并且数据源指向错误的服务器。