conflicting object names in sql server 2000

I can't seem to solve this problem. I hope someone can help. I have a table
which is owned by dbo called xxx. I also have a user define function also
called xxx but owned by a different user. Everything works fine on the local
database. From query analyzer if I type in select * from xxx everything
works fine on the local database.
Here's the problem. When I use dts to move this over to another server, and
then try select * from xxx it returns the error "Server: Msg 208, Level 16,
State 3, Line 1
Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
Unfortunately because of the situation I can't go back in and change the
user function name to something else, or change all the code to prefix the
table with dbo.xxx.
The only clue I've come up is that on the local machine when I do an sp_help
it shows "xxx dbouser table2004-02-16 19:14:17.280"
but when I do an sp_help on the other server it shows "xxxmichaeltable
function2005-01-08 13:21:57.500"
that seems to explain my problem, but my question is how/what can I update
so that the table gets the "higher priority" so when I do the select it looks
at the table and not the function when just using xxx and not dbo.xxx or
michael.xxx?
Hope that makes sense. I'm in a real bind with this and would appreciate
any help. Thanks.
John
Hi
This is a bit confusing. Are you trying to access the table or the function?
What user are YOU when you try to access the object?
Here are a couple of points that may help:
if you say
SELECT * from xxxx
you are selecting from a table. The only question is, which table? SQL
Server will first try to select from a table xxx that YOU own, if there is
none, it will see if there is one owned by dbo.
If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
dbo, or anybody besides Michael, wants to access the table owned by
Michael, she must specify the owner:
SELECT * from michael.xxx
If you are selecting from a table valued function, you MUST specify an
owner, PLUS you must specify a parameter list, even if there are no
parameters.
So,
SELECT * from xxx()
is incorrect syntax because you didn't specify the owner
SELECT * from dbo.xxx()
will access the xxx FUNCTION owned by dbo
and
SELECT * from michael.xxx()
will access the xxx FUNCTION owned by michael
I hope this helps. If not, please clarify
What object you are trying to access, and if it's a function, what are the
parameters
Who owns that object
What user you are
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>I can't seem to solve this problem. I hope someone can help. I have a
>table
> which is owned by dbo called xxx. I also have a user define function also
> called xxx but owned by a different user. Everything works fine on the
> local
> database. From query analyzer if I type in select * from xxx everything
> works fine on the local database.
> Here's the problem. When I use dts to move this over to another server,
> and
> then try select * from xxx it returns the error "Server: Msg 208, Level
> 16,
> State 3, Line 1
> Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
> Unfortunately because of the situation I can't go back in and change the
> user function name to something else, or change all the code to prefix the
> table with dbo.xxx.
> The only clue I've come up is that on the local machine when I do an
> sp_help
> it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> but when I do an sp_help on the other server it shows "xxx michael table
> function 2005-01-08 13:21:57.500"
> that seems to explain my problem, but my question is how/what can I update
> so that the table gets the "higher priority" so when I do the select it
> looks
> at the table and not the function when just using xxx and not dbo.xxx or
> michael.xxx?
> Hope that makes sense. I'm in a real bind with this and would appreciate
> any help. Thanks.
> John
>
>
|||Hi,
Yes, this is very confusing, unfortunately this is what I have to work
with. Thanks for responding though.
the connection string specifies "michael" as the user when connecting to
the database. On the local database server when it does the select * from
xxx it is expecting to see the table which is owned by dbo, and it does
produce these results. When it looks at the function, it does it with
michael.xxx Note that michael is the owner of the database. I have
attempted to detach the database and reattach it on another server and it
works like that as well. However the server I need to get these tables and
functions to I do not have access to reattach the database, only to move the
database over via dts. The only clue I can find is the difference in the
sp_help results which I provided earlier.
Thanks again.
"Kalen Delaney" wrote:

> Hi
> This is a bit confusing. Are you trying to access the table or the function?
> What user are YOU when you try to access the object?
> Here are a couple of points that may help:
> if you say
> SELECT * from xxxx
> you are selecting from a table. The only question is, which table? SQL
> Server will first try to select from a table xxx that YOU own, if there is
> none, it will see if there is one owned by dbo.
> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
> dbo, or anybody besides Michael, wants to access the table owned by
> Michael, she must specify the owner:
> SELECT * from michael.xxx
> If you are selecting from a table valued function, you MUST specify an
> owner, PLUS you must specify a parameter list, even if there are no
> parameters.
> So,
> SELECT * from xxx()
> is incorrect syntax because you didn't specify the owner
> SELECT * from dbo.xxx()
> will access the xxx FUNCTION owned by dbo
> and
> SELECT * from michael.xxx()
> will access the xxx FUNCTION owned by michael
>
> I hope this helps. If not, please clarify
> What object you are trying to access, and if it's a function, what are the
> parameters
> Who owns that object
> What user you are
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>
>
|||A login name of michael in the connection string does not mean the user name
is also michael. A login name maps to a user name when the login is given
access to a particular database. A login michael could be a user named
michael in one database, a user named clerk in another, and a user named
user1 in a third database. If michael is the owner of a database, his user
name will be dbo in that database.
My guess is that the login michael is dbo on one database and not dbo on
another.
How exactly are you 'looking' at the function michael.xxx and how do you
know you are looking at the results of the function and not the table xxx.
Can you post the create table statements, the create function statement, and
the exact commands you are using for access. Also, when you are in a
database, you can execute SELECT USER_NAME() to find out what your user name
is in that database.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
|||Oops, I did make one mistake. You do not have to specify an owner name when
selecting from a table valued function, but you DO need to include the param
list, even if there are no params.
When you say SELECT * from xxx you can only be selecting from a table or a
view, NOT from a function. The owner of the object will depend on the user
name your login name is mapped to.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
|||Here a suggestion: try to add another user (with the role db_owner set?) and
use this account to connect. As this new account will have a different user
name, SQL-Server shouldn't look anymore for any object with michael as the
owner.
S. L.
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
|||Thank you. It appears in the database that it works in the user is the dbo,
and in the other database the user isn't. I believe that will solve my
issue.
"Kalen Delaney" wrote:

> A login name of michael in the connection string does not mean the user name
> is also michael. A login name maps to a user name when the login is given
> access to a particular database. A login michael could be a user named
> michael in one database, a user named clerk in another, and a user named
> user1 in a third database. If michael is the owner of a database, his user
> name will be dbo in that database.
> My guess is that the login michael is dbo on one database and not dbo on
> another.
> How exactly are you 'looking' at the function michael.xxx and how do you
> know you are looking at the results of the function and not the table xxx.
> Can you post the create table statements, the create function statement, and
> the exact commands you are using for access. Also, when you are in a
> database, you can execute SELECT USER_NAME() to find out what your user name
> is in that database.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
>
>