March 21, 2011 at 9:52 am
I am connecting to SQL 2000 DB from my SQL 2008 SSMS.
I am using SQL login "xyz" to connect to SQL 2000 server and once connected, I am running a simple query.
-- DEV Server
Use abc
go
select * from Table1
I get the below
Msg 208, Level 16, State 1, Line 1
Invalid object name 'account'.
------
I am being forced to define the object owner as 'xyz' as it is the owner for the query to work.
Use abc
go
select * from xyz.Table1
**What has confused me is that when I do the same with a different server (PROD Server) it works just fine.
--PROD Server
Use abc
go
select * from Table1 --NO Need for object owner and it still works fine.
I have granted 'sa' permissions as well to 'xyz' login in DEV env, but it still throws the same error and wants me to define the object owner.
Note: both DEV and PROD are at the same SQL Build level.
Any help will be appreciated.
March 21, 2011 at 10:30 am
I am pretty sure this has nothing to do witht eh fact that your using SSMS 2008 by the way, this has to do with how SQL Server looks for an object that is not fully qualified.
I am not sure if this pertains to 2000 (this is really testing my memory) but you should compare the "default schema" between the user in DEV and PROD. My guess (like I said, I am testing my memory, I do not have a SQL 2000 instance to test) is that the default schemas are setup differently.
In general you should always schema-qualify (owner-qualify is the correct lingo for SQL 2000) your object names. It can improve performance and cut down on unintended consequences with "invalid object" references like what you're experiencing.
Also, there is is a dedicated SQL 7,2000 forum...you may have better luck finding an answer from someone still working with SQL 2000 regularly in there. http://www.sqlservercentral.com/Forums/Group406.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 21, 2011 at 12:40 pm
I appreciate your feedback.
1.) The Same query with the same user connection, works fine in SQL Query Analyzer.
2.) Also, to re-state again, the same query works fine from SSMS with Prod server which is also a SQL 2000 at the same version level.
I can go to SQL 2000 forum but thought of providing the feedback if someone has gone through a similar situation or if it is somehow looking for 'dbo' as the default schema, and any other objectowner should be specified explicity (and will not be inherited from the user making that connection).
March 21, 2011 at 2:06 pm
Please proceed knowing that this is not an SSMS / Query Analyzer issue.
Hopefully you will find the issue by checking these four things:
1. In DEV: Is there a table name xyz.account?
2. In DEV: Is there a table name dbo.account?
3. In PROD: Is there a table name xyz.account?
4. In PROD: Is there a table name dbo.account?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 21, 2011 at 8:20 pm
Below are my in-line responses:
Please proceed knowing that this is not an SSMS / Query Analyzer issue. ---- Ok, thanks for teh confirmation.
Hopefully you will find the issue by checking these four things:
1. In DEV: Is there a table name xyz.account? --- YES
2. In DEV: Is there a table name dbo.account? ---- NO
3. In PROD: Is there a table name xyz.account? --- YES
4. In PROD: Is there a table name dbo.account? --- NO
I am still unable to figure out what could be the issue here ....I understand it is a trivial matter and I have instructed the user to use FQN i.e, objectowner.objectname when using SSMS 2008 (as opposed to SQL EM).
Thanks!
March 21, 2011 at 8:27 pm
WasimAli (3/21/2011)
-- DEV ServerUse abc
go
select * from Table1
I get the below
Msg 208, Level 16, State 1, Line 1
Invalid object name 'account'.
Since you are selecting on Table1 and getting an error about account I am assuming that Table1 is a view? If that is the case can you post the code for the view? (Can you just edit the view to fully qualify the reference to account?
March 21, 2011 at 8:30 pm
WasimAli (3/21/2011)
I appreciate your feedback.1.) The Same query with the same user connection, works fine in SQL Query Analyzer.
2.) Also, to re-state again, the same query works fine from SSMS with Prod server which is also a SQL 2000 at the same version level.
From that I would guess that the user is setup differently on Dev than it is on Prod, you might just check that out.
And on the track of what OPC was asking:
1. In DEV: Is there a view named xyz.table1?
2. In DEV: Is there a view named dbo.table1?
3. In PROD: Is there a view named xyz.table1?
4. In PROD: Is there a view named dbo.table1?
March 22, 2011 at 8:58 am
From that I would guess that the user is setup differently on Dev than it is on Prod, you might just check that out.
And on the track of what OPC was asking:
1. In DEV: Is there a view named xyz.table1?
2. In DEV: Is there a view named dbo.table1?
3. In PROD: Is there a view named xyz.table1?
4. In PROD: Is there a view named dbo.table1?
--------------------------------------------------------
Keeping in mind the possibility that the user may be setup differently in DEV, i gave the user 'sa' access (as it is DEV env.) but that didn't help either, so it is not permissions issuse....unless other advance settings that could be affecting it.
And 'no' there are no VIEWS involved, it is just plain simple SQL Table....that is why it is so perplexing.
I am hopeful one of you or someone on this forum can assist me with this mystery.
March 22, 2011 at 10:00 am
WasimAli (3/22/2011)
Keeping in mind the possibility that the user may be setup differently in DEV, i gave the user 'sa' access (as it is DEV env.) but that didn't help either, so it is not permissions issuse....unless other advance settings that could be affecting it.
It might be that having too many permissions in DEV is causing the problem. Try setting security for the user in DEV up exactly like PROD and testing.
And 'no' there are no VIEWS involved, it is just plain simple SQL Table....that is why it is so perplexing.
Do does that mean that what you put in your first post isn't accurate:
Use abc
go
select * from Table1
I get the below
Msg 208, Level 16, State 1, Line 1
Invalid object name 'account'.
In that you show you are selecting from Table1, but getting an error on object account, that sure looks like a view.
March 22, 2011 at 12:02 pm
Ok ..I will proceed with exact same permissions in Dev and Prod and check it out.
And sorry, I just thought of using 'Table1' as the table name and when I posted the error I didn't change the name in that ...so in reality the table in question is "Account" (xyz and table1 were just placeholders in the query, DB name is also not xyz).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply