Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data truncation on MAX column through linked server Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 5,018, Visits: 10,523
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1522683
Posted Friday, December 13, 2013 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
It's a bug. Report it on Connect or via a CSS support call.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1522746
Posted Friday, December 13, 2013 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1522798
Posted Monday, December 16, 2013 4:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 5,018, Visits: 10,523
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1523149
Posted Monday, December 16, 2013 9:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 1,616, Visits: 2,119
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)

Internet ATM Machine
Post #1523295
Posted Monday, December 16, 2013 9:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 5,018, Visits: 10,523
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1523309
Posted Tuesday, December 17, 2013 4:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 5,018, Visits: 10,523
Bug filed.
https://connect.microsoft.com/SQLServer/feedback/details/811840/data-truncation-on-varchar-max-column-through-linked-server

Vote up please!


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1523908
Posted Wednesday, December 18, 2013 12:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257


Voted up. Waiting for the normal response of "Closed - Will not fix".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1524293
Posted Wednesday, December 18, 2013 1:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1524344
Posted Wednesday, December 18, 2013 4:37 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 1,616, Visits: 2,119
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)

Internet ATM Machine
Post #1524402
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse