在SQL Server 2012中怎样才能使用的TimeZoneInfo在SQLCLR组装 [英] How does one use TimeZoneInfo in a SQLCLR assembly in SQL Server 2012

问题描述

我想实现的SQL Server 2012。然而在时区转换的TimeZoneInfo标有 MayLeakOnAbort 属性。这使我定义的SQL函数（它使用的TimeZoneInfo）被调用时，运行时错误。

I want to implement time zone conversion within SQL Server 2012. However, TimeZoneInfo is marked with the MayLeakOnAbort attribute. This causes a runtime error when the SQL function I defined (which uses TimeZoneInfo) is invoked.

报告错误如下：

System.Security.HostProtectionException：试图执行被禁止的CLR主机的操作

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

受保护的资源（仅适用于完全信任）是：所有

The protected resources (only available with full trust) were: All

所要求的资源是：MayLeakOnAbort

The demanded resources were: MayLeakOnAbort

文件暗示，我可以使用的SafeHandle来解决这个问题，漏水，但我不知道怎样。

Documentation hints that I can use "SafeHandle" to get around this leakage issue but I do not see how.

那么，一个人如何使用类的TimeZoneInfo在SQLCLR方面？

So, how does one use the class TimeZoneInfo in a SQLCLR context?

后续行动：

我发现另一个问题这里的网站，其中，虽然它与SQL 2005的交易，prescribes这与2012年有此解决方案方面，虽然，这是不令人满意的作品的行为。

I have found another question here on the site which, though it deals with SQL 2005, prescribes an action which works with 2012. There are aspects of this solution, though, which are unsatisfying.

推荐答案

更新答案的

Updated Answer

我写我在原来的答复，，你可以在这里找到谈到了效用。这是（恕我直言）做时区转换在SQL Server中的最佳方式。

I've written the utility I spoke about in the original answer, which you can find here. This is (IMHO) the best way to do time zone conversions in SQL Server.

的原来的答案的

Original Answer

不幸的是，与时区的SQL Server的工作没有很好的解决方案。

我调查了大量你链接的问题，伴随着这个也。有没有内置的时区功能，并在SQLCLR任何使用的TimeZoneInfo 的要求组件注册为不安全。这通常是不希望的。

I investigated heavily the issue you linked, along with this one also. There are no built-in time zone functions, and any use of TimeZoneInfo in SQLCLR requires the assembly to be registered as "unsafe". This is usually not desired.

我还研究使用野田佳彦时间从SQLCLR。你可以阅读一下在这个问题 。它也必须注册为不安全的方式，因为某些项目的内部缓存。

I also investigated using Noda Time from SQLCLR. You can read about it in this issue. It also had to be registered as "unsafe" because of the way certain items are cached internally.

最终，与任何一个项目，该问题是没有办法缓存在SQLCLR任何东西。在一个线程安全的方式不能使用静态变量，你不能做任何的线程同步或使用类，如 ConcurrentDictionary 。 SQL都想拥有完全控制组件的线程模型。只有单线程的用一次且扔掉的风格code工作在安全的组件。我挖深入到这了这个问题：多线程高速缓存中的SQL CLR

Ultimately, with either item, the problem is that there is no way to cache anything in SQLCLR. You can't use static variables in a thread-safe manner, and you cant do any thread synchronization or use classes like ConcurrentDictionary. SQL wants to have full control of the threading model of the assembly. Only single-threaded use-once-and-throw-away style code works in "safe" assemblies. I dug deep into this in this question: Multithreaded caching in SQL CLR

但愿会有的最终的是构建野田的时间，将工作在SQLCLR，但是这将是一个特别的版本，这并不做任何缓存。因此，它不会执行迅速，但它会完成这项工作，同时仍然是安全的。

Hopefully, there will eventually be a build of Noda Time that will work in SQLCLR, but it will be a special build that doesn't do any caching. So it won't perform as quickly, but it will get the job done while still being safe.

的TimeZoneInfo 不太可能改变。所以，除非SQL Server团队以往任何时候都直接带来了时区功能集成到SQL Server的正确（如Oracle和Postgres的做），那么你只有几个选择：

TimeZoneInfo isn't likely to change. So unless the SQL Server team ever brings time zone functions directly into SQL Server properly (like Oracle and Postgres do), then you've only got a few options:

• 不要尝试时区转换数据层研究。与日期时间或 DATETIME2 值在UTC工作，或使用 DATETIMEOFFSET 与任何值偏移。但这样做时区在应用层之间的所有转换。这是现在我的最好的建议。

• Don't attempt time zone conversions in the data layer. Work with datetime or datetime2 values in UTC, or use datetimeoffset values with any offset. But do all conversions between time zones in the application layer. This is my best recommendation for now.

所有时区中的数据复制到实际的SQL表，并写入与该数据的工作职能。这是不是最好的主意，因为数据的变化往往使表的维护是一个挑战。同时获得准确的功能，包括所有的夏时制规则，可以是具有挑战性。我不知道有这个捆绑漂亮和整洁的任何项目，但如果有人是 - 那么请让我知道在评论

Copy all of the data for the time zones into actual SQL tables, and write functions that work with that data. This isn't the best idea, because the data changes often so table maintenance can be a challenge. Also getting the functions accurate, including all of the rules of daylight saving changes, can be challenging. I am not aware of any project that has this bundled up nice and neat, but if anyone is - then please let me know in comments.

启用 xp_regread ，并直接与时区的数据从Windows注册表项工作。更新会为你做，但你仍然有书面这些功能相同的挑战。和启用注册表中读取可能只是尽可能多的安全风险，为使不安全的CLR程序集呢。

Enable xp_regread and work with the timezone data directly from the windows registry keys. Updates would be done for you, but you still have the same challenges in writing these functions. And enabling registry reads might be just as much of a security risk as enabling unsafe CLR assemblies anyway.

另一个想法我正在考虑是写一个 IANA /奥尔森TZDB 解析器和功能专为SQL Server中。这将类似于上面的选项2，但在一个维护的方式进行，并在IANA标准数据，代替视窗时区。也许我会得到这个有一天，或许会有人打我给它。同样，我不知道有任何当前的项目，做这个的，但如果有人知道的一个，请让我知道在评论。 （完成 - 看到更新顶部）

Another idea I am considering is to write an IANA/Olson TZDB parser and functions specifically for SQL Server. This would be similar to option 2 above, but done in a maintainable way, and with IANA standard data instead of Windows time zones. Maybe I'll get to this someday, or maybe someone will beat me to it. Again, I am not aware of any current project that does this, but if someone knows of one, please let me know in comments. (done - see update at top)

关于 SWITCHOFFSET - 这只能当你已经知道目标偏移。这是成功的一半，并可能是为什么微软仍标记 DATETIMEOFFSET 不日光节约意识中的的文档。

Regarding SWITCHOFFSET - that only works when you already know the target offset. That's half the battle, and probably why Microsoft still marks datetimeoffset as not "daylight savings aware" in the docs.

这篇关于在SQL Server 2012中怎样才能使用的TimeZoneInfo在SQLCLR组装的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！