[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15003
  • Last Modified:

SqlException 'Function name' is not a recognized built-in function name.

MS SQL 2005

This is a function within a stored procefure

      SET @FunctionText = N'SELECT @Achieved = ' + @Function + N'("' + cast(@Customer_ID as varchar(5)) + N'", "' + cast(@date as varchar(12)) + N'")'  ------
      EXEC sp_executesql @FunctionText, N'@Achieved int output ',@Achieved out

generates error

SqlException (0x80131904): 'F_SELECT_FITPOINT_ADHERENCE' is not a recognized built-in function name.

when I make it a 2-partname, I get the error

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Function_Name", or the name is ambiguous.
0
JCM0165
Asked:
JCM0165
  • 6
  • 4
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
whenever you calls a User defined function, you need to mention the owner name  also, for example, if the owner of your function is 'dbo', then the function call shoulld be

dbo.F_SELECT_FITPOINT_ADHERENCE

0
 
JCM0165Author Commented:
anee,

when I make it a 2-partname, I get the error

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Function_Name", or the name is ambiguous.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>I make it a 2-partname, I get the error
How did you do that ? did you modify the Query ,if so paste it here ?

aneesh
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JCM0165Author Commented:
Annesh, here

SET @Function =
      (
      SELECT   TOP (1) Function_
      FROM         FitPoint_Categories
      WHERE     (Order_ = @Order) AND (Deleted <> 1) AND FPQ_ID = @FPQ_ID
      ORDER BY FPC_ID DESC
      )

      SET @FunctionText = N'SELECT @Achieved = ' + @Function + N'("' + cast(@Customer_ID as varchar(5)) + N'", "' + cast(@date as varchar(12)) + N'")'  ------
      EXEC sp_executesql @FunctionText, N'@Achieved int output ',@Achieved out
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

I haven't seen where you added that 'dbo', Okay, i am going to add that owner of that function

SELECT   TOP 1 @Function = Function_
FROM         FitPoint_Categories
WHERE     (Order_ = @Order) AND (Deleted <> 1) AND FPQ_ID = @FPQ_ID
ORDER BY FPC_ID DESC

SELECT @Function = ROUTINE_SCHEMA+'.' +@Function
 FROM information_Schema.Routines
WHERE ROUTINE_NAME  = @FunctionName

SET @FunctionText = N'SELECT @Achieved = dbo.' + @Function + N'("' + cast(@Customer_ID as varchar(5)) + N'", "' + cast(@date as varchar(12)) + N'")'  ------
EXEC sp_executesql @FunctionText, N'@Achieved int output ',@Achieved out
0
 
JCM0165Author Commented:


got
Must declare the scalar variable "@FunctionName".
0
 
JCM0165Author Commented:
used @Function instead of @FunctionName

got
Cannot find either column "fasterfitness" or the user-defined function or aggregate "fasterfitness.F_SELECT_FITPOINT_ADHERENCE", or the name is ambiguous.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ooops,  change '@FunctionName'  to '@Function'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT   TOP 1 @Function = Function_
FROM         FitPoint_Categories
WHERE     (Order_ = @Order) AND (Deleted <> 1) AND FPQ_ID = @FPQ_ID
ORDER BY FPC_ID DESC

SELECT @Function = ROUTINE_SCHEMA+'.' +@Function
 FROM information_Schema.Routines
WHERE ROUTINE_NAME  = @FunctionName

SET @FunctionText = N'SELECT @Achieved = dbo.' + @Function + N'("' + cast(@Customer_ID as varchar(5)) + N'", "' + cast(@date as varchar(12)) + N'")'  ------
print @functionName
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sorry


SELECT   TOP 1 @Function = Function_
FROM         FitPoint_Categories
WHERE     (Order_ = @Order) AND (Deleted <> 1) AND FPQ_ID = @FPQ_ID
ORDER BY FPC_ID DESC

SELECT @Function = ROUTINE_SCHEMA+'.' +@Function
 FROM information_Schema.Routines
WHERE ROUTINE_NAME  = @FunctionName

SET @FunctionText = N'SELECT @Achieved = dbo.' + @Function + N'("' + cast(@Customer_ID as varchar(5)) + N'", "' + cast(@date as varchar(12)) + N'")'  ------
print @FunctionText
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now