SQL Server 2005 CLR function System.NullReferenceException: Object reference not set to an instance of an object

Question

My coworker wrote a solution in VB.NET to manipulate strings. I have been asked to implement them through CLR. The solution consists of 2 namespaces and 1 module. I imported the namespaces to my CLR code to create the functions for SQL Server.

After I deploy the dll file to sql server, the functions work fine except occasionally I get the error

Msg 6522, Level 16, State 1, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "ufn_getDDPhone":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

   at DataLibrary.DataReference.StandardReference.GetValue(String value, Boolean ignorecase)

   at DataLibrary.DataModule.GetCountryCodeStandard(String value)

   at DataLibrary.DataStructure.DataPhone.Refresh()

   at DataLibrary.DataStructure.DataPhone..ctor(String text)

   at DataLibrary.UserDefinedFunctions.ufn_getDDPhone(String phone)

my call to this functions is

UPDATE Contact_Bogus SET ddphone=dbo.ufn_getDDPhone(PHONE) WHERE PHONE is not null or PHONE <>''

If I use    SELECT dbo.ufn_getDDPhone(PHONE) FROM DBO.CONTACT_BOGUS WHERE PHONE is not null or PHONE <>'' first, the execute update command, it works fine. I get this error especially if I haven't execute this query manually for a while.

I traced down to the VB by the error message

Function GetCountryCodeStandard(ByVal value As String) As String
        Dim standard As String

        If Not CountryCodeStandard.IsOpen Then
            CountryCodeStandard.Open()
        End If
        standard = CountryCodeStandard.GetValue(value, True)

        If standard Is Nothing Then
            Return ""
        End If
        Return standard
    End Function

 Private CountryCodeStandard As New StandardReference("CountryCode_Standard")

Public Class StandardReference

        Public Name As String
        Public IsOpen As Boolean
        Private m_ConnStr As String = "Database=reference;Uid=user;Pwd=test;"
        Private m_SQL As String
        Private m_Conn As SqlConnection
        Private m_Ad As SqlClient.SqlDataAdapter
        Private m_Tbl As DataTable
        Private m_Row As DataRow
        Private m_Index As Integer

        Public Sub New()

        End Sub

        Public Sub New(ByVal name As String)
            Me.Name = name
            IsOpen = False
        End Sub

        Public Sub Open()
            m_SQL = "select Value, Standard from " & Name & " order by Value"
            m_Conn = New SqlConnection(m_ConnStr)
            Try
                m_Conn.Open()
                m_Ad = New SqlClient.SqlDataAdapter(m_SQL, m_Conn)
                m_Tbl = New DataTable()
                m_Ad.Fill(m_Tbl)
                IsOpen = True
            Catch ex As Exception
                Console.WriteLine("Error: " & ex.ToString)
            End Try
        End Sub

        Public Sub Close()
            m_Conn.Close()
        End Sub

        Public Function GetValue(ByVal value As String, ByVal ignorecase As Boolean) As String
            For Each m_Row In m_Tbl.Rows
                If (ignorecase AndAlso UCase(m_Row(0).ToString()) = UCase(value)) OrElse (Not ignorecase AndAlso m_Row(0).ToString() = value) Then
                    Return m_Row(1).ToString()
                End If
            Next
            Return Nothing
        End Function

    End Class

I searched a lot and still couldn't figure out why this error comes up from time to time. Your help is greatly appreciated.

Answer 1

Hi Starr,

Could you please post the tables' schema and provide some sample data ? It would be helpful to reproduce your issue.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Hi Peja,

The connection opened connects to a different database on the same server. here is the table schema

CREATE TABLE [dbo].[CountryCode_Standard](
    [Value] [nvarchar](5) NOT NULL,
    [Standard] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_Unique_CountryCode] PRIMARY KEY CLUSTERED ) ON [PRIMARY]

INSERT INTO [dbo].[CountryCode_Standard](VALUE, STANDARD
SELECT '1',    'USA'
UNION SELECT '1-242',    'Bahamas'
UNION SELECT '1-246',    'Barbados'
UNION SELECT '1-264',    'Anguilla'
UNION SELECT '1-268',    'Antigua & Barbuda'
UNION SELECT '1-284',    'Virgin Islands British'
UNION SELECT '1-340',    'Virgin Islands US'
UNION SELECT '1-345',    'Cayman Islands'

I think connection is the problem. For the StandardReference class, if I modify the code in StandardReference as the code below.  It seems working fine. But it is extremely slow. It takes 15 seconds to process one record.

Public Class StandardReference

        Public Name As String
        Public IsOpen As Boolean
        Private m_ConnStr As String = "Database=reference;Uid=user;Pwd=test;"
        Private m_SQL As String
        Private m_Conn As SqlConnection
        Private m_Ad As SqlClient.SqlDataAdapter
        Private m_Tbl As DataTable
        Private m_Row As DataRow
        Private m_Index As Integer

        Public Sub New()

        End Sub

        Public Sub New(ByVal name As String)
            Me.Name = name
            'IsOpen = False
        End Sub

        Public Sub Open()
            'm_SQL = "select Value, Standard from " & Name & " order by Value"
            'm_Conn = New SqlConnection(m_ConnStr)
            'Try
            '    m_Conn.Open()
            '    m_Ad = New SqlClient.SqlDataAdapter(m_SQL, m_Conn)
            '    m_Tbl = New DataTable()
            '    m_Ad.Fill(m_Tbl)
            'Catch ex As Exception
            '    Console.WriteLine("Error: " & ex.ToString)
            'End Try
        End Sub

        Public Sub Close()
            m_Conn.Close()
            IsOpen = False
            m_Conn = Nothing
        End Sub

        Public Function GetValue(ByVal value As String, ByVal ignorecase As Boolean) As String
            m_SQL = "select Value, Standard from " & Name & " order by Value"
            m_Conn = New SqlConnection(m_ConnStr)
            Try
                m_Conn.Open()
                m_Ad = New SqlClient.SqlDataAdapter(m_SQL, m_Conn)
                m_Tbl = New DataTable()
                m_Ad.Fill(m_Tbl)
                m_Conn.Close()
                'IsOpen = False
                For Each m_Row In m_Tbl.Rows
                    If (ignorecase AndAlso UCase(m_Row(0).ToString()) = UCase(value)) OrElse (Not ignorecase AndAlso m_Row(0).ToString() = value) Then
                        Return m_Row(1).ToString()
                    End If
                Next
                Return Nothing
            Catch ex As Exception
                Console.WriteLine("Error: " & ex.ToString)
                Return Nothing
            End Try
            'For Each m_Row In m_Tbl.Rows
            '    If (ignorecase AndAlso UCase(m_Row(0).ToString()) = UCase(value)) OrElse (Not ignorecase AndAlso m_Row(0).ToString() = value) Then
            '        Return m_Row(1).ToString()
            '    End If
            'Next
            'Return Nothing
        End Function
    
    End Class

I really appreciate your help.

Starr

---

SW

Answer 3

Hi Peja,

I think I found the culprit eventually. In the connection string, I need to set ENLIST=FALSE. After I set that, it seems working smoothly now. Thanks again for your time.

Starr

---

SW