SQL Error at 160,000 characters

[memcom]

I have a script that puts the contents of an xml file into a new text column in our database. After that it breaks the contents of the data in that column into 4,000 character segments and gets the data ready for using with openxml. This has worked great until today when we got a file that was about 395,000 characters. I am getting an error everytime that I try to process this file. I narrowed the problem down to a limit at 164,000 characters. Can someone help me to get around the problem. Below is the code that I am using and the error message at the bottom


Script:
use databasename

insert into tmc_xmldocprepare values('saved for tmc import of hsn orders','N', 'HSN')
declare @import_xmldocprepare_uid int
select @import_xmldocprepare_uid = max(uid) from tmc_xmldocprepare where doc like '%saved for tmc import of hsn orders'
declare @import_xml_command as varchar(300)
set @import_xml_command =
'd:\micros~1\mssql\binn\textcopy.exe /S servername /U username /P password /D databasename /T tmc_xmldocprepare /C doc /F source_xml_file.xml /W "where uid = '+cast(@import_xmldocprepare_uid as varchar(6))+'" /I'
exec master.dbo.xp_cmdshell @import_xml_command


/*This section creates a stored procedure that will allow for sp_xmldocprepare to work*/
USE master
GO
IF OBJECT_ID('sp_xml_concat','P') IS NOT NULL
DROP PROC sp_xml_concat
GO
CREATE PROC sp_xml_concat
@hdl int OUT,
@table varchar(8000),
@column sysname
AS
EXEC('
SET TEXTSIZE 4000
DECLARE
@cnt int,
@c nvarchar(4000)
DECLARE
@declare varchar(8000),
@assign varchar(8000),
@concat varchar(8000)

SELECT @c = CONVERT(nvarchar(4000),'+@column+') FROM '+@table+'

SELECT @declare = ''DECLARE'',
@concat = '''''''''''''''',
@assign = '''',
@cnt = 0

WHILE (LEN(@c) > 0) BEGIN
SELECT @declare = @declare + '' @c''+CAST(@cnt as nvarchar(15))+'' nvarchar(4000),'',
@assign = @assign + ''SELECT @c''+CONVERT(nvarchar(15),@cnt)+''= SUBSTRING('+@column+',''+CONVERT(nvarchar(15),1+@cnt*4000)+'',4000) FROM '+@table+' '',
@concat = @concat + ''+@c''+CONVERT(nvarchar(15),@cnt)
SET @cnt = @cnt+1
SELECT @c = CONVERT(nvarchar(4000),SUBSTRING('+@column+',1+@cnt*4000,4000)) FROM '+@table+'
END

IF (@cnt = 0) SET @declare = ''''
ELSE SET @declare = SUBSTRING(@declare,1,LEN(@declare)-1)

SET @concat = @concat + ''+''''''''''''''

EXEC(@declare+'' ''+@assign+'' ''+
''EXEC(
''''DECLARE @hdl_doc int
EXEC sp_xml_preparedocument @hdl_doc OUT, ''+@concat+''
DECLARE hdlcursor CURSOR GLOBAL FOR SELECT @hdl_doc AS DocHandle'''')''
)
')
OPEN hdlcursor
FETCH hdlcursor INTO @hdl
DEALLOCATE hdlcursor
GO

/*This section inserts the usable rows from TMC_XMLDOCPREPARE*/
use databasename
execute
('declare @xmldocprepare_uid as int
declare xmldocprepare cursor for
select uid from tmc_xmldocprepare where processed = ''N''

open xmldocprepare

fetch next from xmldocprepare
into @xmldocprepare_uid

while @@fetch_status = 0
begin

declare @run_850_query as varchar(100)
set @run_850_query = ''(SELECT doc FROM tmc_xmldocprepare WHERE uid=''+cast(@xmldocprepare_uid as varchar(10))+'') a''

DECLARE @hdl int
EXEC sp_xml_concat @hdl OUT, @run_850_query, ''doc''


select *, getdate(), ''N'', ''N'', @xmldocprepare_uid, NULL, ''N'', NULL from openxml (@hdl, ''/HSN_DROPSHIP'',1)
with(FUNCTIONAL_ID VARCHAR(2),HSN_SENDER_CODE VARCHAR(15),VENDOR_SENDER_CODE VARCHAR(15),DATE VARCHAR(8),TIME VARCHAR(4),VENDOR_ID VARCHAR(6),GS_CNTRL_NUM VARCHAR(10))

cross join
openxml (@hdl, ''/HSN_DROPSHIP/TRAILER'',2)
with(ORDER_COUNT VARCHAR(7),GS_CNTRL_NUM VARCHAR(10))

cross join
openxml(@hdl, ''/HSN_DROPSHIP/ORDERS/ORDER'',2)
with(SET_NUM VARCHAR(6),ORDER_NUM VARCHAR(10),VENDOR_NUM VARCHAR(17),ORDER_DATE VARCHAR(8),SENDER_NAME VARCHAR(35),CUSTOMER_NUM VARCHAR(16),CREDIT_CARD_NUMBER VARCHAR(21),CREDIT_CARD_EXP_DATE VARCHAR(15),
EMAIL_ADDRESS VARCHAR(55),QUANTITY VARCHAR(6),HSN_COST VARCHAR(10),VENDOR_UPC VARCHAR(17),HSN_ITEM_NUM VARCHAR(20),SALE_AMOUNT VARCHAR(10),ITEM_DESCR VARCHAR(45),TAX_AMOUNT VARCHAR(10),
CREDIT_AMOUNT VARCHAR(10),SHIPPING_CHARGE VARCHAR(10),HANDLING_CHARGE VARCHAR(10),TOTAL_AMOUNT VARCHAR(10),PAY_METHOD VARCHAR(7),SHIP_MODE VARCHAR(7),SHIP_MODE_DESCR VARCHAR(15),SHIP_NAME VARCHAR(35),
SHIP_ADDRESS_LINE_1 VARCHAR(30),SHIP_ADDRESS_LINE_2 VARCHAR(30),SHIP_CITY VARCHAR(25),SHIP_STATE VARCHAR(7),SHIP_ZIP_CODE VARCHAR(14),PHONE_NUM VARCHAR(15))

EXEC sp_xml_removedocument @hdl


fetch next from xmldocprepare
into @xmldocprepare_uid

end
')
close xmldocprepare
deallocate xmldocprepare





Results:
(1 row(s) affected)


(4 row(s) affected)

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 43
A cursor with the name 'hdlcursor' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 44
A cursor with the name 'hdlcursor' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure sp_xml_concat, Line 45
A cursor with the name 'hdlcursor' does not exist.
Server: Msg 8179, Level 16, State 5, Line 20
Could not find prepared statement with handle 0.
Server: Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 34
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

 

[vongrunt]

Single quote in XML data... probably. Gotta love EXEC statement :|

 

[memcom]

I have already checked for that and there are no single quotes or any other character that is not standard text.