strange problem with varchar(8000) getting cut at 4000 characters

Question

Post reply

strange problem with varchar(8000) getting cut at 4000 characters

Viewing 15 posts - 16 through 30 (of 50 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The DDL for the table ETLPreparedStatement, please.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

Here you are:

USE [ETL]

GO

/****** Object: Table [dbo].[ETLPreparedStatement] Script Date: 4/27/2016 2:32:23 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ETLPreparedStatement](

[PreparedStatementID] [int] IDENTITY(1,1) NOT NULL,

[ETLMappingID] [int] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_ETLMappingID] DEFAULT ((0)),

[BatchID] [int] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_BatchID] DEFAULT ((0)),

[DestinationDB] [varchar](200) NOT NULL CONSTRAINT [DF_ETLPreparedStatement_DestinationDB] DEFAULT ('Not Defined or NULL'),

[DestinationTable] [varchar](200) NOT NULL CONSTRAINT [DF_ETLPreparedStatement_DestinationTable] DEFAULT ('Not Defined or NULL'),

[DependencyLevel] [int] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_DependencyLevel] DEFAULT ((999)),

[PreparedStatement] [varchar](max) NOT NULL CONSTRAINT [DF_ETLPreparedStatement_PreparedStatement] DEFAULT ('Not Defined or NULL'),

[StatementType] [varchar](50) NOT NULL CONSTRAINT [DF_ETLPreparedStatement_StatementType] DEFAULT ('Not Defined or NULL'),

[HasFK] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_HasFKInStatement] DEFAULT ((0)),

[NoDependentProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_NoDependentProcessed] DEFAULT ((0)),

[JoinProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_JoinProcessed] DEFAULT ((0)),

[FROMWHEREProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_FROMWHEREProcessed] DEFAULT ((0)),

[ETLMergeProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_ETLMergeProcessed] DEFAULT ((0)),

[CorrelationProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_CorrelationProcessed] DEFAULT ((0)),

[PreparedStatement2] [varchar](8000) NULL,

[RecordDate] [datetime] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_RecordDate] DEFAULT (getdate()),

CONSTRAINT [PK_ETLPreparedStatement] PRIMARY KEY CLUSTERED

(

[PreparedStatementID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I may work on this more tonight, but I can't replicate your problem.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 4

In the Print statements when the proc runs; everything is fine.

The problem comes in updating the table, I am limited to 4000 characters for some reason.

If I come up with anything I will let everyone know. I appreciate everyone's efforts on this. Really strange problem.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Bill Talada SSChampion Points: 11954 More actions · Answer 5

I suspect a trigger might be converting it to nvarchar and back.

There may be implicit conversions to nvarchar and back if you use some built-in functions.

I suspect SSMS query environment may be lying to you, showing only 4k of 8k; check the options for text output, grid char lengths, etc.

Try selecting datalength(var_or_col) everywhere you can.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 6

Thank you but no triggers, no functions being called. I am the only developer on this and this is a standalone table.

I have not only done a query for the length but I have copied and pasted the row/column value and performed a LEN on that as well, same answer.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Bill Talada SSChampion Points: 11954 More actions · Answer 7

Have you checked your SSMS tools-options text and grid output truncate length settings?

Maybe characterset settings are affecting things.

Are you concatenating a zero byte in the middle? A zero byte might truncate string output.

I don't trust the PRINT command so I wrote my own function to print out large strings.

IF EXISTS

(

SELECT *

FROM sys.procedures

WHERE name = 'PrintMax'

AND type = N'P'

AND schema_id = SCHEMA_ID('dbo')

)

DROP PROCEDURE dbo.PrintMax

GO

CREATE PROCEDURE dbo.PrintMax

@vcmax varchar(max)

AS

DECLARE

@start bigint,

@end bigint,

@crlf varchar(2);

SET @crlf = CHAR(13)+CHAR(10);

IF LEN(@vcmax)=0 RETURN 0;

-- start/end will contain one line with a crlf except maybe not on last one

SET @start = 1;

SET @end = CHARINDEX(@crlf, @vcmax, @start) + 1;

IF @end = 1 SET @end = LEN(@vcmax);

WHILE @start <= @end

BEGIN

PRINT SUBSTRING(@vcmax, @start, @end - @start + 1);

-- next line

SET @start = @end + 1;

SET @end = CHARINDEX(@crlf, @vcmax, @start) + 1;

IF @end = 1 SET @end = LEN(@vcmax);

END

RETURN 0

GO

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 8

I will check settings but I don't think that his it.

See when the stored procedure runs I have print statements that will show on screen what my built string is. and what is showing on screen is correct, I can copy it over to a query window and run it.

When UPDATE the column in sql is where the problem lies, it is only taking the first 4000 characters.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Sorry, cannot see where did you answered this:

Does PRINT LEN (@preparedStatement) return >4000 ever?

Do you actually submit lengthy string to the UPDATE?

_____________
Code for TallyGenerator

Bill Talada SSChampion Points: 11954 More actions · Answer 10

I run this on customer sites to make sure SSMS text won't get chopped.

-- First run this test

-- is ssms configured for 8192 char output in text mode?

SET NOCOUNT ON;

DECLARE @vc varchar(MAX);

SET @vc = REPLICATE('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678'+CHAR(13)+CHAR(10),80);

SELECT @vc;

PRINT 'this should be line 84 and line 82 should be 98 chars long plus crlf. line 1 is blank and line two is underlines.'

GO

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 11

I know it is something with a server setting or something, not code.

Made another table varchar(8000)

I inserted few hundred characters then just did update where field = field + field.

When the next insert would exceed 4000; I get a truncation error and no more inserts.

Anyone have an idea what that might be?

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Jeffery Williams (4/27/2016)
I know it is something with a server setting or something, not code.
Made another table varchar(8000)
I inserted few hundred characters then just did update where field = field + field.
When the next insert would exceed 4000; I get a truncation error and no more inserts.
Anyone have an idea what that might be?

Post the code and DDL you used for this.

Bill Talada SSChampion Points: 11954 More actions · Answer 13

Run these to see what your server settings are. The last column is the value I would expect.

select 'ServerSetting', 'DefaultLangID', @@DEFAULT_LANGID, '0'

UNION all

select 'ServerSetting', 'DefSortOrderID', @@DEF_SORTORDER_ID, '52'

UNION all

select 'ServerSetting', 'LangID', @@LANGID, '0'

select 'ServerSetting', 'Language', @@LANGUAGE, 'us_english'

;

select 'ServerProperty', 'Collation', cast(SERVERPROPERTY('Collation') as varchar(300)), 'SQL_Latin1_General_CP1_CI_AS'

union all

select 'ServerProperty', 'SqlCharSet', cast(SERVERPROPERTY('SqlCharSet') as varchar(300)), '1'

UNION all

select 'ServerProperty', 'SqlCharSetName', cast(SERVERPROPERTY('SqlCharSetName') as varchar(300)), 'iso_1'

UNION all

select 'ServerProperty', 'SqlSortOrder', cast(SERVERPROPERTY('SqlSortOrder') as varchar(300)), '52'

UNION all

select 'ServerProperty', 'SqlSortOrderName', cast(SERVERPROPERTY('SqlSortOrderName') as varchar(300)), 'nocase_iso';

Sergiy SSC Guru Points: 110208 More actions · Answer 14

Jeffery,

I made the following script from the pieces you've posted:

DECLARE @BatchIDas int

,@PreparedStatementas varchar(8000)

,@SourceServerName NVARCHAR (128)

,@SourceDBName NVARCHAR (128)

,@SourceSchemaName NVARCHAR (128)

,@PKTableName NVARCHAR (128)

,@Alias NVARCHAR (128)

, @PKColumnName NVARCHAR (128)

,@FKColumnName NVARCHAR (128)

,@ETLMappingIDAS int

,@SQL1as varchar(4000)

,@SQL2as varchar(4000)

SELECT *

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

SELECT TOP 1 @BatchID = eps.PreparedStatementID + 1

FROM dbo.ETLPreparedStatement eps

ORDER BY eps.PreparedStatementID DESC

SELECT TOP 1

@SourceServerName = @@SERVERNAME, @SourceDBName = CONSTRAINT_CATALOG, @SourceSchemaName = kcu.CONSTRAINT_SCHEMA, @PKTableName = kcu.TABLE_NAME,

@Alias = LEFT(kcu.TABLE_NAME, 2) , @PKColumnName = kcu.COLUMN_NAME, @FKColumnName = kcu.COLUMN_NAME,

@PreparedStatement = ''

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

WHERE kcu.COLUMN_NAME > ''

SELECT @SourceServerName , @SourceDBName , @SourceSchemaName , @PKTableName , @Alias , @PKColumnName , @FKColumnName

SET @SQL1 = ' JOIN [' + @SourceServerName + '].[' + @SourceDBName + '].['

+ @SourceSchemaName + '].[' + @PKTableName

+ '] as ' + @Alias + 'Table on ' + @Alias + 'Table.' + @PKColumnName + ' = st.' + @FKColumnName

SELECT @SQL1

SET @SQL2 = ' JOIN [ETL].[dbo].[ETLCorrelation] ' + @Alias + ' on ' + @Alias + '.SourceGUIDVal = st.' + @FKColumnName

+ ' AND ' + @Alias + '.SourceTable = ''' + @PKTableName + '''' + ' AND ' + @Alias + '.BatchID = ' + cast(@BatchID as varchar(10))

INSERT INTO dbo.ETLPreparedStatement ( PreparedStatement)

SELECT ''

PRINT '@SQL1: ' + cast(@SQL1 as varchar(4000))

PRINT '@SQL2: ' + cast(@SQL2 as varchar(4000))

WHILE LEN (@PreparedStatement) < 5000

BEGIN

SET @PreparedStatement = @PreparedStatement + ' ' + cast(@SQL1 as varchar(4000)) + ' ' + cast(@SQL2 as varchar(4000))

END

PRINT '@PreparedStatement: ' + cast(@PreparedStatement as varchar(8000))

IF LEN(@PreparedStatement) > 5000

BEGIN

UPDATE ETLPreparedStatement

SET PreparedStatement = cast(@PreparedStatement as varchar(8000)), JoinProcessed = 1

WHERE PreparedStatementID = @BatchID

END

SELECT eps.PreparedStatement, LEN(eps.PreparedStatement)

from dbo.ETLPreparedStatement eps

SET @PreparedStatement = cast(@SQL1 as varchar(4000)) + ' ' + cast(@SQL2 as varchar(4000))

UPDATE ETLPreparedStatement

SET PreparedStatement = PreparedStatement + CAST(@PreparedStatement as varchar(8000)), JoinProcessed = 1

WHERE PreparedStatementID = @BatchID

SELECT eps.PreparedStatement, LEN(eps.PreparedStatement)

from dbo.ETLPreparedStatement eps

LEN values returned by the highlighted selects:

5175,

5399

No problem here.

You're definitely barking at the wrong tree.

Once again - check you execution plan for implicit conversions to NVARCHAR.

Or look where do you use @SQL1 instead of @PreparedStatement.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 15

To add to that, if you are, in fact, using SQL Server 2012, make sure that you're at SP3 with the latest CU. There were a couple of problems in the system prior to that and this could be a strange symptom of one of them.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)