Bound SQL Server and Varchar Update (MAX) truncated

Question

I have several databases to support, and I don't want to create a script for each environment, so I created a synonym in combination with a linked server to abstract the tables for my update statements.

My problem is that I cannot update the varchar (max) field with more than 24-32 characters (and this is changing!)

I am researching the Internet and I can’t find anything that speaks directly to this. (UPDATE: binding to my workstation, and not to the server over the network, the local linked SQL servers are updated perfectly. I think this is an environmental problem on the part of the host? Maybe some specific limit?)

Below is an example of how I installed things.

--I create some dynamic sql to create the sysnonyms for me. declare @Synonym varchar(30) = 'sym_Employee' declare @DBTable = 'Employee' Select @Stmt = 'create synonym ' + @Synonym + ' for TargetSQLServer.' + @DBName + '.dbo.' + @DBTable + ';' exec @Stmt --this statment stops at 24 chars, but the column def is varchar(max) update sym_employee set Notes = '012345678901234567890123456789' where employee_ID = 1 

Answer 1

Have you played with setting lazy schema validation to true / false ?

More details about the link below:

http://msdn.microsoft.com/en-us/library/ms178532.aspx

Answer 2

please make sure you specify the length with the varchar data type, as if you did not specify the length with varchar, it accepts the default length (I think 20 characters), which may cause a problem.