存储过程从另一台服务器调用时返回不同的结果

Question

>我在 Server1 中创建了一个返回正确结果的存储过程 然后在 Server2 上，我只需通过链接服务器执行 Server1 存储过程。

起初，Server2 的返回结果与 Server1 相同。

但在 1 或 2 天后，Server2 在某些数据中返回一些空值。但 Server1 仍然返回正确的结果。

结果服务器 1：

Id  Name       Location     Age
1   Alex       USA          20
2   Bob        UK           23
3   David      JP           25
4   Don        UK           24

结果服务器 2：

Id  Name       Location     Age
1   Alex       USA          20
2   Bob        NULL         NULL
3   David      NULL         NULL
4   Don        UK           24

我只能通过重新编译 Server1 存储过程来暂时解决此问题。 比Server2将获得正确的结果，但2天后再次出现相同的问题。

20190920更新.....包括存储过程查询的示例。

仅供参考：一些 NULL 值出现在这些列 CASE 语句中

all the @... is input 
@output is declare as Table. 
above here is a lot of insert statement into the @Output table
SELECT * FROM (
		SELECT tbla.SiteName,tbla.FromWorkCenter ,tbla.Category,tbla.MaterialName,tbla.OperSeq
		,tbla.FromOperSeq,tbla.StepIndex,tbla.FromStepIndex,SUM(tbla.CFqty) CFQty
		 FROM (
				SELECT tbl.SiteName 
				,CASE
				WHEN tbl.RouteName like '%REWORK%' THEN 'PS-'+tbl.ToWorkCenter
				WHEN isnull(tbl.FromWorkCenter,'WRH')='WRH' THEN 
				(Select CASE Source WHEN 'TWH' THEN 'TWH' WHEN 'LWH' THEN 'LWH' ELSE 'WRH' END AS Source
				from DIM_MATERIAL ddm WHERE ddm.MaterialName=tbl.MaterialName
				AND ddm.MainCategory_wk = (SELECT TOP 1 dc.key_wk FROM DIM_Category dc WHERE dc.Category=TBL.Category))
				else 'PS-'+dw.WorkCenterName END AS FromWorkCenter
				,Category ,tbl.MaterialName,tbl.RouteName
				,CASE tbl.OperSeq WHEN '0' THEN '10' ELSE tbl.OperSeq END AS OperSeq
				,CASE rsd2.OperSeq WHEN '0' THEN '10' ELSE rsd2.OperSeq END AS FromOperSeq
				,CASE WHEN rsd.RouteName LIKE '%REWORK%' THEN (
						SELECT TOP 1 c.StepIndex FROM DIM_MATERIAL a
						JOIN DIM_MaterialStructure b on a.Key_wk=b.Material_wk
						JOIN DIM_ROUTESTEPDETAIL c on b.RouteName=c.RouteName and c.OperSeq=tbl.OperSeq and OperSeqFlag='TRUE'
						join AUDIT_JOB d on b.audit_id = d.audit_id and d.post_end_dt <= @Date
						WHERE a.MaterialName=tbl.MaterialName
						ORDER BY d.post_end_dt desc
						)
				ELSE rsd.StepIndex end StepIndex
						,CASE WHEN rsd.RouteName LIKE '%REWORK%' THEN (
						SELECT TOP 1 c.StepIndex FROM DIM_MATERIAL a
						JOIN DIM_MaterialStructure b on a.Key_wk=b.Material_wk
						JOIN DIM_ROUTESTEPDETAIL c on b.RouteName=c.RouteName and c.ToOperSeq=tbl.OperSeq and OperSeqFlag='TRUE'
						join AUDIT_JOB d on b.audit_id = d.audit_id and d.post_end_dt <= @Date
						WHERE a.MaterialName=tbl.MaterialName 
						ORDER BY d.post_end_dt desc
						)
				ELSE rsd2.StepIndex end FromStepIndex
				,(SUM(BFQty)+SUM(RtnFromQty)+SUM(RecQty)-SUM(IssueQty)-SUM(RtnToQty)-(SUM(RejQty))+SUM(TrnQty)+SUM(AdjQty)+SUM(SCQty)) AS CFQty 
				
				FROM @Output TBL
				JOIN DIM_MATERIAL DM ON TBL.MaterialName=dm.MaterialName AND dm.MainCategory_wk = (SELECT TOP 1 dc.key_wk FROM DIM_Category dc WHERE dc.Category=TBL.Category)
				LEFT JOIN DIM_ROUTESTEPDETAIL rsd ON rsd.RouteName=tbl.RouteName AND rsd.OperSeq=CASE tbl.OperSeq WHEN '0' THEN '0' ELSE tbl.OperSeq END 
					AND OperSeqFlag='TRUE' AND rsd.Site= @Company
				LEFT JOIN DIM_ROUTESTEPDETAIL rsd2 ON rsd2.RouteName=tbl.RouteName AND rsd2.ToOperSeq=CASE tbl.OperSeq WHEN '0' THEN '0' ELSE tbl.OperSeq END 
					AND rsd2.OperSeqFlag='TRUE' AND rsd2.Site = CASE tbl.SiteName WHEN 'SIS' THEN 'SIS' ELSE 'INT' END
				LEFT JOIN DIM_WORKCENTER DW ON rsd.WorkCenter_wk=dw.key_wk
				WHERE tbl.SiteName=@Site 
				GROUP BY tbl.SiteName, tbl.FromWorkCenter ,tbl.Category ,tbl.MaterialName ,tbl.RouteName,tbl.OperSeq, tbl.StepIndex,dm.Key_wk,rsd.StepIndex,tbl.RouteName, tbl.Material_WK,rsd2.OperSeq,rsd2.StepIndex,dw.WorkCenterName,tbl.ToWorkCenter, rsd.RouteName
		) tbla
		where tbla.CFQty<>0
		GROUP BY tbla.SiteName, tbla.FromWorkCenter, tbla.Category ,tbla.MaterialName,tbla.OperSeq,tbla.FromOperSeq,tbla.StepIndex,tbla.FromStepIndex
) b
where b.CFQty<>0
ORDER BY b.MaterialName ,b.OperSeq ,b.SiteName ,b.FromWorkCenter ,b.Category ,b.FromOperSeq ,b.StepIndex ,b.FromStepIndex

Answer 1

我发现了问题。 原来是从本地和远程查询时的数据序列不同

结果服务器 1：

Seq Name       Location     Category
1   Bob        UK           20
2   Bob        UK           23

结果服务器 2：

Seq Name       Location     Category
1   Bob        UK           23
2   Bob        UK           20

因此，当过滤某些数据时，未正确连接为结果 null