user-defined function & remote server

Posted on 2005-05-16
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
    LVL 28

    Expert Comment

    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

    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

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

    Accepted Solution

    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()'

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now