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.
我对SQL Server CLR完全陌生。我理解，我们应该在业务逻辑在SQL中实现起来非常复杂的情况下使用CLR。
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.
在VB中有很多函数。NET处理数据，例如标准化数据。我试图通过CLR来实现它们。函数首先访问远程服务器以获取一些引用数据，然后在本地服务器上进行处理。
However no matter how I try, I got Error
然而无论我怎么努力，我还是犯了错误
System.NullReferenceException: Object reference not set to an instance of an object.
系统。NullReferenceException:对象引用没有设置为对象的实例。
or it returns null from the remote server.
或者从远程服务器返回null。
Can we access a remote server in the CLR routine? If yes, how?
我们可以在CLR例程中访问远程服务器吗?如果是,如何?
2 个解决方案
#1
3
You can access remote servers in the .Net CLR but you really shouldn't.
你可以在。net CLR中访问远程服务器，但是你不应该这样做。
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.
SQL server在协作的多任务环境中运行，即线程被信任以及时的方式终止和完成它们的处理(无论哪种方式)。如果您开始调用远程方法(这可能会导致长时间的延迟)等操作，那么您很可能最终会饿死SQL server worker线程，最终导致糟糕的事情发生。
Also see this question
也看到这个问题
#2
1
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.
是的,你可以。您可以使用. net框架中的普通SqlCommand & SqlConnection类来实现这一点。=，如果远程服务器是SQL服务器，我假设它是。
If they are web servers, yes you can, use web services.
如果它们是web服务器，您可以使用web服务。
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.
边注。在CLR中要非常小心，因为尽管CLR看起来很吸引人，但是在SQL 2005中，您只有512MB的内存，通过添加一些启动参数，您可以将其扩展到2Gb。只是要注意。
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.
您必须确保在本地框上重新创建索引和键，为了速度的方便，在将记录插入到表中之后再这样做，否则在已填充的表上构建索引将需要很长时间。
#1
3
You can access remote servers in the .Net CLR but you really shouldn't.
你可以在。net CLR中访问远程服务器，但是你不应该这样做。
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.
SQL server在协作的多任务环境中运行，即线程被信任以及时的方式终止和完成它们的处理(无论哪种方式)。如果您开始调用远程方法(这可能会导致长时间的延迟)等操作，那么您很可能最终会饿死SQL server worker线程，最终导致糟糕的事情发生。
Also see this question
也看到这个问题
#2
1
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.
是的,你可以。您可以使用. net框架中的普通SqlCommand & SqlConnection类来实现这一点。=，如果远程服务器是SQL服务器，我假设它是。
If they are web servers, yes you can, use web services.
如果它们是web服务器，您可以使用web服务。
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.
边注。在CLR中要非常小心，因为尽管CLR看起来很吸引人，但是在SQL 2005中，您只有512MB的内存，通过添加一些启动参数，您可以将其扩展到2Gb。只是要注意。
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.
您必须确保在本地框上重新创建索引和键，为了速度的方便，在将记录插入到表中之后再这样做，否则在已填充的表上构建索引将需要很长时间。