SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Massive Data Input being truncated by N/VChar(MAX)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PKD
Starting Member

USA
2 Posts

Posted - 11/08/2012 :  13:36:45  Show Profile  Reply with Quote
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
Yak Posting Veteran

53 Posts

Posted - 11/08/2012 :  13:45:55  Show Profile  Reply with Quote
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];
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/08/2012 :  13:49:52  Show Profile  Reply with Quote
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).
Go to Top of Page

PKD
Starting Member

USA
2 Posts

Posted - 11/08/2012 :  13:55:02  Show Profile  Reply with Quote
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!
Go to Top of Page

mandm
Yak Posting Veteran

53 Posts

Posted - 11/08/2012 :  16:40:03  Show Profile  Reply with Quote
You're welcome.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000