This project has moved and is read-only. For the latest updates, please go here.
1
Vote

Error trying insert 30000 characters in nvarchar(max) column for more than 6000 rows

description

I'm trying to insert a 6000 row in a table.

I get error when:
try to insert a 6000 and len of bodyMessage is 30000 characters.

it works fine when:
  • len of bodyMessage if 500 characters and try to insert 6000 rows
  • len of bodyMessage if 30000 characters and try to insert<1000 rows
table definition:
CREATE TABLE [dbo].[EmailsSent](
[emailSentId] [uniqueidentifier] NOT NULL,
[notificationId] [int] NULL,
[messageId] [nvarchar](100) NULL,
[emailTo] [nvarchar](100) NULL,
[subject] [nvarchar](200) NOT NULL,
[bodyMessage] [nvarchar](max) NOT NULL,
[userId] [uniqueidentifier] NULL,
[errorMessage] [nvarchar](500) NULL,
[error] [bit] NULL,
[sent] [bit] NULL DEFAULT ((0)),
[userAdded] [nvarchar](50) NOT NULL,
[dateAdded] [datetime] NOT NULL,
[dateUpdated] [datetime] NULL,
[userUpdated] [nvarchar](50) NULL,
[recordId] [int] NULL,
PRIMARY KEY CLUSTERED
(
[emailSentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

the only diferent with other places when i used bulkinsert is "bodyMessage] nvarchar" MAX for nvarchar

I increased time to 180 seconds, but still no works.

I got following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. - System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out - at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.DrainData(TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at System.Data.SqlClient.SqlInternalTransaction.Rollback() at System.Data.SqlClient.SqlTransaction.Rollback() at EntityFramework.BulkInsert.Providers.ProviderBase2.Run[T](IEnumerable`1 entities, BulkInsertOptions options) in c:\dev\EntityFramework.BulkInsert\dev\Src\EntityFramework.BulkInsert\Providers\ProviderBase.cs:line 105 at ComeBien.Controllers.NotificationAdmController.NotificationDtl(NotificationModel notify) - System.Collections.ListDictionaryInternal



any idea what is the real problem? and how can i fix it?

comments