Sql Server x64 和 x86 链接服务器
我有一个 Visual FoxPro 表，需要从 Sql Server 访问。 在 Sql Server x86 中，我只创建一个链接服务器。 不幸的是，VFP 没有 x64 驱动程序 - 因此 Sql Server x64 无法为其创建链接服务器。
到目前为止，我已经提出了以下选项 - 其中没有一个是我特别喜欢的：
- 设置一个 x86 Sql Server 用作中继，以便查询来自 x64 -> x86-> VFP。
我并不关心这个，因为除了开发人员之外，我还是系统管理员。 因此，这意味着我需要修补、维护和监视另一个 Sql Server - 可能还需要另一个服务器（假设我不只是使用单独的实例）。
此外，由于 VFP 提供程序不支持 4 部分语法，因此我必须使用 OPENQUERY。 想到所有需要将 OPENQUERY 语句嵌入到另一个 OPENQUERY 语句中的单引号转义让我头晕……
- 创建一个 CLR 表值函数，尽管程序集（大概？）也是 x64 -所以我必须离开进程（IPC？Web服务？）才能实际运行查询
事实证明，TVF 需要架构，因此此选项并不像我最初想象的那么干净。 我做了一个峰值，将 WCF 客户端引入 MSSQL，它返回单个 XML 列，然后可以使用 Sql XML 数据类型函数对其进行解析。 它有效，而且实际上比 OPENQUERY 查询起来更好一些，因为它实际上将变量作为参数。 这节省了我大部分的单引号和 EXEC 舞蹈。
当然，Sql 中的 WCF 完全不受支持，而且听起来像是一个相当大的 hack。 我对性能和可靠性持非常严重的保留态度。
- 停止从 Sql Server 到 VFP 进行查询，并重写大量客户端代码
显然，这是“正确”的答案。 但是，有大量客户端代码依赖于 Sql Server 表和 VFP 表之间的联接。 重写这些东西来填充临时表或进行客户端连接似乎是一个相当大的负担。
希望有人能提出更好的选择，或者一些类似的经历。
I have a Visual FoxPro table that I need to access from Sql Server. In Sql Server x86, I would just create a linked server. Unfortunately, there is no x64 driver for VFP - so Sql Server x64 can't create a linked server to it.
So far, I've come up with following options - none of which I'm particularly fond of:
- Set up an x86 Sql Server to be used as a relay, so that queries go from x64 -> x86 -> VFP.
I don't really care for this, as in addition to being dev, I'm also sysadmin. So, this means I need to patch, maintain, and monitor yet another Sql Server - and possibly yet another server (assuming I don't just use a separate instance).
Also, since the VFP provider doesn't work with 4 part syntax, I have to use OPENQUERY. Thinking of all the single quote escaping that'd need to happen to have an OPENQUERY statement embedded into another OPENQUERY statement makes my head spin....
- Create a CLR Table Valued Function, though the assembly would (presumably?) also be x64 - so I'd have to go out of proc (IPC? Webservice?) to actually run queries
Turns out that TVFs require a schema, so this option isn't as clean as I initially thought. I did a spike to get a WCF client into MSSQL, which returns a single column of XML that can then be parsed with the Sql XML datatype functions. It works, and is actually a little bit nicer to query than OPENQUERY since it actually takes variables as parameters. That saves me most of the single quote and EXEC dance.
Of course, WCF inside Sql is wholly unsupported, and smells like a pretty big hack. I have pretty serious reservations on performance and reliability.
- Stop making queries from Sql Server to VFP, and rewrite a good bit of client code
Obviously, this is the "right" answer. But, there is a good deal of client code that relies on joins between Sql Server tables and VFP tables. Rewriting this stuff to populate a temp table or do client side joins seems like a rather large burden.
Here's hoping someone can suggest a better alternative, or some similar experiences.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我想我找到了替代方案。 Microsoft 已发布更新Access 驱动程序，有 32 位和 64 位版本。 与原始 Jet OleDB 驱动程序一样，这将允许您从 SQL Server访问 dBase 文件格式 x64。
唯一的限制是 DBF 必须采用 ISAM 支持的 dBASE 格式之一。 我已经使用 dBASE IV 格式做了一些测试，并且使用以下连接字符串似乎可以工作。
I think I found an alternative. Microsoft has released an updated driver for Access, which comes in both 32bit and 64bit flavors. Like the original Jet OleDB driver, this will allow you to access dBase file formats from SQL Server x64.
The only restriction is that the DBF must be in one of the dBASE formats supported by ISAM. I have done a few tests using a dBASE IV format and it seems to work, using the following connection string.
这是一个令人讨厌的问题，我同意。
如果您可以忍受延迟，以 32 位模式运行的 SSIS 定期（可能是在同一 SP 触发的作业中按需）将数据导入到 SQL Server 本机表是另一种选择。 这将取决于数据更改的频率以及数据稍微过时的可能性问题。
It's a nasty problem, I agree.
SSIS run in 32-bit mode to import the data on a regular basis (perhaps on demand, in a job triggered by the same SP) to a SQL Server native table is another option if you can stand the delay. It would depend on the frequency of data change and problems with chance of slightly out of date data.