user-defined function & remote server

Posted on 2005-05-16
Medium Priority
Last Modified: 2010-03-19
Hi all SQL SERVER Expert,

    I've made a user-defined function that works fine on the server, but if I want to use it from a remote server it does not work and I get this error.

Example :

 On the server that have the function this works good
     select * from dbo.func_Serveur_Alias()

 On another server that use a link server I tried this
     select * from suivi.applications.dbo.func_Serveur_Alias()

 And I get this error

Server : Msg 170, level 15, status 31, Line 1
Line 2: Incorrect syntax near '('.

Question by:deschenesp
  • 3
LVL 28

Expert Comment

ID: 14011288
Try creating a view on your server that calls your user-defined function:

CREATE VIEW Serveur_Alias_View
SELECT * FROM dbo.func_Serveur_Alias()

then from your remote server, use this view.

SELECT * FROM suivi.applications.dbo.Serveur_Alias_View

Author Comment

ID: 14011361
I Know that using a view with a remote server is posible, but this is not the way I want to do it. I want to be able to use my functions throught remote server. The reason is that I have more than 200 functions and dont want to recreate all of them in views.

How can I use my functions with a remote server ?
LVL 28

Expert Comment

ID: 14011456
As far as I know, it is not possible to call a user-defined function from a remote server.
LVL 28

Accepted Solution

rafrancisco earned 1000 total points
ID: 14011514
Alternatively, you can use the sp_executesql sp but I don't think this will work for you:

EXEC suivi.applications.dbo.sp_executesql N'SELECT * FROM dbo.func_Serveur_Alias()'

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question