I get the following error when I try to compile my module with the following method:
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.
Here is my full code:
PLEASE HELP! AND THANK YOU!!!
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.
VB.NET:
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 SubHere is my full code:
VB.NET:
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 ModulePLEASE HELP! AND THANK YOU!!!
