BCP执行存储过程导出CSV失败：无法解析列级别排序规则求助

Hey there, let's dig into why your BCP command works in one version but fails in another—this looks like a mix of driver compatibility, linked server configuration, and collation mismatches. Here's how to tackle each issue step by step:

1. Align ODBC Driver Versions #

First, notice the key difference in ODBC drivers between your two runs:

• The successful BCP 11.0.2100.60 uses ODBC Driver 13 for SQL Server
• The failing BCP 11.0.2270.0 relies on ODBC Driver 11

Driver version gaps often cause compatibility hiccups with OLEDB providers like Microsoft.ACE.OLEDB.12.0. Try installing ODBC Driver 13 (or a newer version like ODBC 17) on the Windows 10 machine running the failing BCP. If that doesn't fix it, consider upgrading the SQL Server client tools to match the version that works—11.0.2100.60 is likely tied to an older SP release that plays nicer with Driver 13.

2. Fix the Linked Server "TEMP" Initialization Error #

The root failure starts with the linked server on your Windows Server 2016 SQL instance. Let's address that first:

• Install the correct ACE provider: Make sure the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable is installed on the Server 2016 machine (match the architecture of your SQL Server—64-bit is standard now). Avoid mixing 32/64-bit providers, as that's a common source of "unspecified error" messages.
• Verify linked server settings:
  • In SQL Server Management Studio, go to Linked Servers > Providers > Microsoft.ACE.OLEDB.12.0 and enable the Allow inprocess option. This is usually required for file-based data sources like CSVs.
  • Check that the SQL Server service account has read access to the CSV file path configured in the "TEMP" linked server.
  • Test the linked server directly from the Server 2016 instance: run SELECT * FROM OPENQUERY(TEMP, 'SELECT * FROM [your_csv_file.csv]') to confirm it works outside of BCP. If this fails, fix that first before re-trying BCP.

3. Resolve Column Collation Mismatches #

The final error Unable to resolve column level collations happens when the data from the linked server has collations that don't match your local database's collation. Here's how to fix it:

• Modify your stored procedure: Update <db>.dbo.andy to explicitly convert string columns to your local database's collation. For example:

  SELECT 
      CAST(your_string_column AS VARCHAR(255)) COLLATE SQL_Latin1_General_CP1_CI_AS,
      your_numeric_column -- no collation needed for non-string types
  FROM OPENQUERY(TEMP, 'SELECT * FROM source_data')
  

  Replace SQL_Latin1_General_CP1_CI_AS with your actual database collation (get it with SELECT DATABASEPROPERTYEX('<db>', 'Collation')). Alternatively, use COLLATE DATABASE_DEFAULT to automatically match the local DB's collation.

4. Tweak BCP Command Parameters #

Small parameter changes can make a big difference across versions:

• Try replacing /r 0x0A with /r "\n"—some older BCP versions handle hex line endings less reliably with ODBC 11.
• Add the /k parameter to preserve null values as empty strings, which can reduce implicit conversion issues that trigger collation errors.
• Test with explicit credentials (/U <username> /P <password>) instead of trusted connection (/T) to rule out permission problems between your Windows 10 machine and the SQL server.

内容的提问来源于stack exchange，提问作者Andrew Harris