记录集字段值从SQL Server使用varchar(MAX)数据类型返回null [英] Recordset field values return null from SQL server with varchar(MAX) data type

问题描述

我有一个SQL 2008服务器，该服务器中有一个表，该表具有一列，其数据类型为varchar(MAX).我正在尝试使用ADO将此表中的某些列导入Excel 2010.这是我使用的代码示例:

I have an SQL 2008 server, in the server is a table that has a column with a data type of varchar(MAX). I am trying to import certain columns from this table into Excel 2010 using ADO. Here is an example of the code I am using:

Dim con As Object
Set con = CreateObject("ADODB.Connection")

cmd.CommandText = "SELECT dbo.(Table Name).Name From dbo.(Table)"
cmd.CommandType = 1
con.open
cmd.ActiveConnection = con
Dim rst as Object
set rst = cmd.Execute

ActiveSheet.cells(targetRange.Row, targetRange.Column).CopyFromRecordset rst

代码执行得很好，但是因为名称"列设置为varchar(MAX)，所以工作表中都不会显示任何名称.我知道这一点是因为我复制了SQL Server，并将列的数据类型更改为varchar(1024)，并且按应有的方式工作.

The code executes just fine, however because the column Name is set to varchar(MAX) none of the names show up in the worksheet. I know this because I made a copy of the SQL server and changed the data type for the column to varchar(1024) and it works the way it should.

我还尝试将记录集中的数据手动放入电子表格中，但是没有运气.所以问题是我无法更改真正的SQL Server.

I also tried placing the data in the recordset into the spreadsheet manually but with no luck. So the problem is I can not change the real SQL server.

是否存在我可以添加到Select语句的命令或可以使用的强制转换，该命令会将varchar(MAX)转换为Excel可以使用的数据类型，或者可以执行其他操作以使读入时数据不为空?

Is there a command that I can add to the Select statement or a cast that I can use that will convert the varchar(MAX) to a data type that Excel can use, or is there something else I can do so that the data is not null when I read it in?

我一直在研究这个问题，有的线程已经关闭了一天，但是他们正在谈论在SQL Server上使用select的强制转换，这种转换在这种情况下是无效的，或者在某些其他程序中却无法使用没有帮助.

I have been looking into this problem for a day now with some threads coming close but they are talking about using select on the SQL server with a cast which doesn't work in this case, or in some other program, which doesn't help.

推荐答案

ADO显然不能正确处理VARCHAR(MAX)(或您可能期望的方式)，这是关于返回零列大小的事情，因为它没有定义，就像用于VARCHAR(n).

ADO apparently doesn't handle VARCHAR(MAX) properly (or how you might expect), something about returning a zero column size as its not defined like it would be for VARCHAR(n).

您回答了自己的问题，因为您只需要将列转换为定义的长度即可！

You answered your own question, as you just needed to cast your column to something with a defined length!

CAST(column AS type)

或者您的情况下

SELECT CAST(dbo.(Table Name).Name AS VARCHAR(1024)) FROM dbo.(Table)

这篇关于记录集字段值从SQL Server使用varchar(MAX)数据类型返回null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！