SQL: 指定的转换无效

Question

注意：我正在寻找为什么会发生这种情况以及如何修复它，而不是寻找解决方法。这似乎是一个服务器(SQL Server或连接字符串)问题。

我有一个连接到SQL Server 2008数据库（Database A）的程序，并且我有使用内联SQL运行的代码，其中包含整数和字符串返回，一切正常。但是我被要求切换到另一个SQL Server 2008数据库（Database B），现在所有内容都以字符串形式返回，我从C＃获取了指定的类型转换无效错误，而当我连接到SQL Server 2008（Database A）时，它不会出现此错误。这是一条内联SQL语句，因此SQL语句不会更改，数据库的表模式也是相同的。它在整数主键上执行此操作。有人有任何想法吗？

我最初认为这是2000到2008的问题，但我现在在2008中也遇到了同样的问题。这两个数据库都在同一实例的SQL Server上，这些是连接字符串：

连接字符串

  Server=Server01\instance;Database=Fraud_Micah; Trusted_Connection=yes <- Server 2008 (this one does not)
  Server=Server02\instance;Database=Fraud; Trusted_Connection=yes <- Server 2008 (this one works)

两个数据库的DB兼容级别都是100

查询语句

select *, delimeter, file_filetype.LocalPath, ArchiveDir, EmailList
from file_importtable 
join file_filetype on file_importtable.FileTypeID = file_filetype.ID
where importsuccessdate is null and transferdate is not null
and remotediscoverdate is not null 
and OriginalFileName in ('Test987.xml.pgp')

fileTypeID 是出问题的地方-> InvalidCastException: 指定的转换无效。

C# 代码（请注意读取器类型为 SQLDataReader）

if (!(reader.IsDBNull(reader.GetOrdinal("FileTypeID"))))
{
    file.FileTypeID = reader.GetInt32(reader.GetOrdinal("FileTypeID"));
}

这里是列定义：[FileTypeID] [int] NULL，表中没有空值。

我认为这段 C# 代码与此无关，它是一个可空的整数：public int? FileTypeID { get; set; }

在调试模式下：reader["FileTypeID"] -> "1"，实际上是一个字符串，但为什么连接到 2008 数据库时会返回一个 1 而不是一个 "1" 呢？

2008 表 A 定义

[ProcessSuccessDate] [datetime] NULL,
[ProcessSuccessUser] [datetime] NULL,
[FileTypeID] [int] NULL,
[HoldDate] [datetime] NULL,

2008 年表 B 定义

ProcessSuccessDate] [datetime] NULL,
[ProcessSuccessUser] [datetime] NULL,
[FileTypeID] [int] NULL,
[HoldDate] [datetime] NULL,

< p > file.FileTypeID = (int)reader["FileTypeID"]; 产生相同的结果。

执行

     file.FileTypeID (int)reader.GetInt32(reader.GetOrdinal("FileTypeID"));

这可以运行，但我不想对已经应该作为int返回的每个列都这样做，也不想像这样编写SQL。

     select Convert(int, FileTypeID) as FileTypeId, delimeter, file_filetype.LocalPath, ArchiveDir, EmailList

我也可以绕过这个问题，但是我想知道为什么我已经在表中将类型设置为整数，却还需要这样做。我可能会把所有类型都放在表中作为字符串。目前我不想寻找解决方法，我想了解为什么它没有像应该的那样工作。

- Micah Armantrout

---

错误信息是否告诉了你涉及到的类型？你能发布完整的错误信息吗？ - Mark Byers

你确定 FileTypeID 是一个 int 类型（而不是 long 等其他类型）吗？ - Kirk Woll

那么错误很可能来自于C#代码。文件.FileTypeID定义的类型是什么？ - KreepN

您能再次验证一下您的2000和2008数据库中模式是否相同吗？目前我认为唯一有意义的是该列更改为varchar。 - Adam V

如果在 SQL 中放置一个显式转换会发生什么？将 FileTypeId 转换为整数（convert(int, FileTypeId)）。 - mfussenegger

Answer 1

你列出的两个表中都显示int作为数据类型，但听起来这是两个不同数据库中同一张表的两个版本。

我认为另一个表，也就是你要进行JOIN的那个表，在一个数据库中有一个不同的数据类型。

在两个数据库中，File_fileType.Id的数据类型是什么？

你的JOIN：

join file_filetype on file_importtable.FileTypeID = file_filetype.ID

导致了隐式转换。

上面的图表显示了SQL Server允许或执行的数据类型转换。

你能展示一下以上JOIN中两个版本表的DDL吗？

- JNK

Answer 2

对我来说，这听起来像是一些奇怪的配置设置，可能很难找到。有一种叫做 Redgate SQL Compare 工具可以出色地检测数据库模式之间的差异，非常棒！

他们提供14天试用。建议下载并比较您的数据库。

- dana

Answer 3

SQL的排序规则是否匹配？如果您在不同数据库之间遇到不同的行为，那么这可能是您的问题。右键单击每个数据库并选择属性，排序规则应指定为类似于* Latin1_General_CI_AS *的内容。如果它们不同，那么一定要排除此原因。

- Liam

---

我本来不知道，但是是的，不幸的是这两个排序规则确实匹配，并且它们都是SQL_Latin1_General_CP1_CI_AS。 - Micah Armantrout

好的，只是确认一下，数据库中的数据类型匹配吗？ - Liam

你尝试过将 SQL Server Profiler 附加到你的数据库上，看看 SQL 的运行方式是否有所不同吗？是一个使用 EXEC，而另一个直接处理 SQL 吗？ - Liam

Answer 4

在整个计划中，我决定删除表并使用以下方式重新创建它：

[脚本表为] -> [创建]

从出现问题的同一张表中重新插入所有相同的数据。这解决了我的问题。因此，我不认为表的DML发生了变化，数据也没有改变，但这就是解决我的问题的方法。

- Micah Armantrout

Answer 5

Visual Studio（根据this，版本为Premium和Ultimate）附带了一个数据库比较工具 - 您选择两个数据库，它会显示差异并提供一组脚本，将一个移动到另一个状态。从那里开始，尝试嗅探不同的配置（因为我认为这可能是配置问题）。请查看Visual Studio中的Data\Schema Compare和Data\Data Compare。

- Filip

Answer 6

已经确认至少存在一个错误。由于我没有整个图像，很难准确。因此，我建议进行两次检查。

第一次检查应该在使用INFORMATION_SCHEMA.COLUMNS的两个数据库上进行（对于所需级别来说，这已经足够了），以验证模式是相同的。您可以在http://msdn.microsoft.com/en-us/library/aa933218(v=sql.80).aspx（SQL 2000）或http://msdn.microsoft.com/en-us/library/ms188348（SQL 2008）中阅读有关INFORMATION_SCHEMA.COLUMNS的更多信息。来自不同来源的相同信息。

样例：

SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

如果架构相同，则代码中可能存在您呈现的部分中不可见的错误。要验证您的完整代码是否正确，请通过将选择语句更改如下进行简单检查:

select CAST(fileTypeID as int), ..., file_filetype.LocalPath, ArchiveDir, EmailList
from file_importtable 
join file_filetype on file_importtable.FileTypeID = file_filetype.ID
where importsuccessdate is null and transferdate is not null
and remotediscoverdate is not null 
and OriginalFileName in ('Test987.xml.pgp')

如果失败了，那么请使用断点和 F11 以逐步测试的方式在调试模式下运行。（有时回到根源可能会帮助我们看到眼前看不见的东西——但在这种情况下这种可能性更小）。

通过上述步骤，您将找到原因（希望如此）。

最后一个提示（在做任何其他操作之前可能是您的第一步）：确保您的 SQL 服务器已打补丁并安装了最新的服务包。

- user1088520

Answer 7

你需要把字符串转换为其他类型。

file.FileTypeID = int.Parse((string)reader["FileTypeID"]);

编辑：如果您想要一个解决方法，应该适用于SQL Server 2008和2000

file.FileTypeID = Convert.ToInt32(reader["FileTypeID"]);

- Matthew

---

如果它不能回答问题。 - Micah Armantrout

如果您不想进行强制转换，我怀疑您将不得不在提供程序中进行更改，您正在使用JET还是标准的ADO.net提供程序？ - Matthew

我正在使用System.Data.SqlClient，我认为这应该是可以的。 - Micah Armantrout

不确定那是什么，您是否正在使用http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx（SqlConnection）并传递连接字符串？编辑：好的，我不知道出了什么问题。 - Matthew

@KreepN 我同意，但有时你也只需要它尽快工作。 - Matthew

Answer 8

file.FileTypeID = Convert.ToInt32(reader["FileTypeID"]); 而不是 file.FileTypeID = (int)reader["FileTypeID"];

- levi

---

这是相同的概念，一个权宜之计，我正在寻找为什么事情会以这种方式工作的原因，而不是掩盖它。谢谢！ - Micah Armantrout

Answer 9

看起来你还没有解决这个问题，也许可以尝试使用GetSqlInt32方法。

file.FileTypeID = reader.GetSqlInt32((fieldTypeIDOrdinal).ToNullableInt32();

注意：我没有包含 "IsDBNull" 检查，因为将 "SqlInt32" 与 "ToNullableInt32" 结合使用可以处理这个问题。

其中 "ToNullableInt32" 是一个扩展方法。

public static int? ToNullableInt32(this SqlInt32 value)
{
    return value.IsNull ? (int?) null : value.Value;
}

顺便提一下，您提到这是一个性能优先的应用程序，在这种情况下，您可能希望预计算您的序数值，而不是在每个reader.Read()循环中使用reader.GetOrdinal("FileTypeID")

- Chris Moutray

---

我几乎可以确定数据库或连接字符串存在问题，但我不确定具体是什么。 - Micah Armantrout

是的，我认为这也与数据库设置或连接字符串/数据提供程序有关。 - Chris Moutray

你有没有尝试创建一个简单的示例项目（例如基本控制台应用程序），连接到一个简单的数据库，一个表，一个列设置，其中该列的类型为“int”？可能只需要5分钟左右就可以创建类似的东西，并且可能有助于隔离是你的代码还是数据库的问题... - Chris Moutray

仍然遇到了相同的问题，已经尝试过这个方法。 - Micah Armantrout