Re: conflicting object names in sql server 2000
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/09/05
- Next message: Sylvain Lafontaine: "Re: conflicting object names in sql server 2000"
- Previous message: Kalen Delaney: "Re: conflicting object names in sql server 2000"
- In reply to: zx6er93: "Re: conflicting object names in sql server 2000"
- Next in thread: Sylvain Lafontaine: "Re: conflicting object names in sql server 2000"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 8 Jan 2005 16:21:47 -0800
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... > 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: Sylvain Lafontaine: "Re: conflicting object names in sql server 2000"
- Previous message: Kalen Delaney: "Re: conflicting object names in sql server 2000"
- In reply to: zx6er93: "Re: conflicting object names in sql server 2000"
- Next in thread: Sylvain Lafontaine: "Re: conflicting object names in sql server 2000"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
