609a1fd9   
   From: esquel@sommarskog.se   
      
   BitBuster (ivarru@gmail.com) writes:   
   > We have a linked server connection between two servers (MS SQL Server   
   > 2005), but we have problems transferring large text fields across this   
   > connection. That is to say, INSERT is fine, but SELECT replaces large   
   > text fields (> 64mb) with an empty string - with no warning.   
   >   
   > Do you know if this is an SQL Server or a network setting?   
   > And (more importantly) do you know how this limit can be increased?   
      
   I ran this on my servers at home:   
      
      declare @bobben varchar(MAX)   
      select @bobben = res   
      from   openquery(YAZORMAN, 'SELECT replicate(convert(varchar(MAX),   
                                         ''1234567890''), 7000000) as res')   
      select datalength(@bobben)   
      select @bobben = replicate(convert(varchar(MAX), '1234567890'), 7000000)   
      select datalength(@bobben)   
      
   Both selects returned 70000000 (70 millions), which is > 64 MB.   
      
   You mention "text". There are a lot of restrictions with the text   
   data type. There is also the setting SET TEXTSIZE which may be set   
   by the OLE DB provider to 64 MB.   
      
   If you are using the text data type, I stronly recommend that you   
   move to the new data type varchar(MAX), which is the same basic idea   
   as text, but which in difference to text is a first-class citizen.   
   With varchar(MAX) you done need READTEXT and all that jazz.   
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)