Inserting >8000 char Ntext Timesout

[Lucas Tam]

Is there something special I need to do to insert large Ntext fields?

Small Ntext fields insert/update/delete fine, however, larger one(8K+) time
out on updates and deletes.

Any suggestions would be helpful.

Thanks.

 

[Miha Markic [MVP C#]]

Hi Lucas,

NText field and delete?
Can you show us some code?

 

[Lucas Tam]

Hi Lucas,

NText field and delete?
Can you show us some code?

I'm using Microsoft Application Blocks:

Dim strSQL As String
Dim params(6) As SqlParameter
Dim recordsAffected As Integer = 0
params(0) = New SqlParameter("@Status", ddlStatus.SelectedItem.Value)
params(1) = New SqlParameter("@LanguageID",
ddlLanguages.SelectedItem.Value)
params(2) = New SqlParameter("@Title", txtTitle.Text)
params(3) = New SqlParameter("@Content", rteContent.Text.Trim)
params(4) = New SqlParameter("@UserID", userID)
params(5) = New SqlParameter("@UpdateDate", Date.Today.ToString("d"))
params(6) = New SqlParameter("@ArticleID", articleID)
strSQL = "UPDATE articles SET Status=@Status, LanguageID=@LanguageID ,
Title=@Title, Content=@Content, " _
& "UserID=@UserID, UpdateDate=@UpdateDate " _
& "WHERE ArticleID = @ArticleID;"

recordsAffected = SqlHelper.ExecuteNonQuery(GLOBAL_DB_CONNECTION_STRING,
CommandType.Text, strSQL, params)


So:

1. Insert works fine for any size of text
2. Update does not work >8000 char - timeout
3. Delete does not work >8000 char - timeout
4. Update/Delete works fine for < 8000 char

Update Delete SEEMS to work 5 - 10 minutes after an insert... however, if
I immediately update or delete after an insert, I get a Timeout error.

I need to test the above observation about the 5 - 10 minute wait.

Any help will be appreciated.

 

[Miha Markic [MVP C#]]

Hi Lucas,

Try defining ntext parameter more explicitly, like:
params(3) = New SqlParameter("@Content", SqlDbType.NText, ....)
params(3).Value = rteContent.Text.Trim.

However, I can't figure out what ntext fields has to do with delete
statament.
Is it possible that you are locking a transaction somewhere?

 

[Lucas Tam]

Try defining ntext parameter more explicitly, like:
params(3) = New SqlParameter("@Content", SqlDbType.NText, ....)
params(3).Value = rteContent.Text.Trim.

However, I can't figure out what ntext fields has to do with delete
statament.
Is it possible that you are locking a transaction somewhere?

I tried this too. Doesn't work.


Yes, it seem the transaction is locking. I ran SQL profiler and when it
hits anything to do with the articles table (any updates/deletes) it locks.

Inserts are fine.

I can't figure out for the life of me why it's locking. Any ideas?

 

[Miha Markic [MVP C#]]

Hi Lucas,

Try similar operation from within Query analyzer and see if it also locks.
Plus, check out the database for triggers or something.