同一个查询，同一个数据库，不同的服务器，不同的结果 [英] Same query, same database, different server, different result
问题描述
我们在服务器上有我们的主数据库，有这个存储过程；当我们对数据库运行它时，它返回错误的值.
We have our main database on a server, there is this stored procedure; when we run it against the database, it returns wrong values.
但是当我备份这个数据库并在另一台服务器上恢复它并运行完全相同的查询时，它返回正确的答案.
But when I take a back up of this database and restore it on another server and run the exact same query, it returns the correct answer.
我能做什么?
SQL Server 的配置是否有可能影响查询返回结果的方式?
Is it possible that the configuration of SQL Server affects how a query returns results?
如果是，我可以从哪里开始寻找问题?
If yes where can I start looking for problem ?
这是存储过程，完全相同的过程在两个数据库上运行并且两个数据库是相同的.
Here is the stored procedure, the exact same procedure runs on both databases and both databases are identical.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[S_GheymatGozaryFIFOFroosh]
@AYear SMALLINT,
@LDate CHAR(8),
@OdCd VARCHAR(17),
@FromFirst BIT,
@SCd TINYINT
AS
DECLARE @LHId Int, @LHRadif SmallInt,
@LHFact_Date CHAR(8), @LHFact_No INT,
@LHStock_Cd TinyInt, @LQnt_Resid DECIMAL(18,4),
@LPrc_Resid DECIMAL(30,8)
DECLARE @LRId INT, @LRRadif SmallInt,
@LRFact_Date CHAR(8), @LRFact_No INT,
@LRStock_Cd TinyInt
DECLARE @Kind_Cd TINYINT, @StartDate CHAR(8)
DECLARE @Cnt INT
SET @Cnt = 0
IF @ldate IS NOT NULL AND @FromFirst = 1
BEGIN
DELETE FROM S_Fifo_Gheymat
WHERE (Acc_Year = @Ayear)
AND (@SCd = 0 OR H_Stock_Cd = @SCd)
AND (Od_Cd = @OdCd)
END
IF @SCd = 0
SET @Kind_Cd = 2
ELSE
SET @Kind_Cd = 1
SET @StartDate = Right(CAST(@AYear AS VARCHAR(4)), 2) + '/01/01'
SELECT
@LHId = H_Id,
@LHRadif = H_Radif,
@LHFact_Date = H_Fact_Date,
@LHFact_No = H_Fact_No,
@LHStock_Cd = H_Stock_Cd,
@LQnt_Resid = Qnt_Resid,
@LPrc_Resid = Prc_Resid,
@LRId = R_Id,
@LRRadif = R_Radif,
@LRFact_Date = R_Fact_Date,
@LRFact_No = R_Fact_No,
@LRStock_Cd = R_Stock_Cd
FROM
S_Fifo_Gheymat
WHERE
Acc_Year = @AYear
AND Od_Cd = @OdCd
AND (@SCd = 0 OR H_Stock_Cd = @SCd)
AND EXISTS (SELECT Id
FROM S_Dtl_Fct
WHERE Id = H_Id
AND Radif = H_Radif
AND Stock_Cd = H_Stock_Cd
AND Od_Cd = S_Fifo_Gheymat.Od_Cd)
AND EXISTS (SELECT Id
FROM S_Dtl_Fct
WHERE Id = R_Id
AND Radif = R_Radif
AND Stock_Cd = R_Stock_Cd
AND Od_Cd = S_Fifo_Gheymat.Od_Cd)
SELECT @LHId=ISNULL(@LHId,0),@LHRadif=IsNull(@LHRadif,0),@LHFact_Date=IsNull
(@LHFact_Date,@StartDate),@LHFact_No=IsNull(@LHFact_No,0),@LHStock_Cd=ISNULL
(@LHStock_Cd,0)
,@LQnt_Resid=ISNULL(@LQnt_Resid,0),@LPrc_Resid=ISNULL(@LPrc_Resid,0)
,@LRId=ISNULL(@LRId,0),@LRRadif=IsNull(@LRRadif,0),@LRFact_Date=IsNull
(@LRFact_Date,@StartDate),@LRFact_No=IsNull(@LRFact_No,0),@LRStock_Cd=ISNULL
(@LRStock_Cd,0)
---------------------------------------
IF @LDate IS NULL BEGIN
SELECT TOP 1 @LDate=Fact_Date
FROM S_Dtl_Fct D
LEFT OUTER JOIN S_Hed_Fct H ON D.Id=H.Id
LEFT OUTER JOIN dbo.S_STOCKS S ON D.Stock_Cd=S.Stock_Cd
LEFT OUTER JOIN U_Log U ON H.Id_Log=U.Id_Log AND U.Action_Cd=5
WHERE (H.Acc_Year=@AYear) AND (H.Flag=6) AND (D.Od_Cd=@OdCd) AND
(H.Tamam=0) AND (@SCd<>0 OR S.Estesna_Gp=0)
AND (
(H.Fact_Date>@LHFact_Date)
OR (H.Fact_Date=@LHFact_Date AND
H.Fact_No>@LHFact_No)
OR (H.Fact_Date=@LHFact_Date AND
H.Fact_No=@LHFact_No AND D.Radif>@LHRadif)
OR (H.Fact_Date=@LHFact_Date AND
H.Fact_No=@LHFact_No AND D.Radif=@LHRadif AND D.Stock_Cd>@LHStock_Cd)
)
AND (@SCd=0 OR D.Stock_Cd=@SCd) AND (H.VAZEIAT<>2) AND
(U.Id_Log IS NOT NULL)
ORDER BY H.Fact_Date
End
DECLARE @H TABLE ( H_Id INT,H_Radif SMALLINT,H_Fact_Date CHAR
(8),H_Fact_No INT,H_Stock_Cd TINYINT,Quantity Decimal(18,4),Un_Prc
MONEY,HTamam Bit
,R_Id INT,R_Radif SMALLINT,R_Fact_Date
CHAR(8),R_Fact_No INT,R_Stock_Cd TINYINT,Qnt_Resid Decimal(18,2),Prc_Resid
Decimal(30,8))
INSERT INTO @H
(H_Id,H_Radif,H_Fact_Date,H_Fact_No,H_Stock_Cd,Quantity,HTamam)
SELECT D.Id,D.Radif,H.Fact_Date,H.Fact_No,D.Stock_Cd,D.Quantity,H.Tamam
FROM S_Dtl_Fct D
LEFT OUTER JOIN S_Hed_Fct H ON D.Id=H.Id
LEFT OUTER JOIN dbo.S_STOCKS S ON D.Stock_Cd=S.Stock_Cd
WHERE (H.Acc_Year=@AYear) AND (H.Flag=6) AND (D.Od_Cd=@OdCd) AND
(H.Fact_Date<=@LDate) AND (@SCd<>0 OR S.Estesna_Gp=0)
AND (
(H.Fact_Date>@LHFact_Date)
OR (H.Fact_Date=@LHFact_Date AND H.Fact_No>@LHFact_No)
OR (H.Fact_Date=@LHFact_Date AND H.Fact_No=@LHFact_No
AND D.Radif>@LHRadif)
OR (H.Fact_Date=@LHFact_Date AND H.Fact_No=@LHFact_No
AND D.Radif=@LHRadif AND D.Stock_Cd>@LHStock_Cd)
)
AND (@SCd=0 OR D.Stock_Cd=@SCd) AND (H.VAZEIAT<>2)
ORDER BY H.Fact_Date,H.Fact_No,D.Radif,D.Stock_Cd
Delete S_Related_RH FROM @H H LEFT OUTER JOIN S_Related_RH R ON
H.H_Id=R.H_Id AND H.H_Radif=R.H_Radif
------------------------------------------
DECLARE @HQnt DECIMAL(18,4),@HDate CHAR(8),@SumQ DECIMAL(18,4),@SumG
MONEY,@HQntWithPrc DECIMAL(18,4)
SET @SumG=@LQnt_Resid*@LPrc_Resid
SET @SumQ=@LQnt_Resid
--
DECLARE Cr CURSOR FOR SELECT Quantity,H_Fact_Date,H_Id,H_Radif FROM @H FOR
UPDATE OF Un_Prc
Open Cr
Fetch Next From Cr InTo @HQnt,@HDate,@LHId,@LHRadif
While (@@Fetch_Status=0) AND (@LRId IS NOT NULL)
Begin
IF @HQnt<=@LQnt_Resid BEGIN
SET @LQnt_Resid=@LQnt_Resid-@HQnt
UPDATE @H SET
Un_Prc=@SumG/@SumQ,R_Id=@LRId,R_Radif=@LRRadif,R_Fact_Date=@LRFact_Date,
R_Fact_No=@LRFact_No,R_Stock_Cd=@LRStock_Cd
,Qnt_Resid=@LQnt_Resid,Prc_Resid=@LPrc_Resid
WHERE CURRENT OF Cr
IF @HQnt>0 BEGIN
INSERT INTO dbo.S_Related_RH
(H_Id,H_Radif,R_Id,R_Radif,Quantity)
VALUES (@LHId,@LHRadif,@LRId,@LRRadif,@HQnt)
END
SET @SumG=@LQnt_Resid*@LPrc_Resid
SET @SumQ=@LQnt_Resid
Fetch Next From Cr InTo @HQnt,@HDate,@LHId,@LHRadif
END ELSE BEGIN
IF @LQnt_Resid>0 BEGIN
INSERT INTO dbo.S_Related_RH
(H_Id,H_Radif,R_Id,R_Radif,Quantity)
VALUES (@LHId,@LHRadif,@LRId,@LRRadif,@LQnt_Resid)
END
SET @HQnt=@HQnt-@LQnt_Resid --مقدار باقیمانده حواله
SELECT TOP 1
@LRId=D.Id,@LRRadif=D.Radif,@LRFact_Date=H.Fact_Date,@LRFact_No=H.Fact_No,
@LRStock_Cd=D.Stock_Cd,@LQnt_Resid=D.QUANTITY
,@LPrc_Resid=CASE D.QUANTITY WHEN 0
THEN 0 ELSE ( (Un_Prc*D.QUANTITY)+ISNULL(Qnt_1,0) )/ D.QUANTITY END
FROM S_Dtl_Fct D
LEFT OUTER JOIN S_Hed_Fct H ON D.Id=H.Id
LEFT OUTER JOIN dbo.S_STOCKS S ON D.Stock_Cd=S.Stock_Cd
WHERE (H.Acc_Year=@AYear) AND (H.Flag=5) AND (D.Od_Cd=@OdCd)
AND (H.Fact_Date<=@HDate) AND (H.Tamam=1) AND (@SCd<>0 OR S.Estesna_Gp=0)
AND (
(H.Fact_Date>@LRFact_Date)
OR (H.Fact_Date=@LRFact_Date AND
H.Fact_No>@LRFact_No)
OR (H.Fact_Date=@LRFact_Date AND
H.Fact_No=@LRFact_No AND D.Radif>@LRRadif)
OR (H.Fact_Date=@LRFact_Date AND
H.Fact_No=@LRFact_No AND D.Radif=@LRRadif AND D.Stock_Cd>@LRStock_Cd)
)
AND (@SCd=0 OR D.Stock_Cd=@SCd) AND (H.VAZEIAT<>2)
ORDER BY H.Fact_Date,H.Fact_No,D.Radif,D.Stock_Cd
--
IF @LRId IS NOT NULL BEGIN
IF @HQnt<=@LQnt_Resid SET @HQntWithPrc=@HQnt ELSE SET
@HQntWithPrc=@LQnt_Resid
SET @SumG=@SumG+(@HQntWithPrc*@LPrc_Resid)
SET @SumQ=@SumQ+@HQntWithPrc
End
IF ISNULL(@LQnt_Resid,0)=0 Break
End
END
Close Cr
Deallocate Cr
DECLARE @E Int
SET @E=0
BEGIN TRAN
UPDATE D SET Un_Prc=G.Un_Prc
FROM S_Dtl_Fct D
INNER JOIN @H G ON D.Id=G.H_Id AND D.Radif=G.H_Radif
WHERE (G.HTamam=0) And (G.R_Id IS NOT NULL)
SET @Cnt=@@ROWCOUNT
Set @E=@E+@@Error
DELETE F FROM S_Fifo_Gheymat F
WHERE (Acc_Year=@Ayear) AND (@SCd=0 OR H_Stock_Cd=@SCd) AND
(Od_Cd=@OdCd)
And EXISTS (SELECT TOP 1 Od_Cd
FROM @H
WHERE (H_Stock_Cd=F.H_Stock_Cd) AND
(Od_Cd=@OdCd) AND (R_Id IS NOT NULL)
ORDER BY H_Fact_Date DESC ,H_Fact_No
DESC ,H_Radif DESC ,H_Stock_Cd DESC)
Set @E=@E+@@Error
INSERT INTO S_Fifo_Gheymat
(Acc_Year,H_Stock_Cd,OD_CD,R_Stock_Cd,H_Id,H_Fact_Date,H_Fact_No,
H_Radif,R_Id,R_Fact_Date,R_Fact_No,R_Radif,Qnt_Resid,Prc_Resid)
SELECT TOP 1
@AYear,H_Stock_Cd,@OdCd,R_Stock_Cd,H_Id,H_Fact_Date,H_Fact_No,H_Radif,
R_Id,R_Fact_Date,R_Fact_No,R_Radif,Qnt_Resid,Prc_Resid
FROM @H
WHERE R_Id IS NOT Null
ORDER BY H_Fact_Date DESC ,H_Fact_No DESC ,H_Radif DESC ,H_Stock_Cd Desc
Set @E=@E+@@Error
IF @E=0 COMMIT TRAN ELSE ROLLBACK TRAN
SELECT @Cnt Cnt,@LHFact_No LHFactNo,@LHFact_Date LHFactDate,@LHStock_Cd
LHStock_Cd,@LRFact_No LRFactNo,@LRFact_Date LRFactDate,@LRStock_Cd
LRStock_Cd
推荐答案
如果没有您的数据库副本(不是请求)，就不可能回答这个问题.
Without a copy of your db (not a request) it's not possible to answer this.
SQL Server 的配置是否有可能影响查询返回结果?
Is it possible that the configuration of SQL Server affects how a query returns results?
是的，这是一种可能性.例如，如果您的 ANSI两个服务器之间的 NULL 设置不同，则在 ANSI_NULL 设置为 OFF 的服务器上，NULL 将等于 NULL，但在 ANSI_NULL 为 ON 的服务器上则不然.整理 是另一个.如果一台服务器具有区分大小写的排序规则，则A"和a"不相等，而使用 SQL Server 默认值则相反.这些只是几个例子.
Yes this is a possibility. For example, if your ANSI NULL settings are different between the two servers then NULL will be equal to NULL on the server has ANSI_NULL set to on OFF but not on the server where ANSI_NULL is ON. Collation is another. If one server has a case sensitive collation the "A" and "a" are not equal whereas the opposite is true using the SQL Server default. These are just a couple examples.
也就是说，他们隔离问题的方法是将存储的过程分解为多个部分，并尝试确定差异的开始位置.在您分配变量的第一步中，添加一个步骤将它们转储到一个临时表中，您可以在两个服务器之间汇总和比较该表.如果没有差异，请继续向下运行每个部分，直到找到差异为止.通常我会将所有内容都注释掉，然后从上到下取消注释代码，直到找出问题为止.
That said, they way to isolate the problem is to break the stored proc up into parts and try to identify where the differences are starting. In the first steps, where you assign variables, add a step to dump them into a temp table that you can summarize and compare across both servers. If there's no differences keep moving down the proc running each part until you find a difference. Often I'll comment everything out then uncomment code from top-to-bottom until I isolate the problem.
最后，您在这里使用了几个游标，不需要.您可以通过使代码更加基于集合从而减少冗长来简化代码.排除故障会容易得多，并且性能会好得多.
Lastly, you are using couple cursors here and don't need to. You can simplify your code by making it more set-based and therefore less verbose. It will be much easier to troubleshoot and will perform much, much better.
这篇关于同一个查询，同一个数据库，不同的服务器，不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
