call a UDF from another server <> Authentication

Hello

I'm trying to call a UDF from another server with the following command:

select i from openquery([10.0.10.240], '[survey].[dbo].[ufnGetAxis_Ana] as i')

error:

[OLE/DB provider returned message: Invalid authorization specification]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Msg 7399, Level 16, State 1, Line 3

OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.

Can someone give me some advice on how to pass username and password to access an object on another server?

Many thanks!

hi, first thing is it the SQL Server or other one?

if it is SQL Server, then it quit easy, you need to create a Linked Server(Direct IP Address will not work) using

sp_addlinkedserver [ @.server= ] 'server' [ , [ @.srvproduct= ] 'product_name' ]

[ , [ @.provider= ] 'provider_name' ]

[ , [ @.datasrc= ] 'data_source' ]

[ , [ @.location= ] 'location' ]

[ , [ @.provstr= ] 'provider_string' ]

[ , [ @.catalog= ] 'catalog' ] then use the openquery asOPENQUERY ( linked_server ,'query' )

linked_server

Is an identifier representing the name of the linked server.

'query'

Is the query string executed in the linked server. The maximum length of the string is 8 KB

now try

select i from openquery(<linked server name>, '[survey].[dbo].[ufnGetAxis_Ana] as i')

Regards,

Thanks.

Gurpreet S. Gill

|||

Many thanks Gurpreet

Are you sure about the syntax?
I get a syntax error when executing select i from openquery(myserver,'survey.dbo.ufnGetAxis_Ana(2,7) as i')

Many thanks!

Worf

|||

hi try this

select i FROM openquery(ha9,'select pubs.dbo.myFunction() as i')

this is the command, where 'ha9' is linked server , at the remote end 'pubs' is the database name, 'dbo' is owner and 'myFunction' is the name of the function, in your case it should be

select i from openquery(myserver,'select survey.dbo.ufnGetAxis_Ana(2,7) as i')

NOTE: if your Remote Serever is SQL Server, then , name of the server is the Linked server name, also need to set the login & password.

Regards,

Thanks.

Gurpreet S. Gill

|||

It works Gurpreet!!

Many many thanks!!!

Worf