SQL CLR Function error?

S

Smokey Grindle

What does this mean?

Msg 6522, Level 16, State 1, Procedure
ValidateCoveragesHaveSameReferenceAndValues, Line 0

A .NET Framework error occurred during execution of user defined routine or
aggregate 'ValidateCoveragesHaveSameReferenceAndValues':

System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT validation
method.

System.InvalidOperationException:

at
System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode
eRc)

at System.Data.SqlServer.Internal.ClrLevelContext.GetDatabase(SmiEventSink
sink, Int32* pcbLen, IntPtr* ppwsName)

at
Microsoft.SqlServer.Server.InProcConnection.GetCurrentDatabase(SmiEventSink
eventSink)

at System.Data.SqlClient.SqlInternalConnectionSmi.Activate()

at
System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString
options, Object providerInfo, DbConnection owningConnection)

at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)

at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at
Reschini.BDBSQLLib.UserDefinedFunctions.ValidateCoveragesHaveSameReferenceAndValues(SqlInt32
RateDefinition, SqlInt32 CoverageA, SqlInt32 CoverageB)

..





with this procedure



Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

'Checks to see if two coverages have the same account, aggregation or
carrier value for their payer and payee based on what values the rate
definition specifies it should have for each. If one is a business partner,
then that end is ignored since this one should not be compared on both ends.

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Shared Function ValidateCoveragesHaveSameReferenceAndValues(ByVal
RateDefinition As SqlInt32, ByVal CoverageA As SqlInt32, ByVal CoverageB As
SqlInt32) As SqlBoolean

Dim Result As Boolean = True

Using cn As New SqlConnection("context connection=true")

cn.Open()

Using dtCoverageA As New DataTable, dtCoverageB As New DataTable,
dtRateDefinition As New DataTable

' load info on the items

Using cmd As New SqlCommand("select
Coverages.CoverageID,Coverages.[Type],Coverages.AggregationID,AccountsJunCoverages.AccountID,CoverageTypes.LineOfBusinessID,LinesOfBusiness.CarrierID
FROM coverages INNER JOIN AccountsJunCoverages ON Coverages.CoverageID =
AccountsJunCoverages.CoverageID INNER JOIN CoverageTypes ON
CoverageTypes.CoverageTypeID = Coverages.[Type] INNER JOIN LinesOfBusiness
ON LinesOfBusiness.LineOfBusinessID = CoverageTypes.LineOfBusinessID WHERE
Coverages.CoverageID = @CoverageID")

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("@CoverageID", CoverageA)

dtCoverageA.Load(cmd.ExecuteReader)

cmd.Parameters("@CoverageID").Value = CoverageB

dtCoverageB.Load(cmd.ExecuteReader)

End Using

Using cmd As New SqlCommand("SELECT PayerID,PayeeID ,RCVPayer.[Value] as
PayerName,RCVPayee.[Value] as PayeeName FROM RateDefinitions INNER JOIN
RateCriteriaValue AS RCVPayer ON RCVPayer.CriteriaValueID =
RateDefinitions.PayerID INNER JOIN RateCriteriaValue AS RCVPayee ON
RCVPayee.CriteriaValueID = RateDefinitions.PayeeID WHERE RateDefinitions.ID
= @RateDefinitionID", cn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("@RateDefinitionID", RateDefinition)

dtRateDefinition.Load(cmd.ExecuteReader)

End Using

' now that we have the data, compare it, check payer first

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "ACCOUNT" Then

' check if both coverages have same account

If CInt(dtCoverageA.Rows(0)("AccountID")) <>
CInt(dtCoverageB.Rows(0)("AccountID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "AGGREGATION"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("AggregationID")) <>
CInt(dtCoverageB.Rows(0)("AggregationID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "CARRIER" Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("CarrierID")) <>
CInt(dtCoverageB.Rows(0)("CarrierID")) Then

Result = False

End If

End If

' check payee

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "ACCOUNT" Then

' check if both coverages have same account

If CInt(dtCoverageA.Rows(0)("AccountID")) <>
CInt(dtCoverageB.Rows(0)("AccountID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "AGGREGATION"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("AggregationID")) <>
CInt(dtCoverageB.Rows(0)("AggregationID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "CARRIER" Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("CarrierID")) <>
CInt(dtCoverageB.Rows(0)("CarrierID")) Then

Result = False

End If

End If

cn.Close()

End Using

End Using







' Add your code here

Return New SqlBoolean(Result)

End Function

End Class
 
R

RobinS

You have an error in your code, in
ValidateCoveragesHaveSameReferenceAndValues,
probably while reading the database. Can
you post the code that created the error?

Robin S.
-----------------------
Smokey Grindle said:
What does this mean?

Msg 6522, Level 16, State 1, Procedure
ValidateCoveragesHaveSameReferenceAndValues, Line 0

A .NET Framework error occurred during execution of user defined routine
or aggregate 'ValidateCoveragesHaveSameReferenceAndValues':

System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT
validation method.

System.InvalidOperationException:

at
System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode
eRc)

at System.Data.SqlServer.Internal.ClrLevelContext.GetDatabase(SmiEventSink
sink, Int32* pcbLen, IntPtr* ppwsName)

at
Microsoft.SqlServer.Server.InProcConnection.GetCurrentDatabase(SmiEventSink
eventSink)

at System.Data.SqlClient.SqlInternalConnectionSmi.Activate()

at
System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString
options, Object providerInfo, DbConnection owningConnection)

at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)

at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at
Reschini.BDBSQLLib.UserDefinedFunctions.ValidateCoveragesHaveSameReferenceAndValues(SqlInt32
RateDefinition, SqlInt32 CoverageA, SqlInt32 CoverageB)

.





with this procedure



Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

'Checks to see if two coverages have the same account, aggregation or
carrier value for their payer and payee based on what values the rate
definition specifies it should have for each. If one is a business
partner, then that end is ignored since this one should not be compared on
both ends.

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Shared Function ValidateCoveragesHaveSameReferenceAndValues(ByVal
RateDefinition As SqlInt32, ByVal CoverageA As SqlInt32, ByVal CoverageB
As SqlInt32) As SqlBoolean

Dim Result As Boolean = True

Using cn As New SqlConnection("context connection=true")

cn.Open()

Using dtCoverageA As New DataTable, dtCoverageB As New DataTable,
dtRateDefinition As New DataTable

' load info on the items

Using cmd As New SqlCommand("select
Coverages.CoverageID,Coverages.[Type],Coverages.AggregationID,AccountsJunCoverages.AccountID,CoverageTypes.LineOfBusinessID,LinesOfBusiness.CarrierID
FROM coverages INNER JOIN AccountsJunCoverages ON Coverages.CoverageID =
AccountsJunCoverages.CoverageID INNER JOIN CoverageTypes ON
CoverageTypes.CoverageTypeID = Coverages.[Type] INNER JOIN LinesOfBusiness
ON LinesOfBusiness.LineOfBusinessID = CoverageTypes.LineOfBusinessID WHERE
Coverages.CoverageID = @CoverageID")

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("@CoverageID", CoverageA)

dtCoverageA.Load(cmd.ExecuteReader)

cmd.Parameters("@CoverageID").Value = CoverageB

dtCoverageB.Load(cmd.ExecuteReader)

End Using

Using cmd As New SqlCommand("SELECT PayerID,PayeeID ,RCVPayer.[Value] as
PayerName,RCVPayee.[Value] as PayeeName FROM RateDefinitions INNER JOIN
RateCriteriaValue AS RCVPayer ON RCVPayer.CriteriaValueID =
RateDefinitions.PayerID INNER JOIN RateCriteriaValue AS RCVPayee ON
RCVPayee.CriteriaValueID = RateDefinitions.PayeeID WHERE
RateDefinitions.ID = @RateDefinitionID", cn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("@RateDefinitionID", RateDefinition)

dtRateDefinition.Load(cmd.ExecuteReader)

End Using

' now that we have the data, compare it, check payer first

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "ACCOUNT"
Then

' check if both coverages have same account

If CInt(dtCoverageA.Rows(0)("AccountID")) <>
CInt(dtCoverageB.Rows(0)("AccountID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "AGGREGATION"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("AggregationID")) <>
CInt(dtCoverageB.Rows(0)("AggregationID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "CARRIER"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("CarrierID")) <>
CInt(dtCoverageB.Rows(0)("CarrierID")) Then

Result = False

End If

End If

' check payee

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "ACCOUNT"
Then

' check if both coverages have same account

If CInt(dtCoverageA.Rows(0)("AccountID")) <>
CInt(dtCoverageB.Rows(0)("AccountID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "AGGREGATION"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("AggregationID")) <>
CInt(dtCoverageB.Rows(0)("AggregationID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "CARRIER"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("CarrierID")) <>
CInt(dtCoverageB.Rows(0)("CarrierID")) Then

Result = False

End If

End If

cn.Close()

End Using

End Using







' Add your code here

Return New SqlBoolean(Result)

End Function

End Class
Click to expand...
 
W

William \(Bill\) Vaughn

It probably means you have left your Function marked as SAFE. To do IO in a
CLR function, it must be marked as EXTERNAL ACCESS. See Chapter 14.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Smokey Grindle said:
What does this mean?

Msg 6522, Level 16, State 1, Procedure
ValidateCoveragesHaveSameReferenceAndValues, Line 0

A .NET Framework error occurred during execution of user defined routine
or aggregate 'ValidateCoveragesHaveSameReferenceAndValues':

System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT
validation method.

System.InvalidOperationException:

at
System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode
eRc)

at System.Data.SqlServer.Internal.ClrLevelContext.GetDatabase(SmiEventSink
sink, Int32* pcbLen, IntPtr* ppwsName)

at
Microsoft.SqlServer.Server.InProcConnection.GetCurrentDatabase(SmiEventSink
eventSink)

at System.Data.SqlClient.SqlInternalConnectionSmi.Activate()

at
System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString
options, Object providerInfo, DbConnection owningConnection)

at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)

at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at
Reschini.BDBSQLLib.UserDefinedFunctions.ValidateCoveragesHaveSameReferenceAndValues(SqlInt32
RateDefinition, SqlInt32 CoverageA, SqlInt32 CoverageB)

.





with this procedure



Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

'Checks to see if two coverages have the same account, aggregation or
carrier value for their payer and payee based on what values the rate
definition specifies it should have for each. If one is a business
partner, then that end is ignored since this one should not be compared on
both ends.

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Shared Function ValidateCoveragesHaveSameReferenceAndValues(ByVal
RateDefinition As SqlInt32, ByVal CoverageA As SqlInt32, ByVal CoverageB
As SqlInt32) As SqlBoolean

Dim Result As Boolean = True

Using cn As New SqlConnection("context connection=true")

cn.Open()

Using dtCoverageA As New DataTable, dtCoverageB As New DataTable,
dtRateDefinition As New DataTable

' load info on the items

Using cmd As New SqlCommand("select
Coverages.CoverageID,Coverages.[Type],Coverages.AggregationID,AccountsJunCoverages.AccountID,CoverageTypes.LineOfBusinessID,LinesOfBusiness.CarrierID
FROM coverages INNER JOIN AccountsJunCoverages ON Coverages.CoverageID =
AccountsJunCoverages.CoverageID INNER JOIN CoverageTypes ON
CoverageTypes.CoverageTypeID = Coverages.[Type] INNER JOIN LinesOfBusiness
ON LinesOfBusiness.LineOfBusinessID = CoverageTypes.LineOfBusinessID WHERE
Coverages.CoverageID = @CoverageID")

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("@CoverageID", CoverageA)

dtCoverageA.Load(cmd.ExecuteReader)

cmd.Parameters("@CoverageID").Value = CoverageB

dtCoverageB.Load(cmd.ExecuteReader)

End Using

Using cmd As New SqlCommand("SELECT PayerID,PayeeID ,RCVPayer.[Value] as
PayerName,RCVPayee.[Value] as PayeeName FROM RateDefinitions INNER JOIN
RateCriteriaValue AS RCVPayer ON RCVPayer.CriteriaValueID =
RateDefinitions.PayerID INNER JOIN RateCriteriaValue AS RCVPayee ON
RCVPayee.CriteriaValueID = RateDefinitions.PayeeID WHERE
RateDefinitions.ID = @RateDefinitionID", cn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("@RateDefinitionID", RateDefinition)

dtRateDefinition.Load(cmd.ExecuteReader)

End Using

' now that we have the data, compare it, check payer first

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "ACCOUNT"
Then

' check if both coverages have same account

If CInt(dtCoverageA.Rows(0)("AccountID")) <>
CInt(dtCoverageB.Rows(0)("AccountID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "AGGREGATION"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("AggregationID")) <>
CInt(dtCoverageB.Rows(0)("AggregationID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayerName").ToString().ToUpper() = "CARRIER"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("CarrierID")) <>
CInt(dtCoverageB.Rows(0)("CarrierID")) Then

Result = False

End If

End If

' check payee

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "ACCOUNT"
Then

' check if both coverages have same account

If CInt(dtCoverageA.Rows(0)("AccountID")) <>
CInt(dtCoverageB.Rows(0)("AccountID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "AGGREGATION"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("AggregationID")) <>
CInt(dtCoverageB.Rows(0)("AggregationID")) Then

Result = False

End If

End If

If Result = True AndAlso
dtRateDefinition.Rows(0)("PayeeName").ToString().ToUpper() = "CARRIER"
Then

' check if both coverages have same Aggregation

If CInt(dtCoverageA.Rows(0)("CarrierID")) <>
CInt(dtCoverageB.Rows(0)("CarrierID")) Then

Result = False

End If

End If

cn.Close()

End Using

End Using







' Add your code here

Return New SqlBoolean(Result)

End Function

End Class
Click to expand...
 
Top