sql-server - SQL 跟踪/分析器 - 显示的日期格式不明确 (.NET Core/EF)

Question

我们有一个安装了英语(美国)语言的 SQL Server 2008R2 实例。 SSMS > 实例 > 属性 > 常规。

我们使用默认语言“英式英语”设置了登录。 SSMS > 安全 > 登录 > 用户 > 属性。

前端 .NET Core 应用程序通过表单/Web API 生成插入语句 (EF)，它使用上面设置的登录创建“sp_executesql”系统存储过程。

使用 SQL Server Profiler，我可以看到生成的插入 SQL 包含格式为 'yyyy-mm-dd'. 的字符串日期。例如 "2019-01-15 10:59:19.410" .

在执行该语句之前，有一个 exec sp_reset_connection 后跟以下 SET 语句(我相信这与连接池共享有关，以确保每次登录的正确设置)。

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language British
set dateformat dmy
set datefirst 1
set transaction isolation level read committed

令我困惑的是，即使 yyyy-mm-dd 中有日期，该语句也能成功运行。格式，而环境设置为英式英语 (DMY)。例如 - "2019-01-15 10:59:19.410" .

我了解到，如果 DMY 是当前设置，“2019-01-15 10:59:19.410”将被解释为 2019 年第 15 个月的第一天。

例如，这将失败，因为 SQL 将尝试将日期字符串解释为 yyyy-dd-mm

SET LANGUAGE [British English];

SELECT
    CAST('2019-01-13' AS DATETIME);

Msg 242, Level 16, State 3, Line 3 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

将语言设置为英语 (us_english) 将字符串解释更改为 MDY 并且上述语句有效。

我知道我可以使用非歧义的日期字符串，但我遇到的问题与 EF 前端生成的日期字符串有关。

我无法弄清楚为什么该语句成功运行。显然存在从日期字符串到 DATETIME 类型的隐式转换。

如果我从 SQL 探查器复制语句并粘贴到 SSMS 并尝试针对同一个数据库/同一个用户运行，那么它会像我预期的那样失败。

Msg 8114, Level 16, State 1, Line 14 Error converting data type varchar to datetime.

所以...

我了解从字符串日期转换为 DATETIME 类型并在 SSMS 中运行查询时获得一致的结果时发生了什么，但我不明白为什么在前端运行在 SSMS 中失败的查询。

如果没有很好地解释，我深表歉意。

Answer 1

实际发生的是 Entity Framework 生成参数化语句，然后使用(二进制)TDS 协议(protocol)将这些语句传递给服务器，这就是应用程序与 SQL Server 通信的方式。 DATETIME作为 RPC 参数传递的值不作为字符串传递——它们作为表示精确值的字节序列传递(例如，当作为 2019-01-15T10:59:19.410(两个小端整数)发送时，D6 A9 00 00 AF 16 B5 00 表示为 DATETIME ).(免责声明:我实际上并没有使用数据包嗅探器进行检查，所以我可能弄错了字节)。

当这些参数化语句由 SQL Profiler 呈现时，就好像它们是文本批处理一样，它会将值重新格式化为字符串，因为 T-SQL 没有 DATETIME 的原生文字格式。值。不幸的是，它使用的格式不能保证在所有可能的语言设置下都可以往返，这应该被视为 Profiler 中的一个错误。它所缺少的只是一个 T ，但这是一个重要的遗漏。

类似的事情发生在神秘的 exec sp_reset_connection 身上。您可以看到所有使用连接池的应用程序的痕迹都随处可见——实际上没有应用程序生成这些调用，如果您尝试在 Management Studio 中执行此语句，您将看到没有 sp_reset_connection程序。在协议(protocol)级别，数据包 header 中只有一个“重置此连接”位，该位被转换为一个虚构的调用，带有虚构的 SET。当它作为语句或事件跟踪呈现时，表示当前选项的语句。

简而言之:您的应用程序运行良好，但是如果您想准确地重放它们进行调用时发生的情况，则复制粘贴 Profiler 输出将不起作用。