Re: conflicting object names in sql server 2000
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/09/05
- Next message: Kalen Delaney: "Re: conflicting object names in sql server 2000"
- Previous message: Fabrício de Novaes Kucinskis: "Re: Reporting Services installation crashed SQL Server"
- In reply to: zx6er93: "Re: conflicting object names in sql server 2000"
- Next in thread: zx6er93: "Re: conflicting object names in sql server 2000"
- Reply: zx6er93: "Re: conflicting object names in sql server 2000"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 8 Jan 2005 16:17:06 -0800
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... > 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... >> >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 >> > >> > >> > >> >> >>
- Next message: Kalen Delaney: "Re: conflicting object names in sql server 2000"
- Previous message: Fabrício de Novaes Kucinskis: "Re: Reporting Services installation crashed SQL Server"
- In reply to: zx6er93: "Re: conflicting object names in sql server 2000"
- Next in thread: zx6er93: "Re: conflicting object names in sql server 2000"
- Reply: zx6er93: "Re: conflicting object names in sql server 2000"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
