This is just driving me crazy. I have a small SQL Server 2012 table with the following columns:
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
There are 583 rows in the table.
Here's the problem:
- I can
SELECT *with no problem - I can
INSERT INTOwith no problem However, when I run the following query, everything blows up:
UPDATE tblElinesNewsletter SET NewsletterSent = 1, DateSent = GETDATE(), ApprovalPending = 0 WHERE (NewsletterSent = 0)
Whenever I run the above code, I get a timeout error. The timeout error is the same if I run it from ASP.NET page or from SQL Server Management Studio.
Also, if I right-click from SSMS and choose 'Edit Top 200 Rows...', it will display the grid and let me edit. But as soon as I click away and it's performing the update, it's blowing up.
I've tried the following already:
- Rebuild the index
- Create a brand new table with same schema and transfer data over there
- Restart the server
- Go to home depot and purchase large can of spackle to fix holes in office wall (from banging my head against)
This is not a large table. It simply stores HTML for an email newsletter.
It didn't always do this, and only started about a few months ago. It worked fine before that.
Now, all of the above said, here's the kicker: it doesn't do this all the time. Sometimes it works. What noticed is that if there's a longer newsletter in the table, it seems to not work vs. if the newsletter is very short. These are simple newsletters and not too fancy, here's one right here: http://unitedafa.org/news/elines/view/?id=104169
This is pulling from the database/table that I discuss above.
This SQL Server installation is on a Windows Server 2012 box with 6 GB RAM and only runs a few websites that don't get that much traffic.
One thing I'm noticing as I'm trying to debug this, is that when I execute the SQL Server stored procedure from the ASP.NET web page, when it fails, this seems to freeze up the SQL Server and I can't run the same stored procedure in SSMS. However, once I restart the server, I can then open SSMS and execute the stored procedure. Again, though if I execute the stored procedure from the web page, it 'freezes' up the server again and I can't run any update query against that table.
Just for reference, here's the actual stored procedure that's being called from the page:
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
Also, here's the CREATE TABLE script:
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
Any idea what on earth I'm doing wrong? BTW, I'm not a DBA and more familiar with ASP.NET web code, etc., more than SQL statements.
Any help or advice would be appreciated!
-- Chris
PS, here's some additional info:
SQL Server Query Execution Plan on the Stored Proc:
[SQL Server Execution Plan Screenshot][1]
Here's some additional info:
- There's enough hard drive space.
- There's no other concurrent processes.
The stored proc is actually executed in a sequence with two other before it. Here's the code from the executing page:
Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
Call WriteNewsletterFile()
Call SendNewsletter()
Call UpdateRecord()
End Sub
So the WriteNewsletterFile() and the SendNewsletter() subroutines work fine. It blows up on the UpdateRecord subroutine. Here's the code for each of the routines:
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
And here's the code for the SendElines Subroutine:
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
I should point out that the SendElines() stored proc is on a different database (but on same server). Again though, to be clear, the first two subroutines work fine. It's just the sProc_Elines_Send_MarkComplete that blows up.
Here's the code for 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 webmaster@unitedafa.org.', 16, 1)
END CATCH
Here's the results of the DBCC OPENTRAN I ran. The first two stored procs (sProc_Elines_Send_GetContentPending and sProc_SendElines) executed successfully. However, when sProc_Elines_Send_MarkComplete ran and hung, I ran the DBCC OPENTRAN which contained the message: "No active open transactions." Screenshot enclosed: Here's the DBCC OPENTRAN screenshot
