SQL Server Rejected a Value
SQL Server rejected a value written into <schema>.<table>: <reason> (column <name>). The write was stopped and none of its rows were saved; no row was skipped.
Speediful writes data into SQL Server tables in batches: query and replicate results, load results written back to the input table, CSV files imported into tables, validation results, and the tables the describe and metadata procedures create. If SQL Server cannot store one value in its destination column, the whole write is stopped and none of its rows are saved. The run then fails with this message, and the stored procedure raises error 88300.
The reason in the message is one of these:
| Reason | What it means |
|---|---|
| the value does not match the column's data type | For example, text in an int or datetime column |
| the value is too long for the column | A text value longer than the column's length |
| the value is too large for the column's data type | A number outside the column's range |
| the value is not valid for the column | SQL Server could not read the value from the file |
The Results pane also gives the row number SQL Server reported: the row's position within the batch being written.
SLAM_Alpha_import_csvs reports a rejected value differently: the file's failure line
names the data row and the CSV column. See
Value or Row Rejected.
Speediful never skips a row SQL Server rejects. A run that finished with rows quietly missing would look like a successful run, so it fails instead.
Common causes
- Appending to a table whose columns were changed. With
@exists_action = 'append', Speediful writes into the existing table as it is. If a column was created with a narrower or different type than the incoming data needs (for example, anintcolumn that now receives text), the value cannot be stored. - A CSV file whose values do not match the table it is imported into. For example, a date written in a format SQL Server does not read, or a number column that contains text in some rows.
What to do
- Find the column named in the message and check its data type and length in the destination table.
- Either change the column so it can hold the incoming data, or write to a new table
(or use
@exists_action = 'drop') so Speediful creates columns that fit. - For a CSV import, check the reported row of the file for the value in that column.
- Re-run the procedure.
A query or replicate run writes its results in several writes, and the writes that
finished before the failing one are kept. A CSV import writes each file in one write, so
none of the failing file's rows are kept. If you re-run
into the same table with @exists_action = 'append', the kept rows are written again.
Use @exists_action = 'drop', or delete the rows the failed run added, before you
re-run.