[Solution] the length of the LOB data to be copied exceeds the configured maximum value.

During SQL Server 2005 database usage, JDBC Driver exception: com. microsoft. sqlserver. jdbc. SQLServerException: the length of the LOB data to be copied (144377) exceeds the configured maximum of 65536.

 

  Query a lot of information and provide a solution on the Internet, hoping:

  

  After searching for some information, we found that one of the server configuration options"Max Text Repl Size" ("Max Text copy Size"). Its default value is 65536. You can modify this value to solve the above problem.

SELECT * FROM sys. tolerations where name like '% repl %'

The maximum value of max text repl size (B) is 2147483647. You can set the value smaller than or equal to this value according to the actual situation.

For example:

 

EXEC sp_configure 'show advanced options', 1
Go
Sp_configure 'max text repl size', '20140901'
Go
RECONFIGURE

 

The reason for online data query is that SQL SERVER sets an UPDATE, INSERT, WRITETEXT, and UPDATETEXT statement in replication to add the maximum data volume to the replication column65536Byte (64 k), for columns with a data volume greater than 64 k, it is not automatically intercepted, but cannot be inserted or updated to ensure data integrity. The solution is as follows:

You can use the max text repl size Option to reset the data size (in bytes) of the text and image Fields ). The default value is 64 KB and the maximum value is 2 GB. This option is only applicable to transaction replication. This option is ignored during snapshot copy and merge copy. This setting takes effect immediately without restarting the server. Method 1:
In SQL Server Management Studio, configure the max text repl size Option: 1. In object Resource Manager, right-click the Server and select "properties ".
2. Click the "advanced" node.
3. Under miscellaneous, change the "maximum text copy size" option to the required value. Method 2:
Use sp_configure to modify the max text repl size value. Run the following command in the query Analyzer: Sp_configure 'max text repl size', '20140901'This command re-sets the max text repl size to 2 GB (that is, sets the storage capacity of the image field to 2 GB). Note: Command Format: sp_configure 'name of the configuration option ''' option value'