Query Results Too Wide (error 88322)
The results of this query cannot be stored in one SQL Server table
A SQL Server table can have at most 1,024 columns, and one row can hold at most
8,060 bytes. A query on a wide Salesforce object, such as SELECT * on an object with
hundreds of custom fields, can select more than that. If the destination table would be
too wide, SQL Server cannot create it, or cannot store one of its rows, and the query
stops with this error. SLAM_query, SLAM_query_bulk, SLAM_query_bulkv2,
SLAM_query_soap, SLAM_query_by_id, SLAM_replicate, and SLAM_download_job for a
query job all report it the same way.
When a query has more fields than fit in one SOQL query, Speediful runs it as several queries over different fields of the same records and joins their results into the destination table. The joined table has the same limits.
The Results pane gives the reason after the colon:
| Message in the Results pane | What happened |
|---|---|
... they have <n> columns, and a SQL Server table can have at most 1,024. | The query selects more than 1,024 fields. Speediful stopped before downloading anything. |
... the fields selected need at least <n> bytes in every row, and a SQL Server row can hold at most 8,060. | The fields selected take more than 8,060 bytes in every row, even when they are empty. Speediful stopped before downloading anything. |
... at least one record's field values need more than the 8,060 bytes a SQL Server row can hold. | The fields fit, but the values in at least one record do not. SQL Server refused that record's row while Speediful wrote the records to SQL Server. |
If SQL Server finds one of the first two problems while Speediful writes the results, the
message reads every row would need more than the 8,060 bytes a SQL Server row can hold
or they have more than the 1,024 columns a SQL Server table can have.
What happened to your data
The lines after the message say which of these applies:
Nothing was downloaded from Salesforce, and <table> was not changed.Speediful checks the field types before it downloads any records.No rows were written to <table>. The data downloaded from Salesforce was kept in <n> tables, ...The query ran as several queries. Speediful downloaded every record, and keeps all of them in the tables it wrote them to before the join. The lines that follow name each table, with its row count and how many fields it holds. See The tables that keep your data.Speediful stopped at the first record that did not fit. <table> keeps only the records written to it before that one, which can be none, so it is not a complete copy of the results.The query ran as one SOQL query. See A destination table that holds only some records.No rows were written to <table>.on its own: SQL Server refused the table before Speediful wrote any records to it.
In the first two cases, and when SQL Server refused the table, an existing destination
table keeps its rows. With
@exists_action = 'drop-after', the previous table stays under its renamed copy, and the
Results pane names it in a line that starts drop-after: kept the previous. The exception
is a join that fails in a database using FULL recovery: there Speediful renames the
previous table only after it has joined every record, so the previous table keeps its own
name.
The procedure fails with error 88322 in every case, so a job step or a TRY...CATCH block
sees the failure.
A destination table that holds only some records
A query that fits in one SOQL query writes the records to the destination table as Speediful downloads them. When SQL Server refuses a record, the records written before it stay in the destination table, and the rest are not written. If the record was among the first ones written, the destination table exists but is empty.
Don't use that table as a copy of the results. Query again with fewer fields, or split the fields across two or more queries, as described under What to do.
What happened to the table that was there before depends on @exists_action:
@exists_action | The table that was there before |
|---|---|
'drop' (the default) | Dropped. The destination table holds only the records written before the one that did not fit. |
'rename' | Kept, under its renamed copy, with all its rows. |
'drop-after' | Kept, under its renamed copy, with all its rows. The Results pane names it in a line that starts drop-after: kept the previous. |
'append' | Kept, with all its rows, plus the records written before the one that did not fit. |
The tables that keep your data
When the join fails, the downloaded records stay in one table per query, in the
destination table's schema. Each is named after the destination table with _vshard_
and a number:
No rows were written to Product2. The data downloaded from Salesforce was kept in 3 tables, each with one row per record holding its Id and some of the fields queried:
dbo.Product2_vshard_0: 12,480 rows, Id and 240 more fields
dbo.Product2_vshard_1: 12,480 rows, Id and 240 more fields
dbo.Product2_vshard_2: 12,480 rows, Id and 96 more fields
Each table holds one row per record: its Id and one group of the fields you queried.
Between them, the tables hold every field. Read them the same way as the split tables
under What to do, joined on Id, selecting the columns you need:
SELECT p0.Id, p0.Name, p1.Region__c, p2.Supplier__c
FROM dbo.Product2_vshard_0 AS p0
JOIN dbo.Product2_vshard_1 AS p1 ON p1.Id = p0.Id
JOIN dbo.Product2_vshard_2 AS p2 ON p2.Id = p0.Id;
Don't copy them back into one table: that table has the same limit.
The tables are yours to keep or drop. Speediful does not drop them. Drop them once you no longer need them:
DROP TABLE dbo.Product2_vshard_0, dbo.Product2_vshard_1, dbo.Product2_vshard_2;
They never block a later run. When you query into the same destination table again,
whatever @exists_action you pass, Speediful replaces each table of the same name as it
writes the new results, and drops those tables once the new run joins them into the
destination. To keep a table from the failed run, rename it or copy it elsewhere before
you run the query again.
Which fields take up the row
Some field types take the same number of bytes in every row, even when the field is empty:
| Salesforce field type | Bytes in every row |
|---|---|
| Id, Lookup, Master-Detail | 36 |
Number, Currency, Percent, with the default high numeric precision | 17 |
Number, Currency, Percent, with display numeric precision | 5 to 17, by the field's length and decimal places (9 for a Currency field of length 16 with 2 decimal places) |
| Date/Time | 8 |
| Time | 4 |
| Date | 3 |
| Checkbox | 1 for every 8 checkboxes |
| Text, Text Area, Picklist, Email, Phone, URL | None when empty |
An object with 230 lookup fields needs 8,280 bytes for them alone, so a query that selects all of them cannot be stored in one table.
Text fields take room only in rows where they hold a value. A short value takes 2 bytes per character. SQL Server moves a long value out of the row, but leaves a pointer of about 24 bytes in its place. A record with hundreds of filled-in text fields can therefore need more than 8,060 bytes even when an empty row would fit. That is the third case in the table above, and it depends on the data, so Speediful cannot detect it before it downloads the records.
What to do
-
Query fewer fields. List the fields you need instead of using
SELECT *orFIELDS(ALL). Leaving out lookups and number fields you don't use frees the most room. -
Split the fields across two or more queries. Query each group into its own table, include
Idin every query, and join the tables onIdwhen you read them:EXEC dbo.SLAM_query@query = 'SELECT Id, Name, ProductCode, Family FROM Product2',@table = 'Product2_Part1';EXEC dbo.SLAM_query@query = 'SELECT Id, Region__c, Warehouse__c, Supplier__c FROM Product2',@table = 'Product2_Part2';SELECT p1.Id, p1.Name, p2.Region__cFROM dbo.Product2_Part1 AS p1JOIN dbo.Product2_Part2 AS p2 ON p2.Id = p1.Id;Splitting keeps every field. Don't combine the parts back into one table, because that table has the same limit.
-
Use narrower numeric columns. With numeric precision set to
display, Number, Currency and Percent columns are sized to each field's declared length, and withtextthey take no fixed room at all. The setting applies to every query,SLAM_replicateandSLAM_table_templaterun on the installation.displaycan also round decimal places that Salesforce returns, so choose it only if you need narrower numeric columns everywhere.
If the records were already downloaded, the kept tables hold them split this way, so you may not need to query again.
SLAM_replicate
SLAM_replicate always queries every field of an object, so a wide object can fail with
this message. Speediful lists the object under Failed objects: with the message and
carries on with the other objects. When the run ends, the procedure raises error 88322,
and the message names every object that failed, then the ones whose results are too wide:
One or more objects failed to replicate: Order, OrderItem. The results for Order cannot be stored in one SQL Server table.
The lines that say what happened to each object's data, including any kept tables, appear
at the end of the Results pane. Query that object with SLAM_query and a shorter field
list instead, or split its fields across two or more queries.
If another object in the same run failed because a write stalled (error 88321), the transaction log filled (error 88315), or SQL Server ran out of memory (error 88314), the procedure raises that error instead. See When objects fail.
Other errors while joining
A join that fails for another reason, such as a full transaction log (error 88315), keeps
the tables and names them the same way, after that error's own message. Fix the cause and
run the query again; the new run replaces the tables. If Speediful also prints a warning
that it could not drop a table ending in _vshard_stage, see
Queries split into several queries.
Related exit code
Speediful returns exit code 18, and the procedure raises error 88322. The reason and the destination table are in the Results pane.