Massive Data Input being truncated by N/VChar(MAX)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Massive Data Input being truncated by N/VChar(MAX)

Author

Topic

PKD
Starting Member

2 Posts

Posted - 2012-11-08 : 13:36:45

I've got a large data import coming in from a customer. The data comes in in a variant amount, and I'm writing some SQL to tear it apart, drop it into a staging table, and then use that table to update the correct data tables. I've written a UDF to take the initial data and split it on a specified delimiter.

Data example:
DECLARE @BigStringBlob NVARCHAR(MAX);
SET @BigStringBlob = CAST('(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)' AS NVARCHAR(MAX));

Code:

--Clear Parsed Chunks
DELETE FROM [tmpParsedChunks];

--Fill perma-temp table with initially parsed chunks
--INSERT INTO [tmpParsedChunks]
SELECT [UID],
REPLACE(REPLACE([Values], 'True', '1'), 'False', '0')
FROM [dbo].[udfCharacterListToTable](@BigStringBlob, '^') AS UCLTT
ORDER BY UCLTT.[UID];
SELECT * FROM [tmpParsedChunks];

The above code results in [tmpParsedChunks] having 20 full rows of data, and the 21st row cutting off halfway.

UID (No column name)
1 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
2 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
3 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
4 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
5 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
6 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
7 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
8 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
9 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
10 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
11 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
12 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
13 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
14 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
15 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
16 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
17 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
18 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
19 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
20 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
21 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

row 21 is where 'death' happens.

Problem is, I'm going to have far more than 20 rows of usable data being processed, so I need to resolve this issue and allow more text!

mandm
Posting Yak Master

120 Posts

Posted - 2012-11-08 : 13:45:55

What data type(s) is your UDF working with?

SELECT [UID],
REPLACE(REPLACE([Values], 'True', '1'), 'False', '0')
FROM [dbo].[udfCharacterListToTable](@BigStringBlob, '^') AS UCLTT
ORDER BY UCLTT.[UID];
SELECT * FROM [tmpParsedChunks];

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 13:49:52

Does it die on the 21st row because that is where it is exceeding 8000 characters in the input string? I would look in the udfCharacterListToTable to see if it is really able to handle nvarchar(max).

PKD
Starting Member

2 Posts

Posted - 2012-11-08 : 13:55:02

quote:
Originally posted by mandm

What data type(s) is your UDF working with?

SELECT [UID],
REPLACE(REPLACE([Values], 'True', '1'), 'False', '0')
FROM [dbo].[udfCharacterListToTable](@BigStringBlob, '^') AS UCLTT
ORDER BY UCLTT.[UID];
SELECT * FROM [tmpParsedChunks];

THAT was the exact clue-in I needed!

My UDF was using NVARCHAR(1000) strings for each 'row' of data. I changed it to MAX, and presto, all's well!

Thank you!

mandm
Posting Yak Master

120 Posts

Posted - 2012-11-08 : 16:40:03

You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources