Post reply

Maximum query string permitted in SQL (7.0 or 2K)

  • August 23, 2002 at 8:47 am

    #158638

    Hi all,

    To optimize our SQL updates, we club a lot of update/insert statements into one string

    and pass it to (ADO) Connection.Execute(). What we observed is, some of the updates are not been executed by SQL Server. We ran the profiler and found all the update statements

    present in the trace. Eg. If 4000 update/inserts are sent in one go, sometimes about 456 statements get updated, rest are ignored, another server executed sucessfully 457, on some other server 472 got updated. We calculated the string length which is getting updated, we found it to be around

    20K. Now we doubled the string size (update statements size) and still found the same

    behaviour. i.e. although the 456 strings were now coming to around 40k, they were all

    getting updated, but the rest were still ignored! Above all SQL doesn't even throw an error for this!! We are not able to conclude whether the limitation is on the Buffer Size or is it on the number of SQL Strings. Is there anyway of calculating or setting this limit.

    Expecting a quick reply.

    Regards,

    Levi.


    Jesus My Saviour

  • August 23, 2002 at 10:02 am

    #435213

    don't know the answer, but I wouldn't be surprised if there is a buffer limit. Some funny size limits seem to occur in passing data or returning it at times.

    I'd limit the number of updates to prevent issues rather than wasting time figuring out how to send 4000 updates.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • August 23, 2002 at 10:35 am

    #435219

    Have you tried changing the network packet size configuration option with sp_configure?

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

    http://www.sql.nu

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • August 24, 2002 at 7:22 pm

    #435307

    Not impossible that the buffer has a limit. I have not read anything to this effect either but the best suggestion I can offer is contact MS as a bug report and see what they say or if SQL 2000 you could join the SP3 beta and edge this question in to get the answer from the developers.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply