"Curtis" wrote:

> I am getting this error when I run my function
>
> System.Data.SqlClient.SqlException: Invalid use of side-effecting or
> time-dependent operator in 'SET ON/OFF' within a function.
> System.Data.SqlClient.SqlException:
>    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
>    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
>    at
> System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
>    at
> Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
>    at
> Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
>    at System.Data.SqlClient.SqlDataReaderSmi.InternalNextResult(Boolean
> ignoreNonFatalMessages)
>    at System.Data.SqlClient.SqlDataReaderSmi.NextResult()
>    at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
>    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
>    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
>    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
>    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
>    at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
>    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
> IDbCommand command, CommandBehavior behavior)
>    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32
> startRecord, Int32 maxRecords, IDbCommand command, CommandB...
>
> I am not even sure if a datatable can be used inside SQL.
>
>     <SqlFunction(isdeterministic:=True, DataAccess:=DataAccessKind.Read)> _
>     Public Shared Function blockedScanMachine(ByVal html As SqlString, ByVal
> scanJobDetailID As SqlInt16, ByVal searchEngineID As SqlInt16) As SqlInt16
>         Dim Value As SqlInt16 = 0
>         Dim Pattern As String
>         Dim dt As New DataTable
>         Using sql As New SqlConnection("context connection=true")
>             Using Adapter As New SqlDataAdapter
>                 Using selectPattern As SqlCommand = New
> SqlCommand("getPatternsBySearchEngineID", sql)
>                     selectPattern.CommandType = CommandType.StoredProcedure
>                     selectPattern.Parameters.AddWithValue("@SearchEngineID",
> searchEngineID)
>
>                     sql.Open()
>                     selectPattern.Connection = sql
>
>                     Adapter.SelectCommand = selectPattern
>
>                     Adapter.Fill(dt)
>                 End Using
>             End Using
>
>         End Using
>
>         If dt.Rows.Count > 0 Then
>             For Each dr As DataRow In dt.Rows
>                 Pattern = CType(dr.Item("Regex"), String)
>                 If Regex.IsMatch(CType(html, String), Pattern,
> RegexOptions.IgnoreCase) Then
>                     Value = 1
>                     Exit For
>                 End If
>             Next
>         End If
>         Return Value
>     End Function