我使用SQL Server的INSERT INTO OPENROWSET将每列超过255个字符插入Excel文件时遇到问题 [英] I have a problem inserting more than 255 chars per column into an Excel file using INSERT INTO OPENROWSET from SQL Server

问题描述

使用openrowset将数据从SQL Server导出到已创建的.xlsx文件时，出现错误.

大多数情况下它可以正常工作，但是当数据作为大字符串进入字段时，在插入Excel时会显示此错误:

该语句已终止，字符串或二进制数据将被截断.

数据被插入到表中，但是在Excel中插入时会出现此错误.请帮助我找到解决方案.

解决方案

由于错误提到数据将被截断"，因此应在存储空间较小的占位符或字段中提供较长的字符串值.

例如，源字段可能具有数据类型nvarchar(max)，并且在您的SQL开发中或存在映射的地方，您可以将值分配为较小的数据大小类型.例如，在源表中，您有一个字符串值5000个字符，但在此过程中将其分配给nvarchar(4000)，则将发生数据截断

我建议您检查语句中的数据映射

I am getting an error while exporting data from SQL Server to an already created .xlsx file using openrowset.

It works fine most of times, but when the data comes in of the field as a large string, while inserting into Excel, it shows this error:

The statement has been terminated, string or binary data would be truncated.

Data gets inserted into table, but while inserting in Excel, this error appears. Please help me find a solution.

解决方案

As the error mentions "data would be truncated", you should be provide a longer string value into a placeholder or field that has a smaller storage size.

For example, the source field may have data type nvarchar(max) and in your SQL development or where a mapping exists, you assing the values into a smaller data size type. For example, in source table you have a string value 5000 characters, but during the process it is assigned to a nvarchar(4000) then a data truncation will occur

I would suggest you to check data mappings in your statements

这篇关于我使用SQL Server的INSERT INTO OPENROWSET将每列超过255个字符插入Excel文件时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！