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

问题描述

我在 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

标签： sqlsql-serverstored-proceduressql-server-2012

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

问题描述

解决方案

推荐阅读