SQL statement max size

[cash0]

Hi, I'm having problems with the length of SQL statements.

I have a string of xml that can be very long - the problem is that the xml string is getting cut off at the 64000th character when I create the SQL statement.

The following is ColdFusion code. For those not familiar with ColdFusion - the wddx method changes an array into xml (this is not the problem, I've checked), and the cfqueryparam just strips out apostrophies and whatever (but I also doubt that that is the problem).

-----------

<cfwddx action=&quot;cfml2wddx&quot; input='#this.arevisions#' output=&quot;xmlrevisions&quot;>

<cfquery name=&quot;qSaveWorkflow&quot; datasource=&quot;#request.stdsn.content#&quot;>
update tWorkflow
set xmlrevisions = <cfqueryparam value=&quot;#xmlrevisions#&quot; cfsqltype=&quot;cf_sql_longvarchar&quot; null=&quot;NO&quot;>
where Workflowuuid = '#this.workflowuuid#'
</cfquery>

------------

Is there some kind of size limit on SQL statements?

 

[billchris]

Hi cash0,

SQL Server can only update fields of type text, ntext and image with approximately 120kb at a time. I'm guessing that you are using an ntext field, which holds unicode strings and each character is two bytes, so you are limited to approximately 64k characters.

What you need to do is create a loop that uses the T-SQL UPDATETEXT statement to update 64k characters at a time. (You can lookup UPDATETEXT and &quot;Modifying ntext, text, or image Values&quot; in the BOL.)

The steps that you want to take are:
[ol][li]Turn on transaction logging for UPDATETEXT on your database.

UPDATETEXT is, by default, a non-logged operation. That prevents the log from filling up with huge amounts of data. So, if your database recovery model is simple or bulk-logged, the 'select into/bulkcopy' option defaults to 'false' and you won't be able to rollback a failed transaction.

To turn on transaction logging, call,

sp_dboption '<YourDatabaseName>', 'select into/bulkcopy', 'true'

You can see what the current setting for your database is by running,

sp_dboption '<YourDatabaseName>', 'select into/bulkcopy'

If it is already on, then you don't need to do this step. Note: You must have at least db_owner permissions to change db options.

If it is not critical that this operation succeed, or if you can easily recover from a failure, don't do this step.[/li]

[li]Begin a transaction.

Don't do this step if 'select into/bulkcopy' is off. It won't do you any good. If you want transactions, you will have to write the Cold Fusion code. I don't have a clue how to do that.[/li]

[li]Set the xmlrevisions column to NULL because UPDATETEXT won't work if the ntext field has never been initialized. Plus, it allows you to do the update by just appending the substrings onto the field. (Much simpler code.)[/li]

[li]Get the first 64,000 characters from the xml string.[/li]

[li]Call the UPDATETEXT statement to append the substring to xmlrevisions. If you call UPDATETEXT with the insert_offset and delete_length parameters equal to zero(0), it will append the data to the field.

<cfquery name=&quot;qSaveWorkflow&quot; datasource=&quot;#request.stdsn.content#&quot;>
  -- Get the pointer to the ntext field
  DECLARE @ptrval varbinary(16)
  SELECT 
    @ptrval = TEXTPTR(xmlrevisions) 
  FROM 
    tWorkflow
  WHERE
    Workflowuuid = '#this.workflowuuid#'

  -- Append the substring to the end of the field.
  UPDATETEXT 
    tWorkflow.xmlrevisions @ptrval 0 0 
    <cfqueryparam value=&quot;#xmlSubString#&quot; cfsqltype=&quot;cf_sql_longvarchar&quot; null=&quot;NO&quot;>
</cfquery>

[/li]
[li]Get the next 64,000 characters.[/li]

[li]Repeat from step 5 until there are no more characters.[/li]

[li]Commit the transaction, if there is one.[/li]

[li]If you did step 1, then reset the 'select into/bulkcopy' db option to off.

sp_dboption '<YourDatabaseName>', 'select into/bulkcopy', 'false'

[/li][/ol]
That's it. I hope this helps solve your problem.

--Bill

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[cash0]

OK. I've finally fixed it I think.

Bill, I implemented what you described above, but found that I still had a problem. After many hours of trial and error I realised that it wasn't reading more than 64k either. I don't know if it is a ColdFusion limitation or a SQL server limitation.

Anyway, as well as spliting my data up into 64k chunks and appending it to the database, I had to use the READTEXT feature to read 64k chunks and append it to a string.

I didn't end up doing any transactions and didn't bother with the bulkcopy options (not yet anyway). Also I found that using NULL values in the updatetext statement appended that data the way I needed it to.

My code looked something like this in the end:

-----------------

<cfquery name=&quot;qUpdateXMLRevisions&quot; datasource=&quot;#request.stdsn.content#&quot;>
<cfloop from=&quot;1&quot; to=&quot;#ArrayLen(aRevisionTokens)#&quot; index=&quot;i&quot;>
-- Get the pointer to the field
DECLARE @ptrval#i# varbinary(16)
SELECT @ptrval#i# = TEXTPTR(XMLRevisions)
FROM tWorkflow
WHERE workflowUUID = '#this.workflowuuid#'

-- Append the substring to the end of the field.
UPDATETEXT tWorkflow.XMLRevisions @ptrval#i# NULL NULL <cfqueryparam value=&quot;#aRevisionTokens#&quot; cfsqltype=&quot;cf_sql_longvarchar&quot; null=&quot;NO&quot;>
</cfloop>
</cfquery>

------------------

Thanks for all your help.

 

[billchris]

cash0,

Thanks for the reply. I should have mentioned that I had never tested that code, I just derived it from Microsoft's documentation. I apologize for that.

<Sarcasm>
  I can't believe their documentation was wrong!!!
</Sarcasm>

I'm glad it was some help, though.

--Bill


“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal