tried to bind parameter number 2101. sql server supports a maximum of 2100 parameters

Question

I am trying to run some migrations through phinx to MSSQL, when I execute my query turns out getting the following message "tried to bind parameter number 2101. sql server supports a maximum of 2100 parameters"

I have been looking for a solution for like 3 days, does anyone know, what can be the problem and how can I fix it? could be a problem related to my php migration file? or something about my phinx configuration?

This is part of my migration file:

$rows = 
    [

        [
            'COLUMNA'    => 111,
            'COLUMNB' => 101,
            'COLUMNC' => '-1',
            'COLUMND' => '',
            'COLUMNE' => 'ERROR',
            'COLUMNF' => NULL,
            'COLUMNG' => 1,
            'COLUMNH' =>0,
            'COLUMNI' => 10002,
            'COLUMNJ' => '2017-11-12 00:00:00.000',
            'COLUMNK' => -1,
            'COLUMNM' => -1
        ],
        [   'COLUMNA'    => 112,
            'COLUMNB' => 101,
            'COLUMNC' => '-1',
            'COLUMND' => '',
            'COLUMNE' => 'ERROR',
            'COLUMNF' => NULL,
            'COLUMNG' => 1,
            'COLUMNH' =>0,
            'COLUMNI' => 10002,
            'COLUMNJ' => '2017-11-12 00:00:00.000',
            'COLUMNK' => -1,
            'COLUMNM' => -1
        ],
   ]

$X = $this->table('MYTABLE');
    $X->insert($rows);
    $X->saveData();

Thanks a lot

score 2 · Accepted Answer · answered Oct 02 '20 at 16:07

You can chunk your data with php easily. If you want you can also determine a good chunksize by checking your data size.

$chunk_size = floor(2100 / count($rows[0]));
foreach (array_chunk($rows, $chunk_size) as $data_chunk ) {
  $X->insert($data_chunk)->saveData();
}

score 0 · Answer 2 · answered Nov 20 '17 at 17:23

Obviously you are trying to bind too many parameters, solution is to do the migrations in batches of lets say 1000 rows ~ 12000 parames according to your data.

$X = $this->table('MYTABLE');
$data = [];
for ($i = 0; $i < count($rows); i++) 
{
    $data[] = $rows[i];
    if(i % 1000 === 0) 
    {
        $X->insert($data);
        $X->saveData();
        $data = [];
    }
}

tried to bind parameter number 2101. sql server supports a maximum of 2100 parameters

2 Answers2