Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
cannot run any scalar function in SQL Server 2008 R2

Answered cannot run any scalar function in SQL Server 2008 R2

  • Thursday, July 12, 2012 3:03 AM
     
     
    Sign In to Vote
    0

    Hi, I upgraded a SQL Server 2005 server to SQL Server 2008 R2. The version of the new server is Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86). I have just realized that I cannot execute any scalar function, in any database, in the new server. I mean, I can create the function and it is displayed under the correct folder in the Database but every time I try to execute any function I get the message:

    Cannot find either column "dbo" or the user-defined function or aggregate "<<my function>>", or the name is ambiguous.

    I am totally sure that the schema under which the function was created is dbo and I am calling the function using the schema prefix, for example dbo.myfunction()

    This is occurring only with new functions created under SQL 2008 R2. The old scalar functions migrated from SQL 2005 works fine.

    I would appreciate any help from you.


    Xavier Villafuerte - http://preempalverec.blogspot.com

     

All Replies

  • Thursday, July 12, 2012 3:09 AM
    Moderator
     
     
    Sign In to Vote
    0
    Can you post any example of such function? 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, July 12, 2012 3:12 AM
     
     
    Sign In to Vote
    0

    Sure. The problem is occurring with any function. I initially though the problem was related with the code but I tested creating a simple function like the one below with the same results.

    CREATE FUNCTION [dbo].[fAddition](@a INT, @b INT)

    RETURNS INT
    AS
    BEGIN;
        RETURN @a + @b;
    END;

    Xavier Villafuerte - http://preempalverec.blogspot.com

     
  • Thursday, July 12, 2012 3:25 AM
     
     
    Sign In to Vote
    0

    I have no issue to run this

    use [test]
    go

    CREATE FUNCTION [dbo].[fAddition](@a INT, @b INT)
    RETURNS INT
    AS
    BEGIN;
        RETURN @a + @b;
    END;

    use [test]
    go

    select dbo.fAddition(3,5)
    go

    Many Thanks & Best Regards, Hua Min

     
  • Thursday, July 12, 2012 3:32 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hmm, may be the problem is in permissions and that account doesn't have rights to execute the function? Although the message should have been different.

    Also, just in case - are you sure that the name you use in the call match exactly the name of the function? May be you're using case-sensitive collation of SQL Server?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


     
  • Thursday, July 12, 2012 3:33 AM
     
     
    Sign In to Vote
    0

    Thanks for your answer. What you mention should be the default behavior but I really don´t know why my server is not allowing to run new functions. The function is there, I can see it under the Scalar functions folder but when I try to include it in any SQL statement, simply it is hidden for any reason. The intelli-type is not showing it in the available functions list while I type the name in the query window.

    Xavier Villafuerte - http://preempalverec.blogspot.com

     
  • Thursday, July 12, 2012 3:52 AM
     
     
    Sign In to Vote
    0

    I got it!. I really don´t know the reason but after closing the Management Studio and open it again, I can see the functions. Weird.

    Xavier Villafuerte - http://preempalverec.blogspot.com

     
  • Thursday, July 12, 2012 3:56 AM
    Moderator
     
     
    Sign In to Vote
    0

    Are you able to also execute them?

    Also, perhaps you were in the wrong database when you tried running your scripts (e.g. created functions in one DB, but tried to execute from another).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, July 12, 2012 4:21 AM
     
     
    Sign In to Vote
    0
    I will suggest that you check if you are running your function under the correct database? you might try to get the function under Master while your function is under another user database or vice versa

    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

     
  • Thursday, July 12, 2012 4:26 AM
     
     Answered
    Sign In to Vote
    0

    I got it!. I really don´t know the reason but after closing the Management Studio and open it again, I can see the functions. Weird.

    Xavier Villafuerte - http://preempalverec.blogspot.com


    this has something to do with your local intellisense cache. you don't need to restart SSMS, you can do by refresh it as below:

    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!