从Azure Blob Storage批量上传JSON至Azure SQL DB时遇Msg 4866等错误求助

Hey there, let's work through this frustrating bulk insert issue you're hitting! Since you've already got the Azure Blob connection sorted, we just need to tweak how the JSON data is being loaded into your raw table. The 4866 error ("column is too long") almost always stems from a mismatch between your table schema and how SQL parses the JSON during the bulk load. Here are the most effective fixes to try:

1. Expand the Column Length in Your Target Table #

The error specifically calls out row 1, column 1 being too long—so your raw table's first column (the one meant to store JSON) probably doesn't have enough space. JSON documents can vary widely in size, so using a fixed-length VARCHAR or NVARCHAR is risky.

• Fix: Alter the column to use NVARCHAR(MAX) or VARCHAR(MAX) to accommodate any size of JSON content.
• Example SQL:

  ALTER TABLE dbo.raw ALTER COLUMN your_json_column NVARCHAR(MAX);
  

  (Replace your_json_column with the actual name of your column in the raw table.)

2. Explicitly Define Field/Row Terminators for JSON #

BULK INSERT defaults to settings designed for CSV data, which don't play nicely with JSON. If you don't specify terminators, SQL might misinterpret the entire JSON document as a single overly long field.

• If your Blob contains one single JSON document:
  Use ROWTERMINATOR = '0x00' (this tells SQL the file ends at the null character, which works for single documents):

  BULK INSERT dbo.httpjson 
  FROM 'xxxxxx [path]' 
  WITH ( 
      DATA_SOURCE = 'MyAzureBlobStorage',
      FIELDTERMINATOR = '\t', -- Pick a delimiter that doesn't exist in your JSON
      ROWTERMINATOR = '0x00'
  );
  

• If your Blob uses JSON Lines format (one JSON object per line):
  Use ROWTERMINATOR = '\n' to split rows at each newline:

  BULK INSERT dbo.httpjson 
  FROM 'xxxxxx [path]' 
  WITH ( 
      DATA_SOURCE = 'MyAzureBlobStorage',
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR = '\n'
  );
  

3. Switch to OPENROWSET for More Reliable JSON Loading #

BULK INSERT can be finicky with unstructured data like JSON. Using OPENROWSET gives you more control over how the Blob content is read, and it’s often more reliable for JSON imports.

• Example SQL to insert directly into your raw table:

  INSERT INTO dbo.raw (your_json_column)
  SELECT BulkColumn
  FROM OPENROWSET(
      BULK 'xxxxxx [path]',
      DATA_SOURCE = 'MyAzureBlobStorage',
      SINGLE_CLOB -- Use SINGLE_NCLOB for Unicode JSON, or FILESTREAM if needed
  ) AS blob_data;
  

  This reads the entire Blob content as a single string and inserts it straight into your MAX-length column, skipping the messy terminator configuration.

4. Validate Your Blob's JSON Content #

Double-check that the JSON in your Azure Blob doesn’t have hidden issues:

• Download the Blob file locally and open it in a text editor to look for unexpected line breaks, invisible characters, or malformed JSON.
• Use a JSON validator tool to confirm the content is properly formatted (invalid JSON can also cause weird parsing errors that manifest as "column too long" messages).

Since you’re already connected to the Blob storage, one of these fixes should get you over the finish line!

内容的提问来源于stack exchange，提问作者Lasse Rindom