December 13, 2013 at 7:22 am
I stumbled upon a very strange behavior of MAX datatypes through linked server.
When inserting data to a column with a non default collation, data gets truncated randomly at around 30 characters.
The same thing does not happen with non MAX varchars or text columns.
Setting the collation of the column to the default database collation fixes.
Here's a repro script with a loopback linked server. Same thing happens with a linked server actually pointing to a different instance.
DECLARE @srv nvarchar(4000);
SET @srv = @@SERVERNAME; -- gather this server name
-- Create the linked server
IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = 'LOOPBACK')
BEGIN
EXEC master.dbo.sp_addlinkedserver
@server = N'LOOPBACK',
@srvproduct = N'SQLServ',
@provider = N'SQLNCLI',
@datasrc = @srv;
-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LOOPBACK',
@useself = N'True',
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword = NULL;
END
USE tempdb;
GO
IF OBJECT_ID('testMax') IS NOT NULL
DROP TABLE testMax;
GO
CREATE TABLE testMax (
someColumn varchar(max) COLLATE DATABASE_DEFAULT,
someColumnWithDifferentCollation varchar(max) COLLATE SQL_Latin1_General_CP850_CI_AS,
aTextColumnWithDifferentCollation text COLLATE SQL_Latin1_General_CP850_CI_AS,
someColumnWithDifferentCollationButNotMax varchar(8000) COLLATE SQL_Latin1_General_CP850_CI_AS
)
GO
DECLARE @someValue varchar(max)
SET @someValue = REPLICATE(CAST('a' AS varchar(max)) , 60000)
-- INSERT USING LINKED SERVER: DOESN'T WORK
INSERT INTO LOOPBACK.tempdb.dbo.testMax
SELECT @someValue, @someValue, @someValue, LEFT(@someValue, 8000);
SELECT someColumn,
LEN(someColumn) AS LenOfSomeColumn,
someColumnWithDifferentCollation,
LEN(someColumnWithDifferentCollation) AS LenOfsomeColumnWithDifferentCollation,
aTextColumnWithDifferentCollation,
LEN(CAST(aTextColumnWithDifferentCollation AS varchar(max))) AS LenOfaTextColumnWithDifferentCollation,
someColumnWithDifferentCollationButNotMax,
LEN(someColumnWithDifferentCollationButNotMax) AS LenOfsomeColumnWithDifferentCollationButNotMax
FROM testMax;
DELETE FROM testMax;
--INSERT WITHOUT USING THE LINKED SERVER: WORKS
INSERT INTO tempdb.dbo.testMax
SELECT @someValue, @someValue, @someValue, LEFT(@someValue, 8000);
SELECT someColumn,
LEN(someColumn) AS LenOfSomeColumn,
someColumnWithDifferentCollation,
LEN(someColumnWithDifferentCollation) AS LenOfsomeColumnWithDifferentCollation,
aTextColumnWithDifferentCollation,
LEN(CAST(aTextColumnWithDifferentCollation AS varchar(max))) AS LenOfaTextColumnWithDifferentCollation,
someColumnWithDifferentCollationButNotMax,
LEN(someColumnWithDifferentCollationButNotMax) AS LenOfsomeColumnWithDifferentCollationButNotMax
FROM testMax;
Ideas?
UPDATE: Same behavior found on SQL Server 2008 R2.
UPDATE2: Looks like it's specific to collation SQL_Latin1_General_CP850_CI_AS. I tried with some other collations and no truncation happens.
-- Gianluca Sartori
December 13, 2013 at 9:11 am
It's a bug. Report it on Connect or via a CSS support call.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 13, 2013 at 10:36 am
The good thing is: your test also fails here ( sql2012 SP1 CU7 (Build number: 11.0.3393.0))
I've also tested it with a couple of other 850-collations. All fail !
These are the 3 850-collations I tested
someColumn850Collation varchar(max) COLLATE SQL_Scandinavian_CP850_CI_AS
-- SQL_Latin1_General_Pref_CP850_CI_AS
-- SQL_1xCompat_CP850_CI_AS
As Paul stated, a bug seems to have appeared 🙁
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2013 at 4:21 am
Thanks guys.
Apparently, it's not limited to 850 codepage, but it happens with lots of collations.
I tested it with all possible collations and it fails with the following ones:
Albanian_BIN
Albanian_BIN2
Albanian_CI_AI
Albanian_CI_AI_WS
Albanian_CI_AI_KS
Albanian_CI_AI_KS_WS
Albanian_CI_AS
Albanian_CI_AS_WS
Albanian_CI_AS_KS
Albanian_CI_AS_KS_WS
Albanian_CS_AI
Albanian_CS_AI_WS
Albanian_CS_AI_KS
Albanian_CS_AI_KS_WS
Albanian_CS_AS
Albanian_CS_AS_WS
Albanian_CS_AS_KS
Albanian_CS_AS_KS_WS
Arabic_BIN
Arabic_BIN2
Arabic_CI_AI
Arabic_CI_AI_WS
Arabic_CI_AI_KS
Arabic_CI_AI_KS_WS
Arabic_CI_AS
Arabic_CI_AS_WS
Arabic_CI_AS_KS
Arabic_CI_AS_KS_WS
Arabic_CS_AI
Arabic_CS_AI_WS
Arabic_CS_AI_KS
Arabic_CS_AI_KS_WS
Arabic_CS_AS
Arabic_CS_AS_WS
Arabic_CS_AS_KS
Arabic_CS_AS_KS_WS
Chinese_PRC_BIN
Chinese_PRC_BIN2
Chinese_PRC_CI_AI
Chinese_PRC_CI_AI_WS
Chinese_PRC_CI_AI_KS
Chinese_PRC_CI_AI_KS_WS
Chinese_PRC_CI_AS
Chinese_PRC_CI_AS_WS
Chinese_PRC_CI_AS_KS
Chinese_PRC_CI_AS_KS_WS
Chinese_PRC_CS_AI
Chinese_PRC_CS_AI_WS
Chinese_PRC_CS_AI_KS
Chinese_PRC_CS_AI_KS_WS
Chinese_PRC_CS_AS
Chinese_PRC_CS_AS_WS
Chinese_PRC_CS_AS_KS
Chinese_PRC_CS_AS_KS_WS
Chinese_PRC_Stroke_BIN
Chinese_PRC_Stroke_BIN2
Chinese_PRC_Stroke_CI_AI
Chinese_PRC_Stroke_CI_AI_WS
Chinese_PRC_Stroke_CI_AI_KS
Chinese_PRC_Stroke_CI_AI_KS_WS
Chinese_PRC_Stroke_CI_AS
Chinese_PRC_Stroke_CI_AS_WS
Chinese_PRC_Stroke_CI_AS_KS
Chinese_PRC_Stroke_CI_AS_KS_WS
Chinese_PRC_Stroke_CS_AI
Chinese_PRC_Stroke_CS_AI_WS
Chinese_PRC_Stroke_CS_AI_KS
Chinese_PRC_Stroke_CS_AI_KS_WS
Chinese_PRC_Stroke_CS_AS
Chinese_PRC_Stroke_CS_AS_WS
Chinese_PRC_Stroke_CS_AS_KS
Chinese_PRC_Stroke_CS_AS_KS_WS
Chinese_Taiwan_Bopomofo_BIN
Chinese_Taiwan_Bopomofo_BIN2
Chinese_Taiwan_Bopomofo_CI_AI
Chinese_Taiwan_Bopomofo_CI_AI_WS
Chinese_Taiwan_Bopomofo_CI_AI_KS
Chinese_Taiwan_Bopomofo_CI_AI_KS_WS
Chinese_Taiwan_Bopomofo_CI_AS
Chinese_Taiwan_Bopomofo_CI_AS_WS
Chinese_Taiwan_Bopomofo_CI_AS_KS
Chinese_Taiwan_Bopomofo_CI_AS_KS_WS
Chinese_Taiwan_Bopomofo_CS_AI
Chinese_Taiwan_Bopomofo_CS_AI_WS
Chinese_Taiwan_Bopomofo_CS_AI_KS
Chinese_Taiwan_Bopomofo_CS_AI_KS_WS
Chinese_Taiwan_Bopomofo_CS_AS
Chinese_Taiwan_Bopomofo_CS_AS_WS
Chinese_Taiwan_Bopomofo_CS_AS_KS
Chinese_Taiwan_Bopomofo_CS_AS_KS_WS
Chinese_Taiwan_Stroke_BIN
Chinese_Taiwan_Stroke_BIN2
Chinese_Taiwan_Stroke_CI_AI
Chinese_Taiwan_Stroke_CI_AI_WS
Chinese_Taiwan_Stroke_CI_AI_KS
Chinese_Taiwan_Stroke_CI_AI_KS_WS
Chinese_Taiwan_Stroke_CI_AS
Chinese_Taiwan_Stroke_CI_AS_WS
Chinese_Taiwan_Stroke_CI_AS_KS
Chinese_Taiwan_Stroke_CI_AS_KS_WS
Chinese_Taiwan_Stroke_CS_AI
Chinese_Taiwan_Stroke_CS_AI_WS
Chinese_Taiwan_Stroke_CS_AI_KS
Chinese_Taiwan_Stroke_CS_AI_KS_WS
Chinese_Taiwan_Stroke_CS_AS
Chinese_Taiwan_Stroke_CS_AS_WS
Chinese_Taiwan_Stroke_CS_AS_KS
Chinese_Taiwan_Stroke_CS_AS_KS_WS
Croatian_BIN
Croatian_BIN2
Croatian_CI_AI
Croatian_CI_AI_WS
Croatian_CI_AI_KS
Croatian_CI_AI_KS_WS
Croatian_CI_AS
Croatian_CI_AS_WS
Croatian_CI_AS_KS
Croatian_CI_AS_KS_WS
Croatian_CS_AI
Croatian_CS_AI_WS
Croatian_CS_AI_KS
Croatian_CS_AI_KS_WS
Croatian_CS_AS
Croatian_CS_AS_WS
Croatian_CS_AS_KS
Croatian_CS_AS_KS_WS
Cyrillic_General_BIN
Cyrillic_General_BIN2
Cyrillic_General_CI_AI
Cyrillic_General_CI_AI_WS
Cyrillic_General_CI_AI_KS
Cyrillic_General_CI_AI_KS_WS
Cyrillic_General_CI_AS
Cyrillic_General_CI_AS_WS
Cyrillic_General_CI_AS_KS
Cyrillic_General_CI_AS_KS_WS
Cyrillic_General_CS_AI
Cyrillic_General_CS_AI_WS
Cyrillic_General_CS_AI_KS
Cyrillic_General_CS_AI_KS_WS
Cyrillic_General_CS_AS
Cyrillic_General_CS_AS_WS
Cyrillic_General_CS_AS_KS
Cyrillic_General_CS_AS_KS_WS
Czech_BIN
Czech_BIN2
Czech_CI_AI
Czech_CI_AI_WS
Czech_CI_AI_KS
Czech_CI_AI_KS_WS
Czech_CI_AS
Czech_CI_AS_WS
Czech_CI_AS_KS
Czech_CI_AS_KS_WS
Czech_CS_AI
Czech_CS_AI_WS
Czech_CS_AI_KS
Czech_CS_AI_KS_WS
Czech_CS_AS
Czech_CS_AS_WS
Czech_CS_AS_KS
Czech_CS_AS_KS_WS
Estonian_BIN
Estonian_BIN2
Estonian_CI_AI
Estonian_CI_AI_WS
Estonian_CI_AI_KS
Estonian_CI_AI_KS_WS
Estonian_CI_AS
Estonian_CI_AS_WS
Estonian_CI_AS_KS
Estonian_CI_AS_KS_WS
Estonian_CS_AI
Estonian_CS_AI_WS
Estonian_CS_AI_KS
Estonian_CS_AI_KS_WS
Estonian_CS_AS
Estonian_CS_AS_WS
Estonian_CS_AS_KS
Estonian_CS_AS_KS_WS
Greek_BIN
Greek_BIN2
Greek_CI_AI
Greek_CI_AI_WS
Greek_CI_AI_KS
Greek_CI_AI_KS_WS
Greek_CI_AS
Greek_CI_AS_WS
Greek_CI_AS_KS
Greek_CI_AS_KS_WS
Greek_CS_AI
Greek_CS_AI_WS
Greek_CS_AI_KS
Greek_CS_AI_KS_WS
Greek_CS_AS
Greek_CS_AS_WS
Greek_CS_AS_KS
Greek_CS_AS_KS_WS
Hebrew_BIN
Hebrew_BIN2
Hebrew_CI_AI
Hebrew_CI_AI_WS
Hebrew_CI_AI_KS
Hebrew_CI_AI_KS_WS
Hebrew_CI_AS
Hebrew_CI_AS_WS
Hebrew_CI_AS_KS
Hebrew_CI_AS_KS_WS
Hebrew_CS_AI
Hebrew_CS_AI_WS
Hebrew_CS_AI_KS
Hebrew_CS_AI_KS_WS
Hebrew_CS_AS
Hebrew_CS_AS_WS
Hebrew_CS_AS_KS
Hebrew_CS_AS_KS_WS
Hungarian_BIN
Hungarian_BIN2
Hungarian_CI_AI
Hungarian_CI_AI_WS
Hungarian_CI_AI_KS
Hungarian_CI_AI_KS_WS
Hungarian_CI_AS
Hungarian_CI_AS_WS
Hungarian_CI_AS_KS
Hungarian_CI_AS_KS_WS
Hungarian_CS_AI
Hungarian_CS_AI_WS
Hungarian_CS_AI_KS
Hungarian_CS_AI_KS_WS
Hungarian_CS_AS
Hungarian_CS_AS_WS
Hungarian_CS_AS_KS
Hungarian_CS_AS_KS_WS
Hungarian_Technical_BIN
Hungarian_Technical_BIN2
Hungarian_Technical_CI_AI
Hungarian_Technical_CI_AI_WS
Hungarian_Technical_CI_AI_KS
Hungarian_Technical_CI_AI_KS_WS
Hungarian_Technical_CI_AS
Hungarian_Technical_CI_AS_WS
Hungarian_Technical_CI_AS_KS
Hungarian_Technical_CI_AS_KS_WS
Hungarian_Technical_CS_AI
Hungarian_Technical_CS_AI_WS
Hungarian_Technical_CS_AI_KS
Hungarian_Technical_CS_AI_KS_WS
Hungarian_Technical_CS_AS
Hungarian_Technical_CS_AS_WS
Hungarian_Technical_CS_AS_KS
Hungarian_Technical_CS_AS_KS_WS
Japanese_BIN
Japanese_BIN2
Japanese_CI_AI
Japanese_CI_AI_WS
Japanese_CI_AI_KS
Japanese_CI_AI_KS_WS
Japanese_CI_AS
Japanese_CI_AS_WS
Japanese_CI_AS_KS
Japanese_CI_AS_KS_WS
Japanese_CS_AI
Japanese_CS_AI_WS
Japanese_CS_AI_KS
Japanese_CS_AI_KS_WS
Japanese_CS_AS
Japanese_CS_AS_WS
Japanese_CS_AS_KS
Japanese_CS_AS_KS_WS
Japanese_Unicode_BIN
Japanese_Unicode_BIN2
Japanese_Unicode_CI_AI
Japanese_Unicode_CI_AI_WS
Japanese_Unicode_CI_AI_KS
Japanese_Unicode_CI_AI_KS_WS
Japanese_Unicode_CI_AS
Japanese_Unicode_CI_AS_WS
Japanese_Unicode_CI_AS_KS
Japanese_Unicode_CI_AS_KS_WS
Japanese_Unicode_CS_AI
Japanese_Unicode_CS_AI_WS
Japanese_Unicode_CS_AI_KS
Japanese_Unicode_CS_AI_KS_WS
Japanese_Unicode_CS_AS
Japanese_Unicode_CS_AS_WS
Japanese_Unicode_CS_AS_KS
Japanese_Unicode_CS_AS_KS_WS
Korean_Wansung_BIN
Korean_Wansung_BIN2
Korean_Wansung_CI_AI
Korean_Wansung_CI_AI_WS
Korean_Wansung_CI_AI_KS
Korean_Wansung_CI_AI_KS_WS
Korean_Wansung_CI_AS
Korean_Wansung_CI_AS_WS
Korean_Wansung_CI_AS_KS
Korean_Wansung_CI_AS_KS_WS
Korean_Wansung_CS_AI
Korean_Wansung_CS_AI_WS
Korean_Wansung_CS_AI_KS
Korean_Wansung_CS_AI_KS_WS
Korean_Wansung_CS_AS
Korean_Wansung_CS_AS_WS
Korean_Wansung_CS_AS_KS
Korean_Wansung_CS_AS_KS_WS
Latvian_BIN
Latvian_BIN2
Latvian_CI_AI
Latvian_CI_AI_WS
Latvian_CI_AI_KS
Latvian_CI_AI_KS_WS
Latvian_CI_AS
Latvian_CI_AS_WS
Latvian_CI_AS_KS
Latvian_CI_AS_KS_WS
Latvian_CS_AI
Latvian_CS_AI_WS
Latvian_CS_AI_KS
Latvian_CS_AI_KS_WS
Latvian_CS_AS
Latvian_CS_AS_WS
Latvian_CS_AS_KS
Latvian_CS_AS_KS_WS
Lithuanian_BIN
Lithuanian_BIN2
Lithuanian_CI_AI
Lithuanian_CI_AI_WS
Lithuanian_CI_AI_KS
Lithuanian_CI_AI_KS_WS
Lithuanian_CI_AS
Lithuanian_CI_AS_WS
Lithuanian_CI_AS_KS
Lithuanian_CI_AS_KS_WS
Lithuanian_CS_AI
Lithuanian_CS_AI_WS
Lithuanian_CS_AI_KS
Lithuanian_CS_AI_KS_WS
Lithuanian_CS_AS
Lithuanian_CS_AS_WS
Lithuanian_CS_AS_KS
Lithuanian_CS_AS_KS_WS
Polish_BIN
Polish_BIN2
Polish_CI_AI
Polish_CI_AI_WS
Polish_CI_AI_KS
Polish_CI_AI_KS_WS
Polish_CI_AS
Polish_CI_AS_WS
Polish_CI_AS_KS
Polish_CI_AS_KS_WS
Polish_CS_AI
Polish_CS_AI_WS
Polish_CS_AI_KS
Polish_CS_AI_KS_WS
Polish_CS_AS
Polish_CS_AS_WS
Polish_CS_AS_KS
Polish_CS_AS_KS_WS
Romanian_BIN
Romanian_BIN2
Romanian_CI_AI
Romanian_CI_AI_WS
Romanian_CI_AI_KS
Romanian_CI_AI_KS_WS
Romanian_CI_AS
Romanian_CI_AS_WS
Romanian_CI_AS_KS
Romanian_CI_AS_KS_WS
Romanian_CS_AI
Romanian_CS_AI_WS
Romanian_CS_AI_KS
Romanian_CS_AI_KS_WS
Romanian_CS_AS
Romanian_CS_AS_WS
Romanian_CS_AS_KS
Romanian_CS_AS_KS_WS
Slovak_BIN
Slovak_BIN2
Slovak_CI_AI
Slovak_CI_AI_WS
Slovak_CI_AI_KS
Slovak_CI_AI_KS_WS
Slovak_CI_AS
Slovak_CI_AS_WS
Slovak_CI_AS_KS
Slovak_CI_AS_KS_WS
Slovak_CS_AI
Slovak_CS_AI_WS
Slovak_CS_AI_KS
Slovak_CS_AI_KS_WS
Slovak_CS_AS
Slovak_CS_AS_WS
Slovak_CS_AS_KS
Slovak_CS_AS_KS_WS
Slovenian_BIN
Slovenian_BIN2
Slovenian_CI_AI
Slovenian_CI_AI_WS
Slovenian_CI_AI_KS
Slovenian_CI_AI_KS_WS
Slovenian_CI_AS
Slovenian_CI_AS_WS
Slovenian_CI_AS_KS
Slovenian_CI_AS_KS_WS
Slovenian_CS_AI
Slovenian_CS_AI_WS
Slovenian_CS_AI_KS
Slovenian_CS_AI_KS_WS
Slovenian_CS_AS
Slovenian_CS_AS_WS
Slovenian_CS_AS_KS
Slovenian_CS_AS_KS_WS
Thai_BIN
Thai_BIN2
Thai_CI_AI
Thai_CI_AI_WS
Thai_CI_AI_KS
Thai_CI_AI_KS_WS
Thai_CI_AS
Thai_CI_AS_WS
Thai_CI_AS_KS
Thai_CI_AS_KS_WS
Thai_CS_AI
Thai_CS_AI_WS
Thai_CS_AI_KS
Thai_CS_AI_KS_WS
Thai_CS_AS
Thai_CS_AS_WS
Thai_CS_AS_KS
Thai_CS_AS_KS_WS
Turkish_BIN
Turkish_BIN2
Turkish_CI_AI
Turkish_CI_AI_WS
Turkish_CI_AI_KS
Turkish_CI_AI_KS_WS
Turkish_CI_AS
Turkish_CI_AS_WS
Turkish_CI_AS_KS
Turkish_CI_AS_KS_WS
Turkish_CS_AI
Turkish_CS_AI_WS
Turkish_CS_AI_KS
Turkish_CS_AI_KS_WS
Turkish_CS_AS
Turkish_CS_AS_WS
Turkish_CS_AS_KS
Turkish_CS_AS_KS_WS
Ukrainian_BIN
Ukrainian_BIN2
Ukrainian_CI_AI
Ukrainian_CI_AI_WS
Ukrainian_CI_AI_KS
Ukrainian_CI_AI_KS_WS
Ukrainian_CI_AS
Ukrainian_CI_AS_WS
Ukrainian_CI_AS_KS
Ukrainian_CI_AS_KS_WS
Ukrainian_CS_AI
Ukrainian_CS_AI_WS
Ukrainian_CS_AI_KS
Ukrainian_CS_AI_KS_WS
Ukrainian_CS_AS
Ukrainian_CS_AS_WS
Ukrainian_CS_AS_KS
Ukrainian_CS_AS_KS_WS
Vietnamese_BIN
Vietnamese_BIN2
Vietnamese_CI_AI
Vietnamese_CI_AI_WS
Vietnamese_CI_AI_KS
Vietnamese_CI_AI_KS_WS
Vietnamese_CI_AS
Vietnamese_CI_AS_WS
Vietnamese_CI_AS_KS
Vietnamese_CI_AS_KS_WS
Vietnamese_CS_AI
Vietnamese_CS_AI_WS
Vietnamese_CS_AI_KS
Vietnamese_CS_AI_KS_WS
Vietnamese_CS_AS
Vietnamese_CS_AS_WS
Vietnamese_CS_AS_KS
Vietnamese_CS_AS_KS_WS
SQL_1xCompat_CP850_CI_AS
SQL_AltDiction_CP850_CI_AI
SQL_AltDiction_CP850_CI_AS
SQL_AltDiction_CP850_CS_AS
SQL_AltDiction_Pref_CP850_CI_AS
SQL_AltDiction2_CP1253_CS_AS
SQL_Croatian_CP1250_CI_AS
SQL_Croatian_CP1250_CS_AS
SQL_Czech_CP1250_CI_AS
SQL_Czech_CP1250_CS_AS
SQL_Estonian_CP1257_CI_AS
SQL_Estonian_CP1257_CS_AS
SQL_Hungarian_CP1250_CI_AS
SQL_Hungarian_CP1250_CS_AS
SQL_Latin1_General_CP1250_CI_AS
SQL_Latin1_General_CP1250_CS_AS
SQL_Latin1_General_CP1251_CI_AS
SQL_Latin1_General_CP1251_CS_AS
SQL_Latin1_General_CP1253_CI_AI
SQL_Latin1_General_CP1253_CI_AS
SQL_Latin1_General_CP1253_CS_AS
SQL_Latin1_General_CP1254_CI_AS
SQL_Latin1_General_CP1254_CS_AS
SQL_Latin1_General_CP1255_CI_AS
SQL_Latin1_General_CP1255_CS_AS
SQL_Latin1_General_CP1256_CI_AS
SQL_Latin1_General_CP1256_CS_AS
SQL_Latin1_General_CP1257_CI_AS
SQL_Latin1_General_CP1257_CS_AS
SQL_Latin1_General_CP437_BIN
SQL_Latin1_General_CP437_BIN2
SQL_Latin1_General_CP437_CI_AI
SQL_Latin1_General_CP437_CI_AS
SQL_Latin1_General_CP437_CS_AS
SQL_Latin1_General_CP850_BIN
SQL_Latin1_General_CP850_BIN2
SQL_Latin1_General_CP850_CI_AI
SQL_Latin1_General_CP850_CI_AS
SQL_Latin1_General_CP850_CS_AS
SQL_Latin1_General_Pref_CP437_CI_AS
SQL_Latin1_General_Pref_CP850_CI_AS
SQL_Latvian_CP1257_CI_AS
SQL_Latvian_CP1257_CS_AS
SQL_Lithuanian_CP1257_CI_AS
SQL_Lithuanian_CP1257_CS_AS
SQL_MixDiction_CP1253_CS_AS
SQL_Polish_CP1250_CI_AS
SQL_Polish_CP1250_CS_AS
SQL_Romanian_CP1250_CI_AS
SQL_Romanian_CP1250_CS_AS
SQL_Scandinavian_CP850_CI_AS
SQL_Scandinavian_CP850_CS_AS
SQL_Scandinavian_Pref_CP850_CI_AS
SQL_Slovak_CP1250_CI_AS
SQL_Slovak_CP1250_CS_AS
SQL_Slovenian_CP1250_CI_AS
SQL_Slovenian_CP1250_CS_AS
SQL_Ukrainian_CP1251_CI_AS
SQL_Ukrainian_CP1251_CS_AS
With nvarchar(MAX) everything works fine.
I'm checking my repro script and will file a bug on connect.
-- Gianluca Sartori
December 16, 2013 at 9:18 am
Just curious about the impact of changing the following statement:
SET @someValue = REPLICATE(CAST('a' AS varchar(max)) , 60000)
to:
SET @someValue = CAST(REPLICATE('a' , 60000) AS varchar(max))
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 16, 2013 at 9:31 am
sgmunson (12/16/2013)
Just curious about the impact of changing the following statement:
SET @someValue = REPLICATE(CAST('a' AS varchar(max)) , 60000)
to:
SET @someValue = CAST(REPLICATE('a' , 60000) AS varchar(max))
REPLICATE interprets arguments as non-MAX types unless explicitly passed in as such.
The syntax you are suggesting would truncate to 8000, which is better than 32, but not what I'm after yet 🙂
-- Gianluca Sartori
December 17, 2013 at 4:49 pm
Bug filed.
Vote up please! 🙂
-- Gianluca Sartori
December 18, 2013 at 12:09 pm
spaghettidba (12/17/2013)
Bug filed.Vote up please! 🙂
Voted up. Waiting for the normal response of "Closed - Will not fix". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2013 at 1:31 pm
Jeff Moden (12/18/2013)
Voted up. Waiting for the normal response of "Closed - Will not fix". 😉
Also up-voted and marked as reproducible. Wrong-results bugs usually get a high priority.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 18, 2013 at 4:37 pm
Have you tried using nvarchar(max) to see if it is also affected? Somehow, I get the strange feeling that the other collations are resulting in interpreting what is otherwise part of a character as the end of the field, and thus the truncation.... Although I would have liked to think that the marker for end of field would be collation independent... Or am I just grasping at straws?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 18, 2013 at 7:33 pm
Paul White (12/18/2013)
Jeff Moden (12/18/2013)
Voted up. Waiting for the normal response of "Closed - Will not fix". 😉Also up-voted and marked as reproducible. Wrong-results bugs usually get a high priority.
Excellent tip. Thanks, Paul.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2013 at 2:27 am
sgmunson (12/18/2013)
Have you tried using nvarchar(max) to see if it is also affected? Somehow, I get the strange feeling that the other collations are resulting in interpreting what is otherwise part of a character as the end of the field, and thus the truncation.... Although I would have liked to think that the marker for end of field would be collation independent... Or am I just grasping at straws?
It could be anything. Who knows?
nvarchar(max) is not affected, so maybe you nailed it.
-- Gianluca Sartori
December 19, 2013 at 11:05 pm
Every once in a while, I get lucky... and while it's almost always easier to be lucky than good, I try rather hard not to depend on luck...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 3, 2015 at 11:17 am
I ran into the nvarchar(max) issue where the returned value is NULL. If I run code (four part name) at the linked server, it returns the text; if I run the exact same code via a linked server, it returns NULL (e.g. Server A returns text; Server B with a linked server to Server A returns NULL).
BTW, I'm attempting to pull the "filter_definition" column out of the sys.indexes table.
May 22, 2019 at 8:07 am
Very late reply I know, but has there been any progress on this? I have exactly the same problem remotely querying the "filter_definition" column out of the sys.indexes table. I'm running SQL2014 SP2.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply