Error executing User Defined function on another server

16 Dec 2005 10:36 AM

Suhas Vengilat

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.

Suhas

16 Dec 2005 11:24 AM

ML

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/

19 Dec 2005 5:18 AM

Suhas Vengilat

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

Show quote

"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/

19 Dec 2005 9:07 AM

ML

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/

19 Dec 2005 9:20 AM

ML

Sorry, I forgot to mention, that OPENQUERY does not accept parameters or
variables. Read more here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp

ML

---
http://milambda.blogspot.com/