on 1 of my servers (actually, the dev. server I have setup up here at home), I must included the database owner everytime I select something or make a db call.
for example
select T.foo from K inner join T on T.id = K.id
must actualy be written like:
select dbo.T.foo from dbo.K inner join dbo.T on dbo.T.id = dbo.K.id
or else it wont work
what did I do wrong for this database owner thing to be a "must" when writing queries on this server.
spec:
sql 2000 sp4You are not connecting as a dbo user.|||
That's wierd. What error are you getting? It should check the dbo owned objects after checking for an object owned by you.
|||
Amir, please update thread.
Thanks,
Derek
|||sorry folks,I was tied up on another project for the week. I came back and tried to
trouble shoot instead of wasting you guy's time but at the end, i
failed.
basically, here is the situation:
the actual server that will end up hosting the project is setup fine so if i leave out the dbo.* or user.* and just type select * from table it works.
right now, i'm resorting to including the dbo in my queries... and evertime i upload to the server, i do a search in that folder and replace " dbo." with "" in all files.
let me explain how i made this database:
originally, it was on a dev server somewhere in US...
i made the db on my home server. Then created a user and gave it admin privilages over that db.
Then i did "All Tasks > Import" and imported the tables from that server, to this new home db. I had to go back and manually select the primary key for each table, as they got lost during the transfer.
now, when i look at the "Server Explorer" cluster of tables in this new database, i see that they all have (dbo) beside their names, meaning the owner of each table by default is dbo.
i think that should pretty much cover everything....
any clue as to why user "must" be specified?|||
What do you get on the server that requires dbo. when you execute:
select suser_sname(), user_name()
Also, for some object where you have to enter dbo. for the object, execute:
select *
from sysobjects
where name = '<name>'
select *
from information_schema.tables
where table_name = '<name>'
Perhaps this will shed some light? Also what is in @.@.version?
This might just be a stumper that requires a higher power :)
|||> What do you get on the server that requires dbo. when you execute:
>select suser_sname(), user_name()
| __|_| foouser | foouser
>select *
>from sysobjects
>where name = '<name>'
irrelevant
> select *
> from information_schema.tables
> where table_name = '<name>'
TABLE_SCHEMA is 'dbo' for all objects
conclusion:
objects where created as dbo when they were imported from the server on the hosting company to the local staging environment;
how can i prevent this when using SQL 2000 enterprise manager for import
(note: from my exp. with sql 2005, this problem wasn't encountered when going through the same steps -- or rather similar steps -- using native sql 2005 import/export functionality within the SQL 2005 studio)
side note:
i rather find a script based answer rather learning to use the GUI, because of my DB2, mysql background -- obviously i'm not gifted when using GUI tools
No comments:
Post a Comment