64mb limit for linked server selects?
in [Using MS SQL Server]
|
From: BitBuster on 28 Jun 2010 04:50 Dear everyone, 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? Thanks, Ivar
From: Erland Sommarskog on 28 Jun 2010 17:15 BitBuster (ivarru(a)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(a)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
From: BitBuster on 29 Jun 2010 06:29 On 28 Jun, 23:15, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > I ran this on my servers at home: > > declare @bobben varchar(MAX) > ... Thanks, but I guess I should have been more precise. In fact, I am using the data type nvarchar(max) and the value being truncated has length > 66000000 (i.e. > 128mb). I have no problem selecting 70mb from a varchar(max) column. Kind regards, Ivar
From: Erland Sommarskog on 29 Jun 2010 17:14 BitBuster (ivarru(a)gmail.com) writes: > On 28 Jun, 23:15, Erland Sommarskog <esq...(a)sommarskog.se> wrote: >> I ran this on my servers at home: >> >> � �declare @bobben varchar(MAX) >> ... > > Thanks, but I guess I should have been more precise. > > In fact, I am using the data type nvarchar(max) and the value being > truncated has length > 66000000 (i.e. > 128mb). > I have no problem selecting 70mb from a varchar(max) column. OK. I changed my script to use nvarchar instead, and I get back 140000000. Not that this proves anything. I guess your query looks slightly different. (To put it mildly.) But without a repro, it's difficult to assess the issue. But let's try to narrow it down a bit: o What happens if you run my stupid test query: declare @bobben nvarchar(MAX) select @bobben = res from openquery(SERVER, 'SELECT replicate(convert(nvarchar(MAX), ''1234567890''), 7000000) as res') select datalength(@bobben) o Which version of SQL Server do you use for the local server? The remote server? (The remote is also SQL Server, right?) o Which editions? 32-bit or 64-bit? o The remote server is really remote I presume? (Mine is a second instance on the same box.) Also, when you run your query, can you run Profiler on the remote query, and add the Errors and Warnings event category to the trace? My thinking that this is some resource constraint, that may leads to an error which for some reason is not reported, but I'm just speculating. -- Erland Sommarskog, SQL Server MVP, esquel(a)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
From: BitBuster on 30 Jun 2010 12:17
On 29 Jun, 23:14, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > > o What happens if you run my stupid test query: > > declare @bobben nvarchar(MAX) > select @bobben = res from openquery(SERVER, 'SELECT > replicate(convert(nvarchar(MAX), ''1234567890''), 7000000) as res') > select datalength(@bobben) > > o Which version of SQL Server do you use for the local server? The > remote server? (The remote is also SQL Server, right?) I get 0 from both my linked servers! (... but if I write 3000000 instead of 7000000, the result is 60000000.) One is a SQL Server 2008 Express Edition on a (virtual) 64 bit Windows Server 2008 located in a so-called DMZ in the same building. The other is a SQL Server 2005 Standard Edition on a (virtual) 32 bit Windows Server 2003 somewhere far away. My local server is similar (to the second one). > Also, when you run your query, can you run Profiler on the remote > query, and add the Errors and Warnings event category to the trace? I will try that tomorrow. Thanks for your patience! -- Ivar |