Function error

[thamms]

SELECT dbo.myFunction(GETDATE())

I can't figure this out. If I run the above statement in Database A, it works fine. But if try to run it from Database B on the same server, I get an error:

Msg 4121, Level 16, State 1, Line 1

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

"myFunction" exists in both Database A & Database B, so what's the problem?

Thanks

 

[sweth]

CHECK MYFUNCTION IS CREATED WITH DBO SCHEMA IN DBTABASE B.

 

[thamms]

I'm not sure I understand what you're saying.

The default schema for all databases is "dbo"

 

[ca8msm]

The default schema may be dbo, but did you create it under this schema in database B?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

It could be that the function was mistakenly created using a different schema. It's easy enough to check.

Select Routine_Schema, Routine_Name
From   Information_Schema.Routines
Where  Routine_Name = 'myFunction'

Alternatively, you could get a list of stored procedure and user defined functions that are NOT owned by the dbo schema, like this...

Select Routine_Schema, Routine_Name
From   Information_Schema.Routines
Where  Routine_Schema <> 'dbo'

Like sweth suggests... the most likely cause of your problem is a schema ownership issue. I encourage you to run the 2 queries I show above. At the least, this would rule out schema ownership issues.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

Also, if you're not sure, run this to show the schema it was created under:

[COLOR=blue]select[/color] r.specific_schema
[COLOR=blue]from[/color] information_schema.routines r
[COLOR=blue]where[/color] r.specific_name = [COLOR=red]'myFunction'[/color]

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

Another alternative to check is that the name is slightly misspelled on database b.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

or is this a table valued function? try running it without the dbo. in front. Also a Table valued function would have selct * from and then the fuction name.

"NOTHING is more important in a database than integrity." ESquared

 

[thamms]

I'm sorry, this is TOO weird. And I'm not making this up.

First of all, I ruled out schema issues by running the 2 queries above. Not that I thought this was the issue, because I don't use schemas.

I even RENAMED the function to something unique in Database B. When I run my function:

SELECT dbo.LastMarketCloseDate_me (GETDATE()) AS BusinessDate

I get this error:

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.IsBusinessDate", or the name is ambiguous.

THE ERROR MESSAGE IS NOT EVEN REFERRING TO THE FUNCTION NAME!! HOW CAN THIS BE? Something is screwy here.

As I said, this is ONLY a problem in Database B!!

Thanks

 

[gmmastros]

It's good to rule things out.

Now, my best guess is that the function is referring to another object (table, column, function, view, etc...) that does not exist in database B. We could probably help a little better if you paste in the source code for the function.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom