Lowell (4/15/2014)
well i can think of two things that might explain this:1: you are not a sysadmin, and the object in question is outside of your permissions scope. ie the sysadmin created a table/procedure/function/view named "HighCharacter", so it exists, but you cannot see it due to permissions. this is most likely true, since your LIKE statement on sys.objects did not return the object in question.
2. a little tunnel vision possibility: you want to create a FUNCTION named "HighCharacter", but a Table,View or Procedure already exists by that name.
DROP FUNCTION would fail, becuase it should be DROP PROCEDURE, maybe.
select
schema_name(schema_id) As SchemaName,
name As ObjectName,
type_desc as ObjectType
from sys.objects where name = 'HighCharacter'
Create your Function with a different name, like as HighCharacterV2 and i bet it works just fine.
Hi Lowell,
I think there must be either something I'm missing or there is actually a fault within something in SQL. I'm using the SA account and have confirmed it has sysadmin role. Also your select query returns nothing, no matches found.
My understanding of SA with sysadmin was full access (it doesn't even check permissions), is that true? If so why can't I see it.
I attempted the drop command for every object type I could think of; table,view,function, procedure,trigger, keys, constraint etc -- All returned same error.
Creating the function with a new name works fine as expected. - We can't simply use a new name because the function is called in literally thousands of places. We have managed to implement a workaround into the business critical procedures so they are able to work but its not ideal.
I asked the client to attempted a backup and restore, still not luck. The restored DB still has the issue.
I happy to keep on looking into suggestions, however, my feel this is actually some kind of fault.
Does anyone know, where I can take the problem from here, as in MS support and what is the process?