how to use sql server udf

[msnews.microsoft.com]

I have created function on sql server 2000 which I would like to use in
FoxPro code but SQLEXEC function returns -1. Is there any restriction for
SQL UDF?

My SQL Function is AS follow:

Function GetClientName (@ClientId Char(10))
Returns char(20)
AS
BEGIN
DECLARE @ClientName as varchar(20)
set @ClientName = (Select LastName from Clients where ClientId =
@ClientId)
RETURN @ClientName
END

In FoxPro I use:

lnRetVal = SQLEXEC(pnHandle,"SELECT dbo.GetClientName('0000000101') AS
ClientName", "sqlClientName") - which returns -1

when I use SELECT dbo.GetClientName('0000000101') AS ClientName in query
analyser it returns last name of the client!

Am I missing anything?

Thanks for looking it.

[JayB]

try leaving off the "as clientname" clause

[Sunny]

Thanks for the quick response but did not work...
"JayB" <Ja...@audiman.net> wrote in message
news:g5j4ev$7f1$1...@registered.motzarella.org...

[Sunny]

Sorry did not check enough... it is permission issue on the function. Gave
permission and it worked...
"msnews.microsoft.com" <sunny...@hotmail.com> wrote in message
news:e1DEh%23r5IH...@TK2MSFTNGP02.phx.gbl...

[Anders Altberg]

And what does that procedure buy you that you don't get from
lnid = '0000000101'
SQLEXCE(pnhandle, [SELECT lastname FROM clients WHERE clientid LIKE ?lnid]
, 'Sqlclientname')

-Anders

"Sunny" <sunny...@hotmail.com> wrote in message
news:eHTNWjs5...@TK2MSFTNGP06.phx.gbl...

[Olaf Doschke]

Hi!

> In FoxPro I use:
>
> lnRetVal = SQLEXEC(pnHandle,"SELECT dbo.GetClientName('0000000101') AS
> ClientName", "sqlClientName") - which returns -1

Take a look at the help on SQLEXEC().
Return Value:

Numeric data type. SQLEXEC( ) returns the number of result sets if there is
more than one.
SQLEXEC( ) returns 0 if it is still executing
and returns 1 when it has finished executing.
SQLEXEC( ) returns -1 if a connection level error occurs.

SQLEXEC will never return the result of the
executed T-SQL or UDF executed. That result
you search for will always be in a result CURSOR,
which SQLEXEC creates.

As you found out yourself the -1 return value was
a sign, that something didn't work, in your case
insufficient access rights.

You can also use AERROR() after -1 is returned from
SQLEXEC() to get the error that occured.

Bye, Olaf.