Error executing User Defined function on another server

Hi All,
Facing a problem while executing a user defined function (UDF) which resides
on another sql server ( we have access on this sql server to execute the
UDF). This UDF accepts a varchar parameter with max length 8. When we
execute the function as
Select * from
SERVERNAME.DBName.dbo. fn_GetConsulteesDataForReport('00118179'
) it throws an
error as given below.
Server: Msg 170, Level 15, State 31, Line 1
Line 2: Incorrect syntax near '('.
Please let me know what is the issue in my SQL statement and how we can
resolve this.
Thanks in advance.
SuhasThere's a workaround for this hm... feature. Use the OPENQUERY function:
select *
from openquery(SERVERNAME, '
select *
from
DBName.dbo. fn_GetConsulteesDataForReport('00118179'
)
')
ML
http://milambda.blogspot.com/|||Hi ML,
Thank you for giving this suggestion. But please let me know whether open
query does accept variable as an input to the function ? I mean, can I
execute the same as given below.
declare @.empid varchar(8)
select @.empid = '00118179'
select *
from openquery(SERVERNAME, '
select *
from
DBName.dbo.fn_GetConsulteesDataForReport(@.empid)
')
Becuase we need to dynamically change this employee ID.
Thank you.
Suhas
"ML" wrote:

> There's a workaround for this hm... feature. Use the OPENQUERY function:
> select *
> from openquery(SERVERNAME, '
> select *
> from
> DBName.dbo. fn_GetConsulteesDataForReport('00118179'
)
> ')
>
> ML
> --
> http://milambda.blogspot.com/|||For actual production use I'd suggest designing a local UDF, that references
tables in the database on the linked server. This way you can call the
function directly, and still access the data remotely.
ML
http://milambda.blogspot.com/|||Sorry, I forgot to mention, that OPENQUERY does not accept parameters or
variables. Read more here:
http://msdn.microsoft.com/library/d...br />
5xix.asp
ML
http://milambda.blogspot.com/

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 Creating CLR UDF

Hi:

I am trying to create a CLR UDF in SQL 2005 and cosnistently run into the following error. What am I doing wrong?. Please correct me and show me the right way of doing this.

Msg 6551, Level 16, State 2, Procedure EmailSplitter, Line 3

CREATE FUNCTION for "EmailSplitter" failed because T-SQL and CLR types for return value do not match.

Here is what I am trying to achieve. Split a Email field in the database. For that I am trying to return an array using C# and then trying to call the UDF for the C#.

--1).CLR Code. (EmailSpitter.cs)

using System;
using System.Collections.Generic;
using System.Text;

namespace SQLTools
{
public class EmailSplitter
{
public static string[] Parse(string data)
{
string[] columnData;
string[] separators = new string[1];
separators[0] = " ";

if (data != null)
{
columnData = data.Split(separators, StringSplitOptions.None);
return columnData;
}
return null;
}
}
}

--2). Assembly code.

CREATE Assembly SQLArrayBuilder

FROM 'E:\CLR\EmailSplitter\bin\Debug\EmailSplitter.dll'

WITH PERMISSION_SET=SAFE

Select * from sys.assemblies.

--3). Create the function.

CREATE Function dbo.EmailSplitter

(@.EmailString NVARCHAR(4000))

RETURNS VARCHAR(4000)

AS

EXTERNAL NAME SQLArrayBuilder.[SQLTools.EmailSplitter].Parse

Run into the error:

Msg 6551, Level 16, State 2, Procedure EmailSplitter, Line 3

CREATE FUNCTION for "EmailSplitter" failed because T-SQL and CLR types for return value do not match.

Please help me.

Thank you very much.

AK

Your CREATE FUNCTION returns a string, but your CLR function returns an array of strings, that is why you are getting an error. If you want to return an array of strings you will have to create a CLR function that returns an IEnumerable object and define a table valued function in T-SQL. Look up CLR table valued functions in the BOL for the details.

Dan