Linked Server - User Defined Functions

Question

Post reply

Linked Server - User Defined Functions

Kevin Mitchell

Old Hand

Points: 304
More actions
September 22, 2006 at 4:20 am

#114956

Hi,
Is it possible to reference a user defined function via a linked server in the following way?
e.g
Select [REMOTE_SERVER_NAME].Database.dbo.userdefinedfunction(@Param1)

If not is there another way of achieving this required functionality?
Thanks in anticipation.
Rgds.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Why not just try?

_____________
Code for TallyGenerator

Kevin Mitchell Old Hand Points: 304 More actions · Answer 2

I've have done and it doesn't work. I get the error :

The object name 'REMOTE_SERVER_NAME.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Sorry, my original question was not clear.

Rgds

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Is it scalar or table function?

_____________
Code for TallyGenerator

Kevin Mitchell Old Hand Points: 304 More actions · Answer 4

Scalar - will be returning same single value given the same input.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

For this you need to use linked server.

Something like this:

sp_addlinkedserver [REMOTE_SERVER_NAME]

SELECT ReturnValue FROM OPENQUERY ( [REMOTE_SERVER_NAME] , 'Select [REMOTE_SERVER_NAME].Database.dbo.userdefinedfunction(@Param1) as ReturnValue' )

But actually calling UDF on remote server is not recommended approach.

_____________
Code for TallyGenerator

Kevin Mitchell Old Hand Points: 304 More actions · Answer 6

Many thanks for the reply.

Why is this not the recommended approach given that business logic is stored in functions on the remote server, and we don't want to maintain udf's in 2 locations that have the same functionality?

Thanks.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Do you call functions or methods from another applications?

Another server is absolutely isolated instance. You can request data from it but you cannot rely on its functionality.

It may isolated, may be even down. Did you think about it?

If you want to use the same functionality use the same server.

If you need separate server then duplicate functionality.

If you gonna run your application in 2 different locations you gonna make 2 identical installations. Aren't you? Do you care about duplicated functionality? Or you gonna call functions from 1st instance on 2nd one?

What makes you think it's OK for SQL Server?

_____________
Code for TallyGenerator

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 8

A possible business requirement

I could have application where I setup client data database and metadata database on two different servers. In such cases clients database might need some business logic from metadata database in the form TVF (with TVF i could join client tables with this function).

I could use open query, but don't think its good practice to have it in code and not secured option to go with.

Do we have any other way than open query?

Regards
Shrikant Kulkarni

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Again, if you have a business requirement to have the same application in 2 or more different locations - what would you do?

_____________
Code for TallyGenerator

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 10

I agree with what you wants to suggest. My concern was when one application supports 2 or more databases say 1.Metadata DB: which store applications metadata 2.Client DB: which stores user specific data 3.Report DB: which stores users reports related data. since my client data is huge I would prefer to store it in different server.

When client data needs some business logic from metadata database, do I suppose to duplicate that business logic on the client database server ?

or do you think putting client database on different server is wrong design ?

Regards
Shrikant Kulkarni

Sergiy SSC Guru Points: 110208 More actions · Answer 11

There is no right or wrong. It depends.

But processing data using remote server functionality is definitely wrong.

Imagine, to apply some function from remote server you need pump all data to that server, process it there and than pump it back.

I don't recon it's optimal design.

Do you?

_____________
Code for TallyGenerator

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 12

Yes you are right !! It depends and I am agree with processing data from remote server is bad design. If the performace is only creteria for putting database in different server, I could manage it with high end processors, and supporting hardware.

Thanks a Lot

Regards
Shrikant Kulkarni