Friday, March 9, 2012

function on linked server

I have my own function, which I can use:
select * from index_gold_iif('20040121')
When I try do it from linked serwer:
select * from
lewiatan.e_.dbo.index_gold_iif('20040121')
I have massage:
Server: Msg 170, Level 15, State 31, Line 2
Line 4: Incorrect syntax near '('.
As I understand server can't see function from linked server. Is it true or
I have done some error (what)?
Marek
PS. all done on "sa" userMarek
No, server can see functions
select * from openquery
(servername,'select * from
dbo.fn_dates(''20040101'',''20040110'')') --this function returns range
of date between given dates
"Marek Wierzbicki" <marek.wierzbicki.___@.azymut.pl> wrote in message
news:bv35q6$cuh$1@.news2.ipartners.pl...
quote:

> I have my own function, which I can use:
> select * from index_gold_iif('20040121')
> When I try do it from linked serwer:
> select * from
> lewiatan.e_.dbo.index_gold_iif('20040121')
> I have massage:
> Server: Msg 170, Level 15, State 31, Line 2
> Line 4: Incorrect syntax near '('.
> As I understand server can't see function from linked server. Is it true

or
quote:

> I have done some error (what)?
> Marek
> PS. all done on "sa" user
>
>
|||> No, server can see functions
quote:

> select * from openquery
> (servername,'select * from
> dbo.fn_dates(''20040101'',''20040110'')') --this function returns

range
quote:

> of date between given dates

OPENQUERY - I don't know this function up today. What about optimalization
with this function - isn't it so slow as exec('query string")?
Can I use this function to make query like:
select * from va_name_table
for example:
select * from openquery(localhost, 'select * from '+@.table_name)?
Marek|||> No, server can see functions
quote:

> select * from openquery
> (servername,'select * from
> dbo.fn_dates(''20040101'',''20040110'')') --this function returns

range
quote:

> of date between given dates

there something wrongs with openquery - it didn't recognize query string
bild on the fly. So what for call openquery with string to connect to
function, which I need to write parametr of this funcion once forewer?
Marek
PS. example with error:
declare @.dt as datetime
declare @.s as varchar(520)
select @.dt='20040121 12:22:33'
select @.s='select * from e_.dbo.index_gold_iif('+convert(varchar(22), @.dt,
112)+' '+convert(varchar(22), @.dt, 108)+')'
print @.s -- up to this place workong OK
select * from OPENQUERY(lewiatan , @.s) -- this is not working
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@.s'.|||> No, server can see functions
quote:

> select * from openquery
> (servername,'select * from
> dbo.fn_dates(''20040101'',''20040110'')') --this function returns

range
quote:

> of date between given dates

I think its wrong answer beacouse openquery is run (as I read) on linked
server, not local
Marek

No comments:

Post a Comment