troubleshooting Question

'STORING TO A STATIC FIELD IS NOT ALLOWED IN SAFE ASSEMBLIES'

Avatar of dbansal1
dbansal1 asked on
.NET ProgrammingVisual Basic.NETMicrosoft SQL Server 2008
5 Comments2 Solutions1979 ViewsLast Modified:
I am trying to create a clr that is writtin vb.net, that takes information from sql server 2008 and puts them into an array, does manipulation, and writes to an sql table..

Howerver, I get the error:  

c:\documents and settings\dbansal\my documents\visual studio 2010\projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.sql(131-131): Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6212, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because method 'matrix_initialization' on type 'SqlServerProject1.Module1' in safe assembly 'SqlServerProject1' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

An error occurred while the batch was being executed.
 
The supposed method causing an error: (all my code is attached):

01.Sub matrix_initialization()
02.  Dim row As Integer
03.  Dim column As Integer
04.  Using conn As New SqlConnection("context connection = true")
05.    Dim getAggregationsCommand As New SqlCommand()
06.    getAggregationsCommand.CommandText = _
07.      "select count," & _
08.      "duration,duration2,duration3,duration4,duration5," & _
09.      "duration6 from dbo.tblduryieldconttickerrating "
10.  
11.    getAggregationsCommand.Connection = conn
12.    conn.Open()
13.    Dim reader As SqlDataReader = getAggregationsCommand.ExecuteReader
14.    While reader.Read()
15.      A(1, 1) = reader.GetInt64(0)
16.      A(1, 2) = reader.GetInt64(1)
17.      A(1, 3) = reader.GetInt64(2)
18.      A(1, 4) = reader.GetInt64(3)
19.      A(2, 4) = reader.GetInt64(4)
20.      A(3, 4) = reader.GetInt64(5)
21.      A(4, 4) = reader.GetInt64(6)
22.    End While
23.    reader.Close()
24.    getAggregationsCommand.ExecuteNonQuery()
25.    conn.Close()
26.  End Using
27.  For row = 2 To ORDER
28.    For column = 1 To ORDER - 1
29.      A(row, column) = A(row - 1, column + 1)
30.    Next column
31.  Next row
32.  For row = 1 To ORDER
33.    For column = 1 To ORDER
34.      If (row = column) Then
35.        A(row, column + ORDER) = 1
36.      Else
37.        A(row, column + ORDER) = 0
38.      End If
39.    Next column
40.  Next row
41.  results_row = 6
42.  Using conn2 As New SqlConnection("context connection = true")
43.    Dim getGArrayCommand As New SqlCommand()
44.    getGArrayCommand.CommandText = _
45.       "select yield," & _
46.      "duryield,dur2yield,dur3yield"
47.    getGArrayCommand.Connection = conn2
48.    conn2.Open()
49.    Dim reader2 As SqlDataReader = getGArrayCommand.ExecuteReader
50.    While reader2.Read()
51.      g(1) = reader2.GetInt64(0)
52.      g(2) = reader2.GetInt64(1)
53.      g(3) = reader2.GetInt64(2)
54.      g(4) = reader2.GetInt64(3)
55.    End While
56.    reader2.Close()
57.    getGArrayCommand.ExecuteNonQuery()
58.    conn2.Close()
59.  End Using
60.End Sub




Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Math

Partial Public Class TrendLineStoredProcedure
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Shared Sub getTrendLine(ByVal ticker As SqlChars, ByVal rating As SqlInt32)
        Using conn As New SqlConnection("context connection = true")
            Dim getTrendLineCommand As New SqlCommand()
            Dim tickerParam As New SqlParameter("@tickerin", SqlDbType.NVarChar)
            Dim ratingParam As New SqlParameter("@ratingin", SqlDbType.Int)

            tickerParam.Value = ticker
            ratingParam.Value = rating

             getTrendLineCommand.Parameters.Add(tickerParam)
             getTrendLineCommand.Parameters.Add(ratingParam)

            getTrendLineCommand.CommandText = _
                 "exec dbo.[spgetduryieldcounttickerrating] @ticker = @tickerin, @rating = @ratingin"
            Module1.polynomial_trendline()



            getTrendLineCommand.Connection = conn
             conn.Open()
            getTrendLineCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

Here is my module:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Math
Module Module1

    Const ORDER As Integer = 4
    Const TWICE_ORDER As Integer = 2 * ORDER
    Dim A(ORDER, TWICE_ORDER) As Double
    Dim g(ORDER) As Double
    Dim b(ORDER) As Double      'this is the coefficients array
    Dim results_row As Integer
    Const inversion_sheet As String = "inversion"
    Const data_sheet As String = "data"
    Const SUMS_ROW As Integer = 1
    Const DURATION_COLUMN As Integer = 2
    Const TRENDLINE_COLUMN As Integer = 14
    Const DATA_INCREMENT As Double = 0.1

    Sub polynomial_trendline()
        ' matrix_initialization()
        matrix_inversion()  'Gauss-Jordan Elimination
        coefficients()
         trendline_data()
     End Sub
    Sub matrix_initialization()

        Dim row As Integer
        Dim column As Integer

        Using conn As New SqlConnection("context connection = true")
            Dim getAggregationsCommand As New SqlCommand()


            getAggregationsCommand.CommandText = _
                 "select count," & _
                 "duration,duration2,duration3,duration4,duration5," & _
                 "duration6 from dbo.tblduryieldconttickerrating "

            getAggregationsCommand.Connection = conn
             conn.Open()
            Dim reader As SqlDataReader = getAggregationsCommand.ExecuteReader
             While reader.Read()
                A(1, 1) = reader.GetInt64(0)
                A(1, 2) = reader.GetInt64(1)
                A(1, 3) = reader.GetInt64(2)
                A(1, 4) = reader.GetInt64(3)
                A(2, 4) = reader.GetInt64(4)
                A(3, 4) = reader.GetInt64(5)
                A(4, 4) = reader.GetInt64(6)
             End While
            reader.Close()
            getAggregationsCommand.ExecuteNonQuery()
            conn.Close()
        End Using

        For row = 2 To ORDER
            For column = 1 To ORDER - 1
                A(row, column) = A(row - 1, column + 1)
             Next column
        Next row
        For row = 1 To ORDER
            For column = 1 To ORDER
                 If (row = column) Then
                    A(row, column + ORDER) = 1
                 Else
                    A(row, column + ORDER) = 0
                 End If
            Next column
        Next row

        results_row = 6


        Using conn2 As New SqlConnection("context connection = true")
            Dim getGArrayCommand As New SqlCommand()


            getGArrayCommand.CommandText = _
               "select yield," & _
                 "duryield,dur2yield,dur3yield"
            getGArrayCommand.Connection = conn2
             conn2.Open()
            Dim reader2 As SqlDataReader = getGArrayCommand.ExecuteReader
             While reader2.Read()
                g(1) = reader2.GetInt64(0)
                g(2) = reader2.GetInt64(1)
                g(3) = reader2.GetInt64(2)
                g(4) = reader2.GetInt64(3)
             End While
            reader2.Close()
            getGArrayCommand.ExecuteNonQuery()
            conn2.Close()
        End Using
    End Sub


    Sub matrix_inversion()

        Dim row As Integer
        Dim column As Integer
        Dim max_val As Double
        For row = 1 To ORDER
            pivot(row)
        Next row
    End Sub


    Sub coefficients()
        Dim j, k As Integer
        For k = 1 To ORDER
            b(k) = 0
             For j = 1 To ORDER
                b(k) = b(k) + A(j, k + ORDER) * g(j)
             Next j
        Next k
        For j = 1 To ORDER
            ' Worksheets(data_sheet).Cells(1, TRENDLINE_COLUMN + j - 1).Value = b(j)
        Next j
    End Sub

    Function fitted_yield(ByVal duration As Double) As Double
        Dim k As Integer
        fitted_yield = 0
        For k = 1 To ORDER
            fitted_yield = fitted_yield + b(k) * (duration ^ (k - 1))
         Next k
    End Function




    Sub pivot(ByVal pivot_row As Integer)

        Dim row As Integer
        Dim column As Integer
        Dim max_val As Double
        Dim max_row As Integer
        Dim multiplier As Double

        max_val = A(pivot_row, pivot_row)
        max_row = pivot_row

        For row = pivot_row + 1 To ORDER

             If (Abs(A(row, pivot_row)) > Abs(max_val)) Then

                max_val = A(row, pivot_row)
                max_row = row
             End If
        Next row
        If (max_row > pivot_row) Then
            swap_rows(pivot_row, max_row, max_val)
        Else
            normalize(pivot_row, max_val)

        End If
        For row = 1 To ORDER
            If (row <> pivot_row) Then
                multiplier = -A(row, pivot_row)

                 For column = pivot_row To TWICE_ORDER

                    A(row, column) = A(row, column) + multiplier * A(pivot_row, column)

                  Next column

             End If

        Next row

        ' write_results("pivot stage")
    End Sub


    Sub normalize(ByVal pivot_row As Integer, ByVal max_val As Double)
        Dim column As Integer
        For column = pivot_row To TWICE_ORDER
            A(pivot_row, column) = A(pivot_row, column) / max_val
         Next column
        ' write_results("normalize")
    End Sub

    Sub swap_rows(ByVal pivot_row As Integer, ByVal second_row As Integer, ByVal max_val As Double)

        Dim row As Integer
        Dim column As Integer
        Dim temp As Double
        For column = pivot_row To TWICE_ORDER
            temp = A(pivot_row, column)
            A(pivot_row, column) = A(second_row, column) / max_val
            A(second_row, column) = temp
        Next column
        '  write_results("swap_rows")

    End Sub



    Public Sub trendline_data()

        Dim min_duration As Double
        Dim max_duration As Double
        Dim duration As Double
        Dim k As Double
        Dim data_row As Integer
        Dim data_count As Integer

        Using conn As New SqlConnection("context connection = true")
            Dim getminmaxcommand As New SqlCommand()


            getminmaxcommand.CommandText = _
                 "select count,minduration,maxduration from dbo.tblduryieldconttickerrating "

            getminmaxcommand.Connection = conn
             conn.Open()
            Dim reader As SqlDataReader = getminmaxcommand.ExecuteReader
             While reader.Read()
                data_count = reader.GetInt64(0)
                min_duration = reader.GetInt64(1)
                max_duration = reader.GetInt64(2)
               
             End While
            reader.Close()
            getminmaxcommand.ExecuteNonQuery()
            conn.Close()
        End Using

        Using conn As New SqlConnection("context connection = true")
            Dim writetrendline As New SqlCommand()


            writetrendline.CommandText = _
                 "insert into tbltrendline(yield,duration) values  duration,fitteld_yield"

          

            writetrendline.Connection = conn
             conn.Open()

            min_duration = Int(min_duration / DATA_INCREMENT)
            max_duration = 1 + Int(max_duration / DATA_INCREMENT)

             For k = min_duration To max_duration

                duration = k * DATA_INCREMENT
                data_row = SUMS_ROW + k - min_duration + 2

                 writetrendline.ExecuteNonQuery()

                 '  Worksheets(data_sheet).Cells(data_row, TRENDLINE_COLUMN).Value = duration
                 ' Worksheets(data_sheet).Cells(data_row, TRENDLINE_COLUMN + 1).Value = fitted_yield(duration)

             Next k

             conn.Close()
        End Using


        'data_count = 

        'Worksheets(data_sheet).Cells(SUMS_ROW, 12)

        'min_duration = 

        'Worksheets(data_sheet).Cells(SUMS_ROW + 2, DURATION_COLUMN).Value
        'max_duration = 

        'Worksheets(data_sheet).Cells(SUMS_ROW + data_count + 1, DURATION_COLUMN).Value

    


    End Sub

End Module
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros