SQL Server从UNC路径批量插入无记录无错误问题排查

我有一个位于网络驱动器的CSV文件，希望通过SQL Server存储过程中的BULK INSERT加载。该CSV文件包含类似"text1","text2"的记录格式，共27列、约90行。我已创建匹配的格式文件：

• 将CSV复制到服务器本地后，执行以下OPENROWSET语句可正常加载所有记录：

SELECT *
FROM OPENROWSET (BULK N'K:\Upload\Shipping\testship.csv',
                 FORMATFILE = 'K:\Upload\Shipping\shipping.fmt',
                 FIRSTROW = 2) j

• 但使用UNC路径执行相同操作时，仅返回列头，无任何记录且无错误（指定错误日志也无效）：

SELECT *
FROM OPENROWSET (BULK N'\\servername\Marketing\SHIPPING\Vessel_Departures_and_Cut-off\5Aug.csv',
                 FORMATFILE = '\\servername\Marketing\SHIPPING\Vessel_Departures_and_Cut-off\shipping.fmt',
                 FIRSTROW = 2) j

已排除网络位置权限问题（其他无格式文件的UNC文件可正常加载）。后续发现：将UNC文件复制到本地后，Notepad显示两者编码不同；用Notepad打开原文件复制内容到新文件保存后，可正常加载。

一、确定CSV文件的编码 #

方法1：通过Notepad++查看 #

直接用Notepad++打开原UNC路径下的CSV文件，查看窗口右下角显示的编码标识（常见的如UTF-8 BOM、ANSI、UTF-16 LE、GB2312等）。

方法2：通过PowerShell检测 #

• 检测UTF-8 BOM（SQL Server对带BOM的UTF-8识别需要显式指定编码）：

Get-Content -Path "\\servername\Marketing\SHIPPING\Vessel_Departures_and_Cut-off\5Aug.csv" -Raw | Select-String -Pattern '^\xef\xbb\xbf'

如果输出匹配结果，说明文件带UTF-8 BOM。

• 自动检测编码：

$fileBytes = Get-Content -Path "\\servername\Marketing\SHIPPING\Vessel_Departures_and_Cut-off\5Aug.csv" -Raw -AsByteStream
[System.Text.Encoding]::DetectEncodingFromByteCount($fileBytes[0..20])

二、在OPENROWSET中指定编码 #

SQL Server的BULK操作支持通过CODEPAGE参数显式指定文件编码，常用编码对应的CODEPAGE值：

示例：指定UTF-8编码加载 #

SELECT *
FROM OPENROWSET (BULK N'\\servername\Marketing\SHIPPING\Vessel_Departures_and_Cut-off\5Aug.csv',
                 FORMATFILE = '\\servername\Marketing\SHIPPING\Vessel_Departures_and_Cut-off\shipping.fmt',
                 FIRSTROW = 2,
                 CODEPAGE = '65001') j

其他方式：通过XML格式文件指定编码 #

如果你的格式文件是XML格式，可直接在根节点添加CODEPAGE属性：

<FORMAT XMLNS="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" CODEPAGE="65001">
  <!-- 列定义内容 -->
</FORMAT>

针对UTF-16文件的特殊处理 #

如果文件是UTF-16编码，也可以使用DATAFILETYPE = 'widechar'参数替代CODEPAGE：

SELECT *
FROM OPENROWSET (BULK N'\\servername\Marketing\SHIPPING\Vessel_Departures_and_Cut-off\5Aug.csv',
                 FORMATFILE = '\\servername\Marketing\SHIPPING\Vessel_Departures_and_Cut-off\shipping.fmt',
                 FIRSTROW = 2,
                 DATAFILETYPE = 'widechar') j

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