Inserting >8000 char Ntext Timesout

  • Thread starter Thread starter Lucas Tam
  • Start date Start date
L

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.
 
Hi Lucas,

NText field and delete?
Can you show us some code?
Click to expand...

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.
 
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?
 
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?
Click to expand...

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?
 
Hi Lucas,

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