这有什么理由发生吗？
我使用的是sql Server 2008 R2快速版.
这是存储过程的干运行：
EXEC @return_value = [dbo].[GetAttributes] @pi_PID = 95102,@pi_returnOnly1stRow = 0,@pi_returnExtAttr = 1SELECT 'Return Value' = @return_value
这是来自C#代码的调用：
sqlDataAdapter da = new sqlDataAdapter();da.SelectCommand = new sqlCommand("GetAttributes",conn);da.SelectCommand.Parameters.AdDWithValue("@pi_PID",95102); da.SelectCommand.Parameters.AdDWithValue("@pi_returnOnly1stRow",0);da.SelectCommand.Parameters.AdDWithValue("@pi_returnExtAttr",1);da.SelectCommand.CommandType = CommandType.StoredProcedure;DataSet ds = new DataSet();da.Fill(ds,"result_name");Datatable dt = ds.tables["result_name"];conn.Close(); 编辑：问题返回….
我以为我已经通过杀死引起这个问题的过程解决了这个问题,但不幸的是它又回来了.我说我对这个问题有了更好的理解,我可以看到问题,但不知道是什么导致了这个问题.
问题是一样的.如果直接在sql服务器上运行但是在从C#程序调用时无法执行,则存储过程运行正常….
我跑了Sp_lock.我使用的存储过程有SpID：’59’,其锁定如下所示.我不知道为什么这些表只有在从c#调用存储过程时才被锁定,而不是在SP的干运行期间被锁定.
59 2 0 0 DB [ENCRYPTION_SCAN] S GRANT59 5 1802489500 0 PAG 1:169937 S GRANT59 5 1914489899 0 TAB IS GRANT59 5 1898489842 0 TAB IS GRANT59 5 1177771253 0 TAB IS GRANT59 5 1786489443 0 TAB IS GRANT59 5 1802489500 0 TAB IS GRANT59 5 1882489785 0 TAB IS GRANT59 5 0 0 DB S GRANT
最新编辑：我还通过在SP中涉及的每个表上对NolOCK进行了介绍来编辑我的Sp
我也在这里粘贴存储过程供您参考(添加了最新的NolOCK)….
@pi_PID INT,@pi_returnOnly1stRow BIT,@pi_returnExtAttr BITASBEGIN IF(@pi_returnOnly1stRow=1) BEGIN SELECT top 1 NulL section_name,header_mvoc.text Attr_name,body_mvoc.Text Attr_Value,cds_mspecee.displayOrder FROM cds_mspecee WITH (NolOCK) JOIN cds_mvocee header_mvoc WITH (NolOCK)ON (cds_mspecee.hdrID = header_mvoc.ID) JOIN cds_mvocee body_mvoc WITH (NolOCK) ON (cds_mspecee.bodyID = body_mvoc.ID) JOIN cds_prod WITH (NolOCK)ON (cds_Prod.prodID = cds_mspecee.prodID) JOIN Productvariant revpro WITH (NolOCK) On (revpro.ManufacturerSKU=cds_prod.mfpn) AND revpro.ProductID = @pi_PID UNION SELECT section_evoc.Text section_name,header_evoc.text Attr_name,body_evoc.Text Attr_Value,cds_Especee.displayOrder FROM cds_Especee WITH (NolOCK) JOIN cds_Evocee section_evoc WITH (NolOCK) ON (cds_Especee.SectID = section_evoc.ID) JOIN cds_Evocee header_evoc WITH (NolOCK) ON (cds_Especee.hdrID = header_evoc.ID) JOIN cds_Evocee body_evoc WITH (NolOCK) ON (cds_Especee.bodyID = body_evoc.ID) JOIN cds_prod WITH (NolOCK) ON (cds_Prod.prodID = cds_especee.prodID) JOIN Productvariant revpro WITH (NolOCK) On (revpro.ManufacturerSKU=cds_prod.mfpn) AND revpro.ProductID = @pi_PID AND @pi_returnExtAttr = 1 ORDER BY section_name,displayorder ASC END ELSE BEGIN SELECT NulL section_name,cds_mspecee.displayOrder FROM cds_mspecee WITH (NolOCK) JOIN cds_mvocee header_mvoc WITH (NolOCK) ON (cds_mspecee.hdrID = header_mvoc.ID) JOIN cds_mvocee body_mvoc WITH (NolOCK) ON (cds_mspecee.bodyID = body_mvoc.ID) JOIN cds_prod WITH (NolOCK) ON (cds_Prod.prodID = cds_mspecee.prodID) JOIN productvariant revpro WITH (NolOCK) On (revpro.manufacturerSKU=cds_prod.mfpn) AND revpro.ProductID = @pi_PID UNION SELECT section_evoc.Text section_name,cds_Especee.displayOrder FROM cds_Especee WITH (NolOCK) JOIN cds_Evocee section_evoc WITH (NolOCK) ON (cds_Especee.sectID = section_evoc.ID) JOIN cds_Evocee header_evoc WITH (NolOCK) ON (cds_Especee.hdrID = header_evoc.ID) JOIN cds_Evocee body_evoc WITH (NolOCK) ON (cds_Especee.bodyID = body_evoc.ID) JOIN cds_prod WITH (NolOCK) ON (cds_Prod.prodID = cds_especee.prodID) JOIN productvariant revpro WITH (NolOCK) On (revpro.manufacturerSKU=cds_prod.mfpn) AND revpro.ProductID = @pi_PID AND @pi_returnExtAttr = 1 ORDER BY section_name,displayorder ASC ENDEND解决方法 这听起来像是“参数嗅探”( http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx)的问题,这意味着可以根据过程的先前执行针对不同的参数值优化为您的过程缓存的执行计划.此缓存计划可能对参数值x有效,但对参数值y无效.我之前遇到过同样的问题,当我在SSMS中运行查询时,它立即运行,但如果我从我的应用程序运行,它会“挂起”.这是因为我在SSMS中运行时的查询文本与来自应用程序的查询文本略有不同,因此它使用不同的缓存执行计划.
解决此问题的一种解决方法是在proc中创建本地范围的变量,这些变量充当过程参数的副本.
我还建议：
>如果没有从UNION的每一侧返回重复行,则将UNION替换为UNION ALL以删除sql Server检查行中重复值的不必要开销. What is the difference between UNION and UNION ALL?
>避免使用WITH(NolOCK)提示,因为这些可能会导致数据一致性问题.如果您完全理解副作用,则只使用此提示,并且您的应用程序可以使用脏读取并且可能具有多次返回相同的行(@L_419_2@).
以下是结合上述反馈的示例：
ALTER PROC dbo.usp_YourProcname @pi_PID INT,@pi_returnExtAttr BITASBEGIN --these local variables are used to address "parameter sniffing" issues which may cause an ineffIEnt plan cache. --Use these local variables below instead of the direct parameter values. DECLARE @local_pi_PID INT = @pi_PID,@local_pi_returnOnly1stRow BIT = @pi_returnOnly1stRow,@local_pi_returnExtAttr BIT = @pi_returnExtAttr ; IF(@local_pi_returnOnly1stRow=1) BEGIN SELECT top 1 NulL section_name,cds_mspecee.displayOrder FROM cds_mspecee JOIN cds_mvocee header_mvoc ON (cds_mspecee.hdrID = header_mvoc.ID) JOIN cds_mvocee body_mvoc ON (cds_mspecee.bodyID = body_mvoc.ID) JOIN cds_prod ON (cds_Prod.prodID = cds_mspecee.prodID) JOIN Productvariant revpro On (revpro.ManufacturerSKU=cds_prod.mfpn) AND revpro.ProductID = @local_pi_PID UNION ALL SELECT section_evoc.Text section_name,cds_Especee.displayOrder FROM cds_Especee JOIN cds_Evocee section_evoc ON (cds_Especee.SectID = section_evoc.ID) JOIN cds_Evocee header_evoc ON (cds_Especee.hdrID = header_evoc.ID) JOIN cds_Evocee body_evoc ON (cds_Especee.bodyID = body_evoc.ID) JOIN cds_prod ON (cds_Prod.prodID = cds_especee.prodID) JOIN Productvariant revpro On (revpro.ManufacturerSKU=cds_prod.mfpn) AND revpro.ProductID = @local_pi_PID AND @local_pi_returnExtAttr = 1 ORDER BY section_name,cds_mspecee.displayOrder FROM cds_mspecee JOIN cds_mvocee header_mvoc ON (cds_mspecee.hdrID = header_mvoc.ID) JOIN cds_mvocee body_mvoc ON (cds_mspecee.bodyID = body_mvoc.ID) JOIN cds_prod ON (cds_Prod.prodID = cds_mspecee.prodID) JOIN productvariant revpro On (revpro.manufacturerSKU=cds_prod.mfpn) AND revpro.ProductID = @local_pi_PID UNION ALL SELECT section_evoc.Text section_name,cds_Especee.displayOrder FROM cds_Especee JOIN cds_Evocee section_evoc ON (cds_Especee.sectID = section_evoc.ID) JOIN cds_Evocee header_evoc ON (cds_Especee.hdrID = header_evoc.ID) JOIN cds_Evocee body_evoc ON (cds_Especee.bodyID = body_evoc.ID) JOIN cds_prod ON (cds_Prod.prodID = cds_especee.prodID) JOIN productvariant revpro On (revpro.manufacturerSKU=cds_prod.mfpn) AND revpro.ProductID = @local_pi_PID AND @local_pi_returnExtAttr = 1 ORDER BY section_name,displayorder ASC ENDEND总结
以上是内存溢出为你收集整理的存储过程在DB中有效,但在C#代码中无效全部内容，希望文章能够帮你解决存储过程在DB中有效,但在C#代码中无效所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）