CLR function calls a remote SQL Server

I am totally new to SQL Server CLR. I understand that we should use CLR under the condition that business logic is really complicat开发者_如何转开发ed 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?

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

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.