SQL function not created in the correct SQL database

Question

I have successfully created the following function on the SQL database.

CREATE FUNCTION [dbo].[maximum_time]
(
    @TotalLengthofRecords REAL
)
RETURNS FLOAT
AS
BEGIN
    RETURN
        CASE 
            WHEN @TotalLengthofRecords > 3600 THEN 3600
            ELSE @TotalLengthofRecords
        END 
END

Reviewing the objects in Microsoft SQL Server Management Studio, it seems that the user-defined function has been successfully created in the System Database -> master. However, the function is not appearing within the project database.

Whenever I try to call the function using the following code:

SELECT 
    A.*,
    [dbo].[maximum_time]([TotalLengthofRecords_MAX])AS [TotalLengthofRecords_MAX]
    INTO [dbo].[BunkerAISFinal_V7]
    FROM [dbo].[BunkerAISFinal_V6] AS A;

The system will throw up the following error:

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

How do I solve this problem? Is there a way of creating the function in a particular database name?

Try looking [here](http://stackoverflow.com/questions/2091830/cannot-find-either-column-dbo-or-the-user-defined-function-or-aggregate-dbo-s). Same problem. May help you. — Adam Medina, May 20 '17 at 01:41
I have looked at the post. It is a different problem because I believe my function should be a `Scalar-valued function`. — Cloud, May 20 '17 at 01:45
*it seems that the user-defined function has been successfully created in the System Database -> master*. That may be, but it was created in whatever database was being used at the time, under the account of the DB owner (dbo). Perhaps you should understand the concept of `use ` and what `[dbo]` means before just randomly inserting it into your SQL? — Ken White, May 20 '17 at 01:51
Hi Ken. I understand that concept. However, the code did not work with the replacement of the `CREATE FUNCTION [dbname].[dbo].[maximum_time]`. How should I correct it? — Cloud, May 20 '17 at 01:56
At the top of your function type USE [your database] GO then continue with your function code — S3S, May 20 '17 at 02:22

score 1 · Answer 1 · answered May 20 '17 at 03:44

1

Based on the comments that I have received, the problem was caused by not defining the database name that the function will be created in. The first two lines in the code below were added to solve the problem.

USE [database_name]
GO

CREATE FUNCTION [dbo].[maximum_time]
(
    @TotalLengthofRecords REAL
)
RETURNS FLOAT
AS
BEGIN
    RETURN
        CASE 
            WHEN @TotalLengthofRecords > 3600 THEN 3600
            ELSE @TotalLengthofRecords
        END 
END

answered May 20 '17 at 03:44

Cloud

399
4
13

You can also set a default database for the current user to avoid this – Norbert Forgacs May 20 '17 at 08:16
@berthos User defined function in master database can't be directly referenced. – Rohit Kumar May 20 '17 at 14:14

Rohit Kumar · Answer 2 · 2017-05-25T12:25:56.840

     SELECT 
A.*,
Master.[dbo].[maximum_time]([TotalLengthofRecords_MAX])AS [TotalLengthofRecords_MAX]
INTO [dbo].[BunkerAISFinal_V7]
FROM [dbo].[BunkerAISFinal_V6] AS A;

1. To reference an object the user must follow this

[ServerName/ServerIp].[DatabaseName].[schemaname].[ObjectName].

For different server there must a linked server applied.

2. If the server for the objects being referenced are same and objects are in another database and have a different schema you can use.

[DatabaseName].[schemaname].[ObjectName]

3 If the database are same and different schema use [schemaname].[ObjectName]

4 Else

Directly refer the object

SQL function not created in the correct SQL database

2 Answers2