SQL Server表无法更新
这只是让我发疯。我有一个小的SQL Server 2012表,其中包含以下列:SQL Server表无法更新
id int (this is an IDENTITY column)
DateNewsletter smalldatetime
SubjectNewsletter varchar(100)
ContentHeader varchar(MAX)
ContentNewsletter varchar(MAX)
ContentFooter varchar(MAX)
NewsletterSent bit
DateSent smalldatetime
ApprovalPending bit
PriorityHigh bit
表中有583行。
这里的问题:
- 我可以
SELECT *
没有问题 - 我可以
INSERT INTO
没有问题 -
然而,当我运行下面的查询,都吹了起来:
UPDATE tblElinesNewsletter SET NewsletterSent = 1, DateSent = GETDATE(), ApprovalPending = 0 WHERE (NewsletterSent = 0)
每当我跑了ABO ve代码,我得到一个超时错误。如果我从ASP.NET页面或从SQL Server Management Studio运行它,超时错误是相同的。
另外,如果我从SSMS中右击并选择“编辑前200行......”,它会显示网格,让我编辑。但是一旦我点击它并执行更新,它就会炸毁。
我已经试过以下不已:
- 服务器
- 转到重建索引
- 那边
- 创建具有相同的架构和数据传输一个全新的表重新启动以家得宝并购买大罐可以修复办公室墙上的洞(从我的头撞到)
这不是一个大桌子。它只是存储电子邮件通讯的HTML。
它并不总是这样做,才开始约在几个月前。在此之前它运行良好。
现在,所有的上面说的,这里的起脚:它不会做这一切的时候。有时它有效。需要注意的是,如果表格中有更长的时事通讯,它似乎不起作用,如果通讯时间很短。这些都是简单的通讯,不是太花哨,这里是一个在这里:http://unitedafa.org/news/elines/view/?id=104169
这是从我上面讨论的数据库/表拉。
此SQL Server安装在Windows Server 2012中框6 GB RAM和只运行了几个网站,没有得到多少流量。
有一件事我注意到,因为我试图调试这一点,是当我从ASP.NET网页,执行SQL Server存储过程的时候失败了,这似乎冻结了SQL Server和我无法在SSMS中运行相同的存储过程。但是,一旦我重新启动服务器,我就可以打开SSMS并执行存储过程。同样,如果我从网页执行存储过程,它会再次冻结服务器,并且我无法对该表运行任何更新查询。
仅供参考,这里是一个的被从页面调用的实际存储过程:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author: Christopher Lee
-- Create date: 16-Aug-2012
-- Modify date: 16-Aug-2012
-- Description: Mark all records as Sent.
-- ======================================================
ALTER PROCEDURE [dbo].[sProc_Elines_Send_MarkComplete]
AS
BEGIN TRANSACTION
UPDATE tblElinesNewsletter
SET NewsletterSent = 1,
DateSent = GETDATE(),
ApprovalPending = 0
WHERE (NewsletterSent = 0)
COMMIT
而且,这里的CREATE TABLE
脚本:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblElinesNewsletter]
(
[id] [int] IDENTITY(100543,1) NOT NULL,
[DateNewsletter] [smalldatetime] NOT NULL,
[SubjectNewsletter] [varchar](100) NOT NULL,
[ContentHeader] [varchar](max) NOT NULL,
[ContentNewsletter] [varchar](max) NOT NULL,
[ContentFooter] [varchar](max) NOT NULL,
[NewsletterSent] [bit] NOT NULL,
[DateSent] [smalldatetime] NULL,
[ApprovalPending] [bit] NOT NULL,
[PriorityHigh] [bit] NOT NULL,
CONSTRAINT [PK_tblElinesNewsletter2]
PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblElinesNewsletter]
ADD CONSTRAINT [DF_tblElinesNewsletter2_PriorityHigh] DEFAULT ((0)) FOR [PriorityHigh]
GO
任何想法,我在做地球上的什么错误?顺便说一句,我不是一个DBA,更熟悉ASP.NET Web代码等等,比SQL语句多。
任何帮助或建议,将不胜感激!
克里斯 -
PS,这里的一些额外的信息:
在存储过程SQL Server查询执行计划:
[SQL Server的执行计划截图] [1]
这里有一些额外的信息:
- 有足够的硬盘空间。
- 没有其他并发进程。
存储过程实际上是在一个序列中执行的,其他两个序列在它之前执行。下面是从执行网页代码:
Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
Call WriteNewsletterFile()
Call SendNewsletter()
Call UpdateRecord()
End Sub
所以WriteNewsletterFile()和SendNewsletter()子程序做工精细。它在UpdateRecord子例程上爆炸。下面是每个例程的代码:
Sub WriteNewsletterFile()
Dim NewsID As String = ""
Dim Conn As SqlConnection
Dim Cmd As SqlCommand
Dim Rdr As SqlDataReader
Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("UnitedAFAConnectionStringNSS").ConnectionString)
Cmd = New SqlCommand()
Cmd.CommandText = "sProc_Elines_Send_GetContentPending"
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Connection = Conn
Cmd.Connection.Open()
Rdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection)
If Rdr.HasRows Then
While Rdr.Read
NewsID = Rdr("id")
NewsletterSubject = Rdr("SubjectNewsletter")
NewsletterHeader = Rdr("ContentHeader")
NewsletterContent = Rdr("ContentNewsletter")
NewsletterFooter = Rdr("ContentFooter")
End While
End If
Conn.Close()
Conn.Dispose()
Cmd.Dispose()
' Header
NewsletterHeaderForEmail = NewsletterHeader.Replace("<p>", "<p style=""font-family: Arial, Helvetica, sans-serif; font-size: 12pt; line-height: 1.3em;margin-bottom:1em"">")
NewsletterHeaderForEmail = NewsletterHeaderForEmail.Replace("<li>", "<li style=""font-family: Arial, Helvetica, sans-serif; font-size: 12pt; line-height: 1.3em;margin-bottom:1em"">")
NewsletterHeaderForEmail = NewsletterHeaderForEmail.Replace("[NewsID]", NewsID)
' Footer
NewsletterFooterForEmail = NewsletterFooter.Replace("<p>", "<p style=""font-family: Arial, Helvetica, sans-serif; font-size: 12pt; line-height: 1.3em;margin-bottom:1em"">")
NewsletterFooterForEmail = NewsletterFooterForEmail.Replace("<li>", "<li style=""font-family: Arial, Helvetica, sans-serif; font-size: 12pt; line-height: 1.3em;margin-bottom:1em"">")
' Content
NewsletterContentForEmail = NewsletterContent.Replace("<p>", "<p style=""font-family: Arial, Helvetica, sans-serif; font-size: 12pt; line-height: 1.3em;margin-bottom:1em"">")
NewsletterContentForEmail = NewsletterContentForEmail.Replace("<li>", "<li style=""font-family: Arial, Helvetica, sans-serif; font-size: 12pt; line-height: 1.3em;margin-bottom:1em"">")
NewsletterContentForEmail = "<table border=""0"" cellpadding=""0"" cellspacing=""0""><tr><td style=""font-family: Arial, Helvetica, sans-serif; font-size: 12pt"">" & NewsletterContentForEmail & "</td></tr></table>"
NewsletterFinalReadyForSending = NewsletterHeaderForEmail & NewsletterContentForEmail & NewsletterFooterForEmail
Dim filePath2 As String = "C:\Programs\SendElines.bat"
Dim w2 As StreamWriter
w2 = File.CreateText(filePath2)
w2.WriteLine("START """" ""C:\Program Files (x86)\Gammadyne Mailer\gm.exe"" /s /n /subject""" & NewsletterSubject & """ /html""C:\Programs\elines.html"" ""C:\Users\Public\Documents\Newsletters\Elines\Sending Template - Elines.mmp""")
w2.Flush()
w2.Close()
Dim filePath As String = "C:\Programs\elines.html"
Dim w As StreamWriter
w = File.CreateText(filePath)
w.WriteLine(NewsletterFinalReadyForSending)
w.Flush()
w.Close()
End Sub
Sub SendNewsletter()
Dim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("UnitedAFAConnectionStringAfaNewsletters").ConnectionString)
Dim MySQL As String = "sProc_SendElines"
Dim Cmd As New Data.SqlClient.SqlCommand(MySQL, Conn)
Cmd.CommandType = CommandType.StoredProcedure
Conn.Open()
Cmd.ExecuteNonQuery()
Conn.Close()
Conn.Dispose()
End Sub
Sub UpdateRecord()
Dim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("UnitedAFAConnectionStringNSS").ConnectionString)
Dim MySQL As String = "sProc_Elines_Send_MarkComplete"
Dim Cmd As New Data.SqlClient.SqlCommand(MySQL, Conn)
Cmd.CommandType = CommandType.StoredProcedure
Conn.Open()
Cmd.ExecuteNonQuery()
Conn.Close()
Conn.Dispose()
End Sub
而这里的SendElines子程序代码:
USE [afanewsletters]
GO
/****** Object: StoredProcedure [dbo].[sProc_SendElines] Script Date: 9/5/2017 2:51:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author: Christopher Lee
-- Create date: 21-Aug-2012
-- Modify date: 21-Aug-2012
-- Description: Runs the Elines sending BAT file.
-- ======================================================
ALTER PROCEDURE [dbo].[sProc_SendElines]
AS
EXEC xp_logevent 67845, 'Send Elines', informational
我要指出的是存储在SendElines()PROC是一个不同的数据库中(但在同一台服务器上)。尽管如此,要清楚的是,前两个子程序工作正常。这只是发生了sProc_Elines_Send_MarkComplete。
下面的代码为sProc_Elines_Send_GetContentPending:
USE [Newsletters]
GO
/****** Object: StoredProcedure [dbo].[sProc_Elines_Send_GetContentPending] Script Date: 9/6/2017 6:57:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author: Christopher Lee
-- Create date: 16-Aug-2012
-- Modify date: 16-Aug-2012
-- Description: Get content for Elines for approval.
-- ======================================================
ALTER PROCEDURE [dbo].[sProc_Elines_Send_GetContentPending]
AS
BEGIN TRY
BEGIN TRANSACTION
SELECT * FROM tblElinesNewsletter
WHERE (NewsletterSent = 0)
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
RAISERROR ('Problem with sProc_Elines_Send_GetContentPending, please contact the MEC Webmaster at [email protected]', 16, 1)
END CATCH
这里的DBCC OPENTRAN我跑的结果。前两个存储过程成功执行(sProc_Elines_Send_GetContentPending和sProc_SendElines)。但是,当sProc_Elines_Send_MarkComplete运行并挂起时,我运行了包含消息“没有活动的打开事务”的DBCC OPENTRAN。附上的屏幕截图:Here's the DBCC OPENTRAN screenshot
在这段代码
Rdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection)
If Rdr.HasRows Then
While Rdr.Read
....
End While
End If
Conn.Close()
Conn.Dispose()
不关闭和处置的读者。我不知道SqlConnection
和SqlDataReader
的内部结构,但是一个开放的阅读器可能会导致conn.Close()不起作用。
如果读者是开放的,这意味着由sProc_Elines_Send_GetContentPending
读取的记录和表可能被锁定,这意味着即使不再访问记录,也不会释放锁。
您还应该看看Using
声明(请参阅this question)以处理连接,命令和阅读器。
此外,您有一个While
循环,但只处理单个记录。如果Reader没有返回记录,那么最终会有很多未初始化的变量。但这与你的锁定问题无关。
嗨@devio:从代码中可以看到,数据库连接似乎正在关闭。下面是从上面的代码片段: 如果Rdr.HasRows然后 虽然RDR。读 NewSID的= RDR( “ID”) NewsletterSubject = RDR( “SubjectNewsletter”) NewsletterHeader = RDR( “ContentHeader”) NewsletterContent = RDR( “ContentNewsletter”) NewsletterFooter = RDR( “ContentFooter”) 完虽然 结束如果 Conn.Close() Conn.Dispose() Cmd.Dispose() –
“从我们都可以在代码中看到”,代码运行正常。 – devio
查询看起来很好,如果你的行少于1k,我不知道为什么它最终会超时。
您可以尝试的一件事是在顶部添加一个变量,并将CAST(GETDATE()as smalldatetime)添加到该变量中。然后在更新中使用该变量。
DECLARE @myDate smalldatetime AS CAST(GETDATE() AS smalldatetime)
UPDATE tblElinesNewsletter
SET
NewsletterSent = 1,
DateSent= myDate ,
ApprovalPending = 0
WHERE NewsletterSent = 0
我不知道它会做什么,但这个想法是日期的值存储在一个恒定的,而不是有它的WHERE子句中进行评估。
感谢SchmitzIT,我试过了,它又一次爆炸了。我也修改了UPDATE语句,只是更新tblElinesNewsletter SET ApprovalPending = 0。两个都失败了。 –
当您提升SSMS中的预计执行计划时会发生什么?它是什么显示?我唯一能想到的就是nvarchar(max)列是一个问题,因为那些被认为是blob。但是你没有触及那些更新声明。 – SchmitzIT
是的,这是疯狂的事情。现在我将听起来无知:我在哪里/如何提出估算执行计划? –
全部:
首先,感谢您的帮助。这真的是我第一次在这个广泛的问题上使用这个板子。我希望我能够看到所有的回应,因为它们都能帮助我们更清楚地了解正在发生的事情。
我解决了这个问题,部分原因是Devio说连接没有关闭。进一步挖掘.NET页面,我发现有一个会话状态数据库调用来管理页面的安全性。这是来自iframe标记,实际上是在使用相同连接字符串和数据库的不同页面上。显然这个连接字符串没有关闭,并保持打开相同的数据库和相同的整个表。然后,sProc_Elines_Send_GetContentPending过程试图运行 - 看起来像sProc_SendElines过程那样。但是,当sProc_Elines_Send_MarkComplete过程试图运行时,它失败了,因为我提到了加速器。
当我重写了数据库连接代码添加到下面的语句:
Cmd.ExecuteNonQuery()
Conn.Close()
Conn.Dispose()
然后,它关闭了安全会话状态的数据库连接,然后让所有三个其他的存储过程运行。
因此,这又解决了这个问题,并且为我解释这个问题的起源是来自德维奥的密切联系建议。
我希望我用正确的堆栈溢出协议做了这个问题关闭,请让我知道如果我错过了任何东西。再次感谢大家。你是最棒的!
- Chris
很好看,你跟踪它;) – devio
这张桌子上是否有触发器?另外,你能发布你正在得到的确切的错误信息吗? – Sparrow
您的表格没有“IDENTITY”列。也许这会有所帮助? (PS我也不是DBA) – VDWWD
小表上的超时建议阻止。 –