Result truncation bug
-
Here's an interesting riddle. I'm building a code generator, so the SELECT clause is just one great big concatenation of a bunch of stuff. I have 1 line of code that depending upon where it is placed either returns a correct output or it truncates the output. The code is below and attached. I've run this against SQL Server 2016 and 2017 from SSMS 17.3 on 2 different machines. It behaves the same on both machines and against both versions of SQL Server. The comment in the middle of the code is precisely where the issue is.
If
REPLACE(b.TempTableColumnDef,b.TempTableColumnDef,'') +is either at this precise location or ANY line above this, I get a full, correct output. (No, I'm not done, so there are parts which aren't syntactically correct yet.)
If you moveREPLACE(b.TempTableColumnDef,b.TempTableColumnDef,'') +down 1 line in the code, the result you get will truncate the output of this stringREPLACE(u.ColList,',' ,',' + CHAR(10) + ' ')approximately 4000 characters in while correctly outputting all of the remainder of the string below it, INCLUDING the 2nd occurrence of outputtingREPLACE(u.ColList,',' ,',' + CHAR(10) + ' ').If you move the line with
REPLACE(b.TempTableColumnDef,b.TempTableColumnDef,'') +down TWO or more lines in the SELECT, the truncation of the result string is even more severe. If you comment out that line, the result string truncation is more severe. It does not matter if you have a static character value. It also does not matter, as I've done, if you literally wipe out the contents of b.TempTableColumnDef by turning it into an empty string. As long as the concatenation of that empty string exists in the SELECT clause at or above the location below, the result string is NOT truncated. If you put it anywhere below where it currently is, it truncates the result string.Do I need this thing in the code generator any more? No. But, I can't remove it and get a complete string.
USE msdb;
GODECLARE @SourceSchema SYSNAME = 'dbo',
@SourceTable SYSNAME = 'backupset',
@TargetDatabase SYSNAME = 'tempdb',
@TargetSchema SYSNAME = 'dbo',
@TargetTable SYSNAME = 'backupset',
@MergeType INT,
@ColumnNameLength INT,
@DataTypeLength INT,
@DataTypeStartPosition INT,
@NullStartPosition INT;IF @TargetSchema IS NULL
BEGIN
SET @TargetSchema = @SourceSchema;
END;IF @TargetTable IS NULL
BEGIN
SET @TargetTable = @SourceTable;
END;SELECT @ColumnNameLength = MAX(LEN(b.name)),
@DataTypeLength = MAX(LEN(UPPER(c.name) + CASE WHEN c.name IN ('nvarchar','nchar') THEN '(' + REPLACE(CAST(b.max_length/2 AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('varchar','char') THEN '(' + REPLACE(CAST(b.max_length AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('decimal','numeric') THEN '(' + CAST(b.precision AS VARCHAR(30)) + ',' + CAST(b.scale AS VARCHAR(30)) + ')'
ELSE '' END))
FROM sys.tables a INNER JOIN sys.columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.system_type_id = c.system_type_id
WHERE a.schema_id = SCHEMA_ID(@SourceSchema) AND a.name = @SourceTable AND b.is_column_set = 0;SET @DataTypeStartPosition = (((@ColumnNameLength / 4) + 1) * 4) - 1;
SET @NullStartPosition = ((@DataTypeLength / 4) + 1) * 4;WITH ColCTE AS
(SELECT DISTINCT CASE WHEN b.name LIKE '%-%' THEN '['+b.name+']' ELSE b.name END name, b.column_id, CASE WHEN b.column_id = e.column_id THEN 'Yes' ELSE 'No' END KeyColumn,
'<datatypespace>' + UPPER(c.name) + CASE WHEN c.name IN ('nvarchar','nchar') THEN '(' + REPLACE(CAST(b.max_length/2 AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('varchar','char') THEN '(' + REPLACE(CAST(b.max_length AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('decimal','numeric') THEN '(' + CAST(b.precision AS VARCHAR(30)) + ',' + CAST(b.scale AS VARCHAR(30)) + ')'
ELSE '' END + '<nullspace>' + CASE WHEN b.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END ColDef, c.name DataType, LEN(b.name) ColumnNameLength,
LEN(UPPER(c.name) + CASE WHEN c.name IN ('nvarchar','nchar') THEN '(' + REPLACE(CAST(b.max_length/2 AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('varchar','char') THEN '(' + REPLACE(CAST(b.max_length AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('decimal','numeric') THEN '(' + CAST(b.precision AS VARCHAR(30)) + ',' + CAST(b.scale AS VARCHAR(30)) + ')'
ELSE '' END) DataTypeLength
FROM sys.tables a INNER JOIN sys.columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.system_type_id = c.system_type_id
LEFT OUTER JOIN sys.indexes d ON b.object_id = d.object_id
LEFT OUTER JOIN sys.index_columns e ON d.object_id = e.object_id AND d.index_id = e.index_id AND e.column_id = b.column_id
WHERE a.schema_id = SCHEMA_ID(@SourceSchema) AND a.name = @SourceTable AND d.is_primary_key = 1 AND c.name <> 'sysname' AND b.is_column_set = 0),
DeletedExistsCTE
AS
(SELECT MAX(CASE WHEN name = 'Deleted' THEN 1 ELSE 0 END) DeleteExists
FROM ColCTE),
TempColumnDefCTE
AS
(SELECT CAST(1 AS VARCHAR(MAX)) TempTableColumnDef),
SourceSelectColumnListCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ', ' + name
FROM ColCTE
ORDER BY column_id
FOR XML PATH ('')),1,1,''))) AS VARCHAR(8000)) ColList),
KeyColumnCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ' AND tgt.' + name + ' = src.' + name
FROM ColCTE
WHERE KeyColumn = 'Yes'
ORDER BY column_id
FOR XML PATH ('')),1,4,''))) AS VARCHAR(8000)) ColList),
UpdateSetClauseCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ', tgt.' + name + ' = src.' + name
FROM ColCTE
WHERE name NOT IN ('RowHash') AND KeyColumn = 'No'
ORDER BY column_id
FOR XML PATH ('')),1,1,''))) AS VARCHAR(8000)) ColList),
InsertColumnListCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ', ' + name
FROM ColCTE
WHERE name NOT IN ('RowHash')
ORDER BY column_id
FOR XML PATH ('')),1,1,''))) AS VARCHAR(8000)) ColList),
InsertSourceColumnListCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ', src.' + name
FROM ColCTE
WHERE name NOT IN ('RowHash')
ORDER BY column_id
FOR XML PATH ('')),1,1,''))) AS VARCHAR(8000)) ColList),
VarcharMaxColsCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ' OR tgt.' + name + ' <> src.' + name
FROM ColCTE
WHERE ColDef LIKE '%VARCHAR(MAX)%'
ORDER BY column_id
FOR XML PATH ('')),1,4,''))) AS VARCHAR(8000)) ColList)
SELECT 'CREATE PROCEDURE ' + @SourceSchema + '.asp_Merge' + @SourceTable + ' @InitialLoad CHAR(3), @ProcessExceptions CHAR(3), @ServerExecutionID BIGINT' + CHAR(10) + 'AS' + CHAR(10) +
'SET NOCOUNT ON;' + CHAR(10) + 'DECLARE @LoadDate DATETIME;' + CHAR(10) + CHAR(13) +
'IF @ProcessExceptions = ''Yes''' + CHAR(10) + 'BEGIN' + CHAR(10) + ' DECLARE @ExceptionProcess TABLE' + CHAR(10) + ' (LoadDate DATETIME NOT NULL);' + CHAR(10) + 'END;' + CHAR(10) + CHAR(13) +
'IF @ProcessExceptions = ''Yes''' + CHAR(10) + 'BEGIN' + CHAR(10) + ' INSERT INTO @ExceptionProcess' +
CHAR(10) + ' (LoadDate)' + CHAR(10) + ' SELECT DISTINCT DWLoadDate' + CHAR(10) + ' FROM ' + @TargetDatabase + '.zException' + @SourceSchema + '.' + @SourceTable + CHAR(10) + ' WHERE ErrorDescription = ''Orphan ' +
@SourceTable + ''';' + CHAR(10) + CHAR(13) + ' SELECT @LoadDate = MIN(LoadDate)' + CHAR(10) + ' FROM @ExceptionProcess;' + CHAR(10) + CHAR(13) + ' WHILE @LoadDate IS NOT NULL' +
CHAR(10) + ' BEGIN' + CHAR(10) + ' DELETE FROM @ExceptionProcess' + CHAR(10) + ' WHERE LoadDate = @LoadDate;' + CHAR(10) + CHAR(13) + ' TRUNCATE TABLE ' + @SourceSchema + '.' +
@SourceTable + ';' + CHAR(10) + ' TRUNCATE TABLE #' + @SourceTable + ';' + CHAR(10) + CHAR(13) + ' INSERT INTO ' + @SourceSchema + '.' + @SourceTable + CHAR(10) + ' (' +
f.ColList + ')' + CHAR(10) + ' SELECT DISTINCT ' + REPLACE(REPLACE(f.ColList,'ServerExecutionID, ',''),'LoadDate','') + '@ServerExecutionID, CURRENT_TIMESTAMP' + CHAR(10) +
' FROM ' + @TargetDatabase + '.zException' + @SourceSchema + '.' + @SourceTable + CHAR(10) +
' WHERE DWLoadDate = @LoadDate AND ErrorDescription = ''Orphan ' + @SourceTable + ''';' + CHAR(10) +
' --Foreign Key check' + CHAR(10) +
' --Remove orphans' + CHAR(10) +
' --Remove rows that are no longer orphaned' + CHAR(10) +
' WITH SourceCTE' + CHAR(10) + ' AS' + CHAR(10) + ' (SELECT ' + c.ColList + CHAR(10) + ' FROM ' + @SourceSchema + '.' + @SourceTable + ')' + CHAR(10) +
' MERGE INTO ' + @TargetDatabase + '.' + @TargetSchema + '.' + @TargetTable + ' WITH (HOLDLOCK) AS tgt' + CHAR(10) +
' USING SourceCTE AS src ON ' + d.ColList + CHAR(10) + ' WHEN MATCHED AND src.RowHash <> tgt.RowHash THEN' + CHAR(10) +
' UPDATE' + CHAR(10) +
/************************************************************************/
--I don't have the slightest clue. If you remove b.ColList from the SELECT statement, the result is truncated. You can manipulate it into an empty string and it will return a full result
-- BUT, ANY removal or replacement of b.ColList causes result truncation. The contents of that CTE does not matter. The value in the column does not matter. You can set it to ANYTHING, you just can not remove it.
--If you move this one line of code anywhere ABOVE this point, a correct result is produced.
--If you move this one line of code down 1 line, the result is truncated literally in the middle of the output of u.ColList while still correctly outputting everything else below this line.
--If you move this one line of code down 2 or more lines, the truncation of the result string is even more severe.
REPLACE(b.TempTableColumnDef,b.TempTableColumnDef,'') +
/************************************************************************/
' SET ' + REPLACE(u.ColList,',' ,',' + CHAR(10) + ' ') + CHAR(10) +
CASE WHEN de.DeleteExists = 1 THEN ' WHEN MATCHED AND src.Deleted = 1 THEN DELETE' + CHAR(10) ELSE '' END +
' WHEN NOT MATCHED THEN' + CHAR(10) + ' INSERT (' + f.ColList + ')' + CHAR(10) +
' VALUES(' + g.ColList + ');' + CHAR(10) + CHAR(13) +
' SELECT @LoadDate = MIN(LoadDate)' + CHAR(10) + ' FROM @ExceptionProcess;' + CHAR(10) + ' END;' +
CHAR(10) + 'END;' + CHAR(10) + 'ELSE' + CHAR(10) + 'BEGIN' + CHAR(10) +
' --Foreign key check' + CHAR(10) + ' --Add logging to exception for orphans' +
CHAR(10) + ' INSERT INTO ' + @TargetDatabase + '.zException' + @SourceSchema + '.' + @SourceTable + CHAR(10) + ' (' + f.ColList + ', ErrorCode, ErrorColumn, ErrorDescription)' + CHAR(10) +
' SELECT ' + f.ColList + ', 0, 0, ''Orphan ' + @SourceTable + '''' + CHAR(10) + ' FROM ' + @SourceSchema + '.' + @SourceTable + CHAR(10) +
' WHERE IS NULL;' + CHAR(10) + CHAR(13) + ' --Remove the orphaned rows from Staging table' + CHAR(10) + ' DELETE FROM ' + @SourceSchema + '.' + @SourceTable + CHAR(10) +
' WHERE IS NULL;' + CHAR(10) + CHAR(13) +
' WITH SourceCTE' + CHAR(10) + ' AS' + CHAR(10) + ' (SELECT ' + c.ColList +
CHAR(10) + ' FROM ' + @SourceSchema + '.' + @SourceTable + ')' + CHAR(10) +
' MERGE INTO ' + @TargetDatabase + '.' + @TargetSchema + '.' + @TargetTable + ' WITH (HOLDLOCK) AS tgt' + CHAR(10) +
' USING SourceCTE AS src ON ' + d.ColList + CHAR(10) + ' WHEN MATCHED AND src.RowHash <> tgt.RowHash THEN' + CHAR(10) +
' UPDATE' + CHAR(10) + ' SET ' + REPLACE(u.ColList,',' ,',' + CHAR(10) + ' ') + CHAR(10) +
CASE WHEN de.DeleteExists = 1 THEN ' WHEN MATCHED AND src.Deleted = 1 THEN DELETE' + CHAR(10) ELSE '' END +
' WHEN NOT MATCHED THEN' + CHAR(10) + ' INSERT (' + f.ColList + ')' + CHAR(10) +
' VALUES(' + g.ColList + ');' + CHAR(10) +
'END;', b.TempTableColumnDef
FROM sys.tables a CROSS JOIN SourceSelectColumnListCTE c
CROSS JOIN KeyColumnCTE d
CROSS JOIN InsertColumnListCTE f
CROSS JOIN InsertSourceColumnListCTE g
CROSS JOIN VarcharMaxColsCTE h
CROSS JOIN UpdateSetClauseCTE u
CROSS JOIN DeletedExistsCTE de
CROSS JOIN TempColumnDefCTE b
WHERE a.schema_id = SCHEMA_ID(@SourceSchema) AND a.name = @SourceTableMichael Hotek
-
Before I read any further, did you try running this via sqlcmd in a batch file?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Request 2: please save both query plans off as files and do a text-based comparison of them. My suspicion, as I bet yours since I know you know SQL Server very well, is that the query plan is logically/boolean correct but results in a (hopefully unintended and "bug") consequence.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Try changing varchar(8000) to varchar(max). Worked for me
USE msdb;
GODECLARE @SourceSchema SYSNAME = 'dbo',
@SourceTable SYSNAME = 'backupset',
@TargetDatabase SYSNAME = 'tempdb',
@TargetSchema SYSNAME = 'dbo',
@TargetTable SYSNAME = 'backupset',
@MergeType INT,
@ColumnNameLength INT,
@DataTypeLength INT,
@DataTypeStartPosition INT,
@NullStartPosition INT;IF @TargetSchema IS NULL
BEGIN
SET @TargetSchema = @SourceSchema;
END;IF @TargetTable IS NULL
BEGIN
SET @TargetTable = @SourceTable;
END;SELECT @ColumnNameLength = MAX(LEN(b.name)),
@DataTypeLength = MAX(LEN(UPPER(c.name) + CASE WHEN c.name IN ('nvarchar','nchar') THEN '(' + REPLACE(CAST(b.max_length/2 AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('varchar','char') THEN '(' + REPLACE(CAST(b.max_length AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('decimal','numeric') THEN '(' + CAST(b.precision AS VARCHAR(30)) + ',' + CAST(b.scale AS VARCHAR(30)) + ')'
ELSE '' END))
FROM sys.tables a INNER JOIN sys.columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.system_type_id = c.system_type_id
WHERE a.schema_id = SCHEMA_ID(@SourceSchema) AND a.name = @SourceTable AND b.is_column_set = 0;SET @DataTypeStartPosition = (((@ColumnNameLength / 4) + 1) * 4) - 1;
SET @NullStartPosition = ((@DataTypeLength / 4) + 1) * 4;WITH ColCTE AS
(SELECT DISTINCT CASE WHEN b.name LIKE '%-%' THEN '['+b.name+']' ELSE b.name END name, b.column_id, CASE WHEN b.column_id = e.column_id THEN 'Yes' ELSE 'No' END KeyColumn,
'<datatypespace>' + UPPER(c.name) + CASE WHEN c.name IN ('nvarchar','nchar') THEN '(' + REPLACE(CAST(b.max_length/2 AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('varchar','char') THEN '(' + REPLACE(CAST(b.max_length AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('decimal','numeric') THEN '(' + CAST(b.precision AS VARCHAR(30)) + ',' + CAST(b.scale AS VARCHAR(30)) + ')'
ELSE '' END + '<nullspace>' + CASE WHEN b.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END ColDef, c.name DataType, LEN(b.name) ColumnNameLength,
LEN(UPPER(c.name) + CASE WHEN c.name IN ('nvarchar','nchar') THEN '(' + REPLACE(CAST(b.max_length/2 AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('varchar','char') THEN '(' + REPLACE(CAST(b.max_length AS VARCHAR(30)),'-1','MAX') + ')'
WHEN c.name IN ('decimal','numeric') THEN '(' + CAST(b.precision AS VARCHAR(30)) + ',' + CAST(b.scale AS VARCHAR(30)) + ')'
ELSE '' END) DataTypeLength
FROM sys.tables a INNER JOIN sys.columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.system_type_id = c.system_type_id
LEFT OUTER JOIN sys.indexes d ON b.object_id = d.object_id
LEFT OUTER JOIN sys.index_columns e ON d.object_id = e.object_id AND d.index_id = e.index_id AND e.column_id = b.column_id
WHERE a.schema_id = SCHEMA_ID(@SourceSchema) AND a.name = @SourceTable AND d.is_primary_key = 1 AND c.name <> 'sysname' AND b.is_column_set = 0),
DeletedExistsCTE
AS
(SELECT MAX(CASE WHEN name = 'Deleted' THEN 1 ELSE 0 END) DeleteExists
FROM ColCTE),
TempColumnDefCTE
AS
(SELECT CAST(1 AS VARCHAR(MAX)) TempTableColumnDef),
SourceSelectColumnListCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ', ' + name
FROM ColCTE
ORDER BY column_id
FOR XML PATH ('')),1,1,''))) AS VARCHAR(max)) ColList),
KeyColumnCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ' AND tgt.' + name + ' = src.' + name
FROM ColCTE
WHERE KeyColumn = 'Yes'
ORDER BY column_id
FOR XML PATH ('')),1,4,''))) AS VARCHAR(max)) ColList),
UpdateSetClauseCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ', tgt.' + name + ' = src.' + name
FROM ColCTE
WHERE name NOT IN ('RowHash') AND KeyColumn = 'No'
ORDER BY column_id
FOR XML PATH ('')),1,1,''))) AS VARCHAR(max)) ColList),
InsertColumnListCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ', ' + name
FROM ColCTE
WHERE name NOT IN ('RowHash')
ORDER BY column_id
FOR XML PATH ('')),1,1,''))) AS VARCHAR(max)) ColList),
InsertSourceColumnListCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ', src.' + name
FROM ColCTE
WHERE name NOT IN ('RowHash')
ORDER BY column_id
FOR XML PATH ('')),1,1,''))) AS VARCHAR(max)) ColList),
VarcharMaxColsCTE
AS
(SELECT CAST(LTRIM(RTRIM(STUFF((SELECT ' OR tgt.' + name + ' <> src.' + name
FROM ColCTE
WHERE ColDef LIKE '%VARCHAR(MAX)%'
ORDER BY column_id
FOR XML PATH ('')),1,4,''))) AS VARCHAR(max)) ColList)SELECT 'CREATE PROCEDURE ' + @SourceSchema + '.asp_Merge' + @SourceTable + ' @InitialLoad CHAR(3), @ProcessExceptions CHAR(3), @ServerExecutionID BIGINT' + CHAR(10) + 'AS' + CHAR(10) +
'SET NOCOUNT ON;' + CHAR(10) + 'DECLARE @LoadDate DATETIME;' + CHAR(10) + CHAR(13) +
'IF @ProcessExceptions = ''Yes''' + CHAR(10) + 'BEGIN' + CHAR(10) + ' DECLARE @ExceptionProcess TABLE' + CHAR(10) + ' (LoadDate DATETIME NOT NULL);' + CHAR(10) + 'END;' + CHAR(10) + CHAR(13) +
'IF @ProcessExceptions = ''Yes''' + CHAR(10) + 'BEGIN' + CHAR(10) + ' INSERT INTO @ExceptionProcess' +
CHAR(10) + ' (LoadDate)' + CHAR(10) + ' SELECT DISTINCT DWLoadDate' + CHAR(10) + ' FROM ' + @TargetDatabase + '.zException' + @SourceSchema + '.' + @SourceTable + CHAR(10) + ' WHERE ErrorDescription = ''Orphan ' +
@SourceTable + ''';' + CHAR(10) + CHAR(13) + ' SELECT @LoadDate = MIN(LoadDate)' + CHAR(10) + ' FROM @ExceptionProcess;' + CHAR(10) + CHAR(13) + ' WHILE @LoadDate IS NOT NULL' +
CHAR(10) + ' BEGIN' + CHAR(10) + ' DELETE FROM @ExceptionProcess' + CHAR(10) + ' WHERE LoadDate = @LoadDate;' + CHAR(10) + CHAR(13) + ' TRUNCATE TABLE ' + @SourceSchema + '.' +
@SourceTable + ';' + CHAR(10) + ' TRUNCATE TABLE #' + @SourceTable + ';' + CHAR(10) + CHAR(13) + ' INSERT INTO ' + @SourceSchema + '.' + @SourceTable + CHAR(10) + ' (' +
f.ColList + ')' + CHAR(10) + ' SELECT DISTINCT ' + REPLACE(REPLACE(f.ColList,'ServerExecutionID, ',''),'LoadDate','') + '@ServerExecutionID, CURRENT_TIMESTAMP' + CHAR(10) +
' FROM ' + @TargetDatabase + '.zException' + @SourceSchema + '.' + @SourceTable + CHAR(10)
+ ' WHERE DWLoadDate = @LoadDate AND ErrorDescription = ''Orphan ' + @SourceTable + ''';' + CHAR(10) +
' --Foreign Key check' + CHAR(10) +
' --Remove orphans' + CHAR(10) +
' --Remove rows that are no longer orphaned' + CHAR(10) +
' WITH SourceCTE' + CHAR(10) + ' AS' + CHAR(10) + ' (SELECT ' + c.ColList + CHAR(10) + ' FROM ' + @SourceSchema + '.' + @SourceTable + ')' + CHAR(10) +
' MERGE INTO ' + @TargetDatabase + '.' + @TargetSchema + '.' + @TargetTable + ' WITH (HOLDLOCK) AS tgt' + CHAR(10) +
' USING SourceCTE AS src ON ' + d.ColList + CHAR(10) + ' WHEN MATCHED AND src.RowHash <> tgt.RowHash THEN' + CHAR(10) +
' UPDATE' + CHAR(10) +
--/************************************************************************/
----I don't have the slightest clue. If you remove b.ColList from the SELECT statement, the result is truncated. You can manipulate it into an empty string and it will return a full result
---- BUT, ANY removal or replacement of b.ColList causes result truncation. The contents of that CTE does not matter. The value in the column does not matter. You can set it to ANYTHING, you just can not remove it.
----If you move this one line of code anywhere ABOVE this point, a correct result is produced.
----If you move this one line of code down 1 line, the result is truncated literally in the middle of the output of u.ColList while still correctly outputting everything else below this line.
----If you move this one line of code down 2 or more lines, the truncation of the result string is even more severe.
REPLACE(b.TempTableColumnDef,b.TempTableColumnDef,'') +
--/************************************************************************/
' SET ' + REPLACE(u.ColList,',' ,',' + CHAR(10) + ' ') + CHAR(10) +
CASE WHEN de.DeleteExists = 1 THEN ' WHEN MATCHED AND src.Deleted = 1 THEN DELETE' + CHAR(10) ELSE '' END +
' WHEN NOT MATCHED THEN' + CHAR(10) + ' INSERT (' + f.ColList + ')' + CHAR(10) +
' VALUES(' + g.ColList + ');' + CHAR(10) + CHAR(13) +
' SELECT @LoadDate = MIN(LoadDate)' + CHAR(10) + ' FROM @ExceptionProcess;' + CHAR(10) + ' END;' +
CHAR(10) + 'END;' + CHAR(10) + 'ELSE' + CHAR(10) + 'BEGIN' + CHAR(10) +
' --Foreign key check' + CHAR(10) + ' --Add logging to exception for orphans' +
CHAR(10) + ' INSERT INTO ' + @TargetDatabase + '.zException' + @SourceSchema + '.' + @SourceTable + CHAR(10) + ' (' + f.ColList + ', ErrorCode, ErrorColumn, ErrorDescription)' + CHAR(10) +
' SELECT ' + f.ColList + ', 0, 0, ''Orphan ' + @SourceTable + '''' + CHAR(10) + ' FROM ' + @SourceSchema + '.' + @SourceTable + CHAR(10) +
' WHERE IS NULL;' + CHAR(10) + CHAR(13) + ' --Remove the orphaned rows from Staging table' + CHAR(10) + ' DELETE FROM ' + @SourceSchema + '.' + @SourceTable + CHAR(10) +
' WHERE IS NULL;' + CHAR(10) + CHAR(13) +
' WITH SourceCTE' + CHAR(10) + ' AS' + CHAR(10) + ' (SELECT ' + c.ColList +
CHAR(10) + ' FROM ' + @SourceSchema + '.' + @SourceTable + ')' + CHAR(10) +
' MERGE INTO ' + @TargetDatabase + '.' + @TargetSchema + '.' + @TargetTable + ' WITH (HOLDLOCK) AS tgt' + CHAR(10) +
' USING SourceCTE AS src ON ' + d.ColList + CHAR(10) + ' WHEN MATCHED AND src.RowHash <> tgt.RowHash THEN' + CHAR(10) +
' UPDATE' + CHAR(10) + ' SET ' + REPLACE(u.ColList,',' ,',' + CHAR(10) + ' ') + CHAR(10) +
CASE WHEN de.DeleteExists = 1 THEN ' WHEN MATCHED AND src.Deleted = 1 THEN DELETE' + CHAR(10) ELSE '' END +
' WHEN NOT MATCHED THEN' + CHAR(10) + ' INSERT (' + f.ColList + ')' + CHAR(10) +
' VALUES(' + g.ColList + ');' + CHAR(10) +
'END;', b.TempTableColumnDef
FROM sys.tables a CROSS JOIN SourceSelectColumnListCTE c
CROSS JOIN KeyColumnCTE d
CROSS JOIN InsertColumnListCTE f
CROSS JOIN InsertSourceColumnListCTE g
CROSS JOIN VarcharMaxColsCTE h
CROSS JOIN UpdateSetClauseCTE u
CROSS JOIN DeletedExistsCTE de
CROSS JOIN TempColumnDefCTE b
WHERE a.schema_id = SCHEMA_ID(@SourceSchema) AND a.name = @SourceTableFor better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
1. No, there is nothing wrong with the query plan. The query plan is the same regardless of where I put that line in the SELECT statement. It is a byte for byte match on the query plan and nothing looks wrong with it.
2. Changing it to VARCHAR(MAX) does not fix the problem. It still truncates if I move that line of code down in the SELECT statement. Additionally, none of these strings even come close to 8000 characters AND the MERGE statement in here is generated twice inside this code with the FIRST occurrence being truncated while the SECOND occurrence is complete.
3. No, completely removing that CTE and the single line that references it in the SELECT list does not fix the problem, it simply truncates even worse.
4. No, replacing the reference to the column in the SELECT list with a hard coded empty string does not fix the problem.
5. No, it does not matter if that piece concatenates and empty string, a single character, or a full 40,000 characters of data, it still produces correct output when it is at that location and truncates if moved down.The truncation does not happen AT the point where that line is in the SELECT statement. It happens in the middle of an output BEFORE that piece of the string is concatenated. The further down in the SELECT clause it is moved, the further prior to it is where the string is truncated. I'm not changing the FROM clause, WHERE clause, or anything in any of the CTEs, I'm simply changing the location that a string is concatenated in the SELECT clause.
Michael Hotek
-
Yes, I ran this is SSMS, PowerShell, SQLCMD via batch file, SQL CMD within SSMS, EXEC(@var), sp_executeSQL, and an execute SQL task from within SSIS. Behavior matched for all execution methods.
Michael Hotek
-
did my change not work for you?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
No. VARCHAR(MAX) or VARCHAR(8000), it still truncates based on where in the SELECT clause I place it. The further down it goes, the more severe the truncation.
Michael Hotek
-
The trick is that for a given concatenation of two strings using the + operator, the result will be truncated at 8000 bytes unless one of the operands has a MAX length (see the last paragraph in the "Remarks" section at https://docs.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-transact-sql#remarks)
The REPLACE you're highlighting is the only operand in all the concatenations you do that will have a MAX length (because the string_expression given as its first parameter is a MAX type, because you explicitly CAST it as such).
So, basically, the output of all your concatenations is capped at 8000 bytes until you introduce the REPLACE.
The later in the list of concatenations you introduce it, the more truncation you allow to occur.
Just make sure you introduce a MAX type early in the list of concatenations and you'll be fine (most easily just CONVERT/CAST the first literal to a MAX type).
Some code to illustrate (using a less efficient tally CTE just to keep things shorter):
--Introducing the MAX type at the end saves us nothing, and we only get the first 8000 characters, the 'a' string
WITH
tally AS (SELECT TOP (32000) N=ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
string AS (SELECT string=REPLICATE('a',8000)+REPLICATE('b',8000)+REPLICATE('c',8000)+REPLICATE('d',8000)+CONVERT(VARCHAR(MAX),''))SELECT DISTINCT string_length=LEN(string), characters=SUBSTRING(string,N,1)
FROM tally, string
WHERE N<=LEN(string);--If I move the MAX type empty string up one spot, then the we'll get the 8000 'd' characters in addition to the 8000 'a' characters
--because we didn't introduce a MAX type until after the 'b' and 'c' concatenationsWITH
tally AS (SELECT TOP (32000) N=ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
string AS (SELECT string=REPLICATE('a',8000)+REPLICATE('b',8000)+REPLICATE('c',8000)+CONVERT(VARCHAR(MAX),'')+REPLICATE('d',8000))SELECT DISTINCT string_length=LEN(string), characters=SUBSTRING(string,N,1)
FROM tally, string
WHERE N<=LEN(string);--Moving up one more spot, we'll get 'a','c', and 'd' characters
WITH
tally AS (SELECT TOP (32000) N=ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
string AS (SELECT string=REPLICATE('a',8000)+REPLICATE('b',8000)+CONVERT(VARCHAR(MAX),'')+REPLICATE('c',8000)+REPLICATE('d',8000))SELECT DISTINCT string_length=LEN(string), characters=SUBSTRING(string,N,1)
FROM tally, string
WHERE N<=LEN(string);--And finally, if I introduce it before any concatenation result would go over 8000 bytes, I get no truncation at all
WITH
tally AS (SELECT TOP (32000) N=ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
string AS (SELECT string=REPLICATE('a',8000)+CONVERT(VARCHAR(MAX),'')+REPLICATE('b',8000)+REPLICATE('c',8000)+REPLICATE('d',8000))SELECT DISTINCT string_length=LEN(string), characters=SUBSTRING(string,N,1)
FROM tally, string
WHERE N<=LEN(string); -
I noticed that I had issues with my dynamic SQL when I used fixed length character string variables or the sysname data type. Try changing sysname to nvarchar(128).
- Jacob Wilkins - Tuesday, January 30, 2018 4:28 PM
It could be best to just cast every damn thing explicitly to MAX. No reliance on implicit conversions, order of precedence, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
This simplified example may explain the issue:
DECLARE @a VARCHAR(8000) = REPLICATE('A', 8000);
DECLARE @b-2 VARCHAR(8000) = REPLICATE('B', 8000);
DECLARE @C VARCHAR(MAX) = @a + @b-2;
SELECT LEN(@C);DECLARE @D VARCHAR(MAX) = REPLICATE('D', 8000);
DECLARE @E VARCHAR(MAX) = REPLICATE('E', 8000);
DECLARE @F VARCHAR(MAX) = @D + @E;SELECT LEN(@F);
-
Yes, but it does not fix the problem. Everything is explicitly cast to a VARCHAR(MAX) and it STILL truncates based on where I place that single line of code. The ENTIRE block of code that I'm outputting is LESS than 8000 characters in total. It's not truncating at 8000 characters. It's truncating at between 3752 characters and 4073 characters based on the position in the SELECT clause. I've even gone to the length of explicitly casting every column in the CTEs to a VARCHAR(MAX) and then further wrapping every single column OR static string in a CAST to VARCHAR(MAX). I'm literally doing the equivalent of beating SQL Server over the head with the VARCHAR(MAX). It now appears a total of 326 times in my query and it STILL does not matter, I get the resultant string truncated.
I've spent almost 2 days trying to get it to behave properly so I can actually remove that CTE from the query, because I no longer need it. I won't be spending any more time on it. I'll just leave the unused CTE in the query and just leave the stupid replacement of the column value to an empty string inside the concatenation at a point where nothing truncates. The end result is that I get a complete block of code and that piece is wiped out to an empty string. It just looks ugly and will stay that way.
Michael Hotek
-
On every server on which I've run your code, I can reproduce the behavior, but only when the resulting string would be more than 8000 bytes (note that since you will be converting to NVARCHAR because of the SYSNAMEs, that is 4000 characters, not 8000), and only when a MAX type is not included prior to a concatenation that would exceed 8000 bytes.
Under no conditions have I been able to reproduce the behavior you cite if a MAX type is included early in the concatenation, which is as expected.
Is the code you've posted here exactly the code you're running, or are you changing something when running on your systems? I ask because the resulting string (in the non-truncated cases) has been nearly 20000 characters on ever server on which I've run it, so I suspect something is different between the code you're running and what we've seen so far.
Also, how are you checking that the resulting string is correct or truncated?
Cheers!
-
Okay, I copied your code from the original post and ran it on my system. Yes, it is truncated but the length of the code generated is 19,020 characters which exceeds the maximum number of characters that can be displayed as text.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply