LINQ Large string (64k) not inserted into SQL Database

Question

I have a column in SQL varchar(max). Using LINQ, when I'm trying to add a record with a string over 64k large, the value is not saved into DB. I'm simply doing;

test.MyString = LargeString; //(over 64k text)
mydb.myTable.InsertOnSubmit(test);
mydb.SubmitChanges();

Is there a limit when using LINQ to SQL ? What's the correct way to do this?

Skep

Are you getting some exception? Or just the column turns out empty? What happens if you assign a smaller string like "test string"? – Citify

Was the size of the field just recently increased to MAX? If so, you may need to recompile the DBML – Platte

I'm not getting any exception, and te column turns empty (not NULL) No problem with smaller string. I also removed the table from my DBML et readded the table. Same problem. – Skep

I finally upgraded from SQL 2005 to SQL 2008 R2. Now it's working, but I have no idea why this was not working under SQL 2005. – Skep

Answer 1

VarChar(Max) supports string of upto 8000 characters (SQL Server 2k). On 2005+ this should not cause error.

You may want to use Text fields; for SQL Server 2005+, VarChar(Max) is preferred.

Important

ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

See this for details on VarChar & Text data type

1. SQL Server Text type vs. varchar data type
2. MSDN: ntext, text, and image

---

I noticed you are calling InsertOnSubmit on mydb and calling SubmitChanges on ISNetDB - is this typo or cause of the error.

Womble

Yes, it's a typo. Should be mydb.SubmitChanges – Skep