network error

microsoft.public.sqlserver.programming

30 Dec 2005 2:58 AM

tin

hi all,

i am having a clr table-value function that gets a web page and parse some
integer values, then populates an ArrayList, which will be exposed as a
table.

it was fine, it would returns a table with one column, populated with some
int from the function. but this week, it no longer working, and it returns
the following error instead. the code and database settings have been the
same. the only thing is that the server ip is changed. but the db is still
accessible from other clients with the changed ip. it's only when the
traffic is initiated from within the database won't go thru? would it really
be the change of ip? if so, how can i re-configure it??

please advice!

ERROR:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user defined routine or
aggregate 'udf_LuceneConnect':
System.Net.WebException: Unable to connect to the remote server --->
System.Net.Sockets.SocketException: A connection attempt failed because the
connected party did not properly respond after a period of time, or
established connection failed because connected host has failed to respond
System.Net.Sockets.SocketException:
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot,
SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure,
Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState
state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
   at System.Net.HttpWebRequest.GetResponse()
   at LuceneConnector.LuceneConnect(String IndexName, Int32 ID, String
SearchText)

QUERY:

select * from dbo.udf_LuceneConnect('', 0, 'http://192.168.2.13/test/ids.htm')

CLR Code:

using System;
using System.IO;
using System.Net;
using System.Web;
using System.Text;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public partial class LuceneConnector
{
    [SqlFunction(Name="udf_LuceneConnect", FillRowMethodName = "FillRow",
TableDefinition="id int")]
    public static IEnumerable LuceneConnect(string IndexName, int ID, string
SearchText)
    {
        string url = SearchText;
        HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(url);
        HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
        StreamReader strm = new StreamReader(webresp.GetResponseStream(),
Encoding.ASCII);
        ArrayList ids = new ArrayList();
        string line = null;
        while ((line = strm.ReadLine()) != null)
        {
            ids.Add(int.Parse(line));
        }
        strm.Close();
        return ids;
    }

    public static void FillRow(Object obj, out int id)
    {
        id = (int)obj;
    }
}