Re: incoming tabular data stream error?
I'll make a recommendation here that may or may not apply, but may be worth noting. This is assuming ms sql Server. Insert...values... Insert...values... , n Will be much slower from SQL perspective than: Insert... Values(...) , (...) , (...) , n Or Begin tran Insert... Values... Insert... Values... , n Commit tran First option is preferred. I only mention this as cfthread will just speed up the sql statement generation (ie , looping over the spreadsheet) and not the actual sql execution time (or at least it sounds like in this instance) . You may be able to include some of these sql performance tweaks to improve performance further. Byron Mann Lead Engineer Architect HostMySite.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359687 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: incoming tabular data stream error?
Running a page that reads a text file into an array - then writes the array to the database. I get the following error:* Detail:* [Macromedia][SQLServer JDBC Driver][SQLServer]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100. Looking that up, Ben Nadle says it's from a valuelist being too large: /id IN ( //cfqueryparamvalue=//#ValueList( qSelected.id )#cfsqltype=//cf_sql_integerlist=//true///) /But, I don't have one of those on the page!/ /Dumping the array in question, it looks OK. There's only 1001 rows. /Hmmm. /Trying a smaller list - 604 records - same error./ /Smaller yet - 200 records - no error. cfquery name=insertLIST cfloop from=1 index=i to=#arrayLen(mailREC)# INSERT INTO nl_mailgroups ( ml_email, ml_firstname, ml_lastname, other ) VALUES ( cfqueryparam value=#mailREC[i][1]# cfsqltype=CF_SQL_VARCHAR /, cfqueryparam value=#mailREC[i][2]# cfsqltype=CF_SQL_VARCHAR null=#not len(mailREC[i][2])# /, cfqueryparam value=#mailREC[i][3]# cfsqltype=CF_SQL_VARCHAR null=#not len(mailREC[i][3])# /, cfqueryparam value=#mailREC[i][4]# cfsqltype=CF_SQL_VARCHAR null=#not len(mailREC[i][4])# / ); INSERT INTO nl_catREL ( groups_id, ml_id ) VALUES ( cfqueryparam value=#req.thisGROUPID# cfsqltype=CF_SQL_INTEGER /, scope_identity() ); /cfloop /cfquery/ Yeah, pretty clearly, there's a limit to the number of parameters you can insert within a single SQL batch. Your query has six parameters. Multiplying 6 by 600 gives you 3600, which is significantly higher than 2100. You can either remove your CFQUERYPARAMs (which might be ok if your data isn't coming from an untrusted source), or you can limit your batch size so you don't exceed 2100 parameters per CFQUERY tag. You could either loop over the CFQUERY tag itself (which might well cause other performance issues) or you could make sure your loop doesn't exceed 350 records (2100 divided by 6) by having an outer loop and an inner loop. Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Service-Disabled Veteran-Owned Small Business (SDVOSB) on GSA Schedule, and provides the highest caliber vendor- authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359663 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: incoming tabular data stream error?
You can either remove your CFQUERYPARAMs (which might be ok if your data isn't coming from an untrusted source), or you can limit your batch size so you don't exceed 2100 parameters per CFQUERY tag. Might try this first. It's from inside a client admin system, and there's a specifically formatted xlsx sheet they use for this. Plus, all the vars are cleaned before the insert as well. You could either loop over the CFQUERY tag itself (which might well cause other performance issues) It definitely does. One test spreadsheet I've got is over 15,000 records, and I might as well go get lunch. Usually the server resets the connection before it completes... or you could make sure your loop doesn't exceed 350 records (2100 divided by 6) by having an outer loop and an inner loop. Could this be a job for cfthread? Split a large list up into 4 or 5 threads and just let them run (as soon as I get my head around the math... heh)? Seems that would speed things up a good bit, but I've never used cfthread before. Might be fun... ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359665 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: incoming tabular data stream error?
or you could make sure your loop doesn't exceed 350 records (2100 divided by 6) by having an outer loop and an inner loop. Could this be a job for cfthread? Split a large list up into 4 or 5 threads and just let them run (as soon as I get my head around the math... heh)? Seems that would speed things up a good bit, but I've never used cfthread before. Might be fun... Yes, you could use CFTHREAD here pretty easily, and the records will probably get into the database more quickly, but at the cost of other things that might be happening on the server at the same time. Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Service-Disabled Veteran-Owned Small Business (SDVOSB) on GSA Schedule, and provides the highest caliber vendor- authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359673 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: incoming tabular data stream error?
You can either remove your CFQUERYPARAMs (which might be ok if your data isn't coming from an untrusted source) This works. Tried it with a list of roughly 1000 records. Will try my 15,000 record sheet later this evening and see what happens. Added 4 threads to split all list up as well. Speeds things up a bit. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359675 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
