Error encountered while executing a UDF

Hi,

I get the following error while trying to execute a UDF.

Msg 6522, Level 16, State 1, Line 1

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

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

System.NullReferenceException:

at SalesCentral_Database.UserDefinedFunctions.udfTest()

My function is defined as follows:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _

Public Shared Function udfTest() As SqlString

SqlContext.Pipe.Send(System.DateTime.Now().ToString())

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

conn.Open()

Dim cmd As New SqlCommand("SELECT MAX(ACCTPERIOD) FROM TBL_OPPORTUNITY_HIS", conn)

Return New SqlString(cmd.ExecuteScalar().ToString())

End Function

End Class

SQL Server

CREATE ASSEMBLY SalesCentral_Database from '\\isew5l6c\tranf\SFS_Sales\SalesCentral_Database.dll' WITH PERMISSION_SET = SAFE

CREATE FUNCTION test() RETURNS NVARCHAR(10) AS EXTERNAL NAME [SalesCentral_Database].[SalesCentral_Database.UserDefinedFunctions].[udfTest]

SELECT dbo.test()

- Here I get the error mentioned above. Can anyone please tell me what am I missing.

Thanks.

Hi,

This is the same error as in this thread. You should remove the "SqlContext.Pipe.Send..." line from your code. I'm curious what you intent is with that linethe pipe is really to return results from within a stored procedure.

Cheers,
-Isaac

|||Thanks for bringing that to my notice. Sorry it has slipped out of my mind :)

Error during query execution

Hi,
Does anyone know how to solve the following error during
query execution?
Using 'dbghelp.dll' version '4.0.5'
*Stack Dump being sent to e:\sql_data\Microsoft SQL
Server\MSSQL$DEVA_AP04_SQL\log\SQL00048.dmp
*
****************************************
*******************
********************
*
* BEGIN STACK DUMP:
* 03/11/04 16:40:34 spid 57
*
* Exception Address = 00418209 (FastDBCSToUnicode +
000000B6 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000015
* Input Buffer 2824 bytes -
* SELECT event_time as [Alert Time],
combo_orders.firm_name AS [Firm], dbo
* .GetTradingSymbol(underlying_id) AS [Underlying],
combo_orders.order_pri
* ce AS [Price], combo_orders.original_quantity AS
[Original Quantity], tr
* aded_quantity AS [Traded Quantity],
combo_orders.current_quantity AS [Cu
* rrrent Quantity], order_begin_time as [Time of Entry],
dbo.GetOrderStatu
* sText(combo_orders.current_order_status) AS [Current
Status], dbo.GetCom
* bexAlertText(reason_code) as [Alert Type],
dbo.GetCombexAlertAcknowledge
* dText(acknowledged) as [Acknowledged], login_id as [ISE
Analyst], combex
* _alerts.logb_order_number as [LogB Order Number],
item_id as [Item ID],
* combex_alert_id as [Combex Alert ID] FROM combex_alerts
WITH (NOLOCK), c
* ombo_orders WITH (NOLOCK) WHERE
combex_alerts.logb_order_number = combo_
* orders.logb_order_number AND reason_code IN
(13,7,2,3,9,11,12,10,8,4,6,1
* ,14,5,15,22,20,18,21,19,16,17) AND display_on_off = 1
AND combex_alerts.
* market_date ='3/11/2004 12:00:00 AM' AND
combo_orders.logb_order_number
* <> 999 AND (combo_orders.sequence_number = (SELECT MAX
(sequence_number)
* FROM combo_orders co2 WITH (NOLOCK) WHERE
co2.logb_order_number = combo
* _orders.logb_order_number AND co2.logb_order_number <>
999 AND co2.mar
* ket_date = combo_orders.market_date AND
co2.market_date = '3/11/2004 12
* :00:00 AM' AND co2.underlying_id =
combo_orders.underlying_id)) ORDER B
* Y [Acknowledged], [Combex Alert ID] DESC
.
.
.
Error: 0, Severity: 19, State: 0
language_exec: Process 57 generated an access violation.
SQL Server is terminating this process..
Regards,
JennyJenny,
Access violations are more often than not SQL Server bugs. The first
thing to do is make sure you are up-to-date on service packs. What is
the result you get when you run select @.@.version? The one solution I
can find in newsgroups to an access violation referring to
FastDBCSToUnicode was solved by the installation of SQL Server 2000
service pack 3.
SK
Jenny wrote:

>Hi,
>Does anyone know how to solve the following error during
>query execution?
>Using 'dbghelp.dll' version '4.0.5'
>*Stack Dump being sent to e:\sql_data\Microsoft SQL
>Server\MSSQL$DEVA_AP04_SQL\log\SQL00048.dmp
>*
> ****************************************
*******************
>********************
>*
>* BEGIN STACK DUMP:
>* 03/11/04 16:40:34 spid 57
>*
>* Exception Address = 00418209 (FastDBCSToUnicode +
>000000B6 Line 0+00000000)
>* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
>* Access Violation occurred reading address 00000015
>* Input Buffer 2824 bytes -
>* SELECT event_time as [Alert Time],
>combo_orders.firm_name AS [Firm], dbo
>* .GetTradingSymbol(underlying_id) AS [Underlying],
>combo_orders.order_pri
>* ce AS [Price], combo_orders.original_quantity AS
>[Original Quantity], tr
>* aded_quantity AS [Traded Quantity],
>combo_orders.current_quantity AS [Cu
>* rrrent Quantity], order_begin_time as [Time of Entry],
>dbo.GetOrderStatu
>* sText(combo_orders.current_order_status) AS [Current
>Status], dbo.GetCom
>* bexAlertText(reason_code) as [Alert Type],
>dbo.GetCombexAlertAcknowledge
>* dText(acknowledged) as [Acknowledged], login_id as [ISE
>Analyst], combex
>* _alerts.logb_order_number as [LogB Order Number],
>item_id as [Item ID],
>* combex_alert_id as [Combex Alert ID] FROM combex_alerts
>WITH (NOLOCK), c
>* ombo_orders WITH (NOLOCK) WHERE
>combex_alerts.logb_order_number = combo_
>* orders.logb_order_number AND reason_code IN
>(13,7,2,3,9,11,12,10,8,4,6,1
>* ,14,5,15,22,20,18,21,19,16,17) AND display_on_off = 1
>AND combex_alerts.
>* market_date ='3/11/2004 12:00:00 AM' AND
>combo_orders.logb_order_number
>* <> 999 AND (combo_orders.sequence_number = (SELECT MAX
>(sequence_number)
>* FROM combo_orders co2 WITH (NOLOCK) WHERE
>co2.logb_order_number = combo
>* _orders.logb_order_number AND co2.logb_order_number <>
>999 AND co2.mar
>* ket_date = combo_orders.market_date AND
>co2.market_date = '3/11/2004 12
>* :00:00 AM' AND co2.underlying_id =
>combo_orders.underlying_id)) ORDER B
>* Y [Acknowledged], [Combex Alert ID] DESC
>.
>.
>.
>Error: 0, Severity: 19, State: 0
>language_exec: Process 57 generated an access violation.
>SQL Server is terminating this process..
>
>Regards,
>Jenny
>
>