+ Reply to Thread
Results 1 to 5 of 5

What is the max length of SqlCommand.CommandText

  1. What is the max length of SqlCommand.CommandText

    What is the max length of a T-SQL statement I can execute using the
    CommandText property on SqlCommand?

    I am importing a lot of records (and other processing) and I want to do
    something like:

    Insert Into tblSomeTable (Col1, Col2,....Col15)
    Select (Val1, Val2,.....Val15) Union All Select (Val1, Val2,....VAl15)

    Sometimes I've got 10s of thousands of records to insert and I want to do
    them in "Batches"....
    Like 100 at a time, so there would be 100 "Union All"s...Because then I have
    to do a bunch of other processing....any ways..I want to make sure that I
    don't make the SQL statment too big. Does anyone know the max length of the
    T-SQL I can pass in?

    That way I can do something like:

    ------------------------------------------------------------------------------------------
    private void addRecods(List records){

    string sql = generateSQL(records);

    if(sql.length > MAX_TSQL_LENGTH) {
    int mid = records.Count / 2;
    addRecords(records.GetRange(0, mid));
    addRecords(records.GetRange(mid, records.Count - mid));
    return;
    }

    executeSQL(sql);

    }
    ------------------------------------------------------------------------------------------

    Hopefully you get the point...


  2. Re: What is the max length of SqlCommand.CommandText

    The CommandText property is defined as System.String, which has length of
    INT data type, so the max should be around the max INT value of
    2,147,483,647. But also this can be limited by amount of memory available on
    the system.

    Maybe you should look at alternative approach to load the data. Generating a
    delimited data file and then using BCP, BULK INSERT or Integration Services
    to load the file will be very efficient.

    HTH,

    Plamen Ratchev
    http://www.SQLStudio.com


  3. Re: What is the max length of SqlCommand.CommandText

    Now I do understand the there is a max length of the actual .NET string, but
    what about SQL. Can I load a T-SQL Command that is a length of
    2,147,483,647 and have it execute? Is there some kind of limit on SQL's
    side that might break the attempt?


  4. Re: What is the max length of SqlCommand.CommandText

    I am not aware of any limit on the SQL Server side.

    HTH,

    Plamen Ratchev
    http://www.SQLStudio.com

  5. Re: What is the max length of SqlCommand.CommandText

    EDBrian (preport@newsgroups.nospam) writes:
    > Now I do understand the there is a max length of the actual .NET string,
    > but what about SQL. Can I load a T-SQL Command that is a length of
    > 2,147,483,647 and have it execute? Is there some kind of limit on SQL's
    > side that might break the attempt?


    The maximum size for a query batch is 65536 * network packet size. The
    default packet is 4096 bytes, which gives an upper limit of 268 million
    bytes.

    Since you were using UNION ALL, you should keep your batch sizes moderate.
    Do not have more than 100-500 SELECTs in the same UNION ALL. If you have
    too many, the compilation time wil go through the roof, and it will
    run slower than if you had had single INSERT statements.

    The fastest for INSERT is this somewhat obsure syntax:

    INSERT tbl( col1, col2, col3, ...)
    EXEC('SELECT val1, val2, val3, ...
    SELECT val11, val12, val13, ...
    ...')

    Even faster is to use XML.




    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

+ Reply to Thread