CLR 函数调用远程 SQL Server
我对 SQL Server CLR 完全陌生。我明白，在业务逻辑用SQL实现起来确实很复杂的情况下，我们应该使用CLR。
VB.NET中有很多处理数据的功能，例如标准化数据。我正在尝试通过 CLR 来实现它们。这些函数首先访问远程服务器以获取一些参考数据，然后在本地服务器上进行处理。
但是无论我如何尝试，我都会遇到错误
System.NullReferenceException：未将对象引用设置为对象的实例。
或者它从远程服务器返回 null。
我们可以在 CLR 例程中访问远程服务器吗？如果是，怎么办？
I am totally new to SQL Server CLR. I understand that we should use CLR under the condition that business logic is really complicated to implement in SQL.
We have quite a few functions in VB.NET to process data, such as standardizing data. I am trying to implement them through CLR. The functions access a remote server first to get some reference data, then process on the local server.
However no matter how I try, I got Error
System.NullReferenceException: Object reference not set to an instance of an object.
or it returns null from the remote server.
Can we access a remote server in the CLR routine? If yes, how?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以在 .Net CLR 中访问远程服务器，但实际上不应该。
SQL Server 在协作多任务环境中运行，即线程被信任能够及时终止并完成其处理（以一种或另一种方式）。如果你开始做一些事情，比如调用远程方法（这可能会导致长时间的延迟），你可能会导致 SQL Server 工作线程挨饿，最终导致糟糕的事情发生。
另请参阅此问题
You can access remote servers in the .Net CLR but you really shouldn't.
SQL server operates in a cooperative multitasking environment, i.e. threads are trusted to terminate and complete their processing (one way or another) in a timely manner. If you start doing things like calling remote methods (which are liable to long delays) you are likely going to end up starving SQL server worker threads which will ultimately end up with Bad Things happening.
Also see this question
是的，你可以。您可以使用普通的 SqlCommand & .NET 框架中的 SqlConnection 类可以执行此操作。=，如果远程服务器是 SQL Server（我认为是）。
如果它们是网络服务器，是的，您可以使用网络服务。
附带说明一下。在 CLR 中执行操作时要非常小心，因为尽管 CLR 看起来很有吸引力，但在 SQL 2005 下您只有大约 512MB 内存，通过添加一些启动参数，您可以将其扩展到 2Gb。请注意。
编辑：
根据您的评论，我建议使用链接服务器，然后在本地重新创建远程表，然后在本地服务器上加入它。
您必须确保在本地机器上重新创建索引和键，并且为了速度起见，请在将记录插入表后执行此操作，否则在已填充的表上构建索引将需要很长时间。
Yes you can. You can use the normal SqlCommand & SqlConnection classes in the .NET framework to do so.=, if the remote servers are SQL Servers, which I assume it is.
If they are web servers, yes you can, use web services.
On a side note. Be very careful what you do in the CLR, because as attractive as CLR looks you only have about 512MB of memory under SQL 2005, and by adding some startup parameters you can push it out to 2Gb. Just be aware.
EDIT:
Based on your comments, I suggest using a linked server, and then re-creating the remote table locally and then joining to it on the local server.
You will have to make sure you re-create indexes and keys on the local box, and for speed -sake, do it after you inserted the records into the table, else building your indexes on an already populated table, will take a long time.