Post reply

Query fail when using only table name

  • July 21, 2011 at 5:31 pm

    #244780

    Hello !

    I have a problem after switch servers. First, we have a server with SQL Server 2005 and all was working ok. Now, we upgraded to SQL Server 2008 and now, all queries from the application fails.

    For example, this query works ok on the old server:

    select top 1 fondo from tablename order by newid()

    but on the new server fails with error: Object name 'tablename' is not valid.

    If I change query to this:

    select top 1 fondo from username.tablename order by newid()

    works perfectly.

    There is a way to use the old format, so we can use queries referencing only the table name ? There are a lot of queries on the application and will be a really pain to change all.

    Any ideas ?

    Thanks!

  • July 21, 2011 at 5:36 pm

    #1356826

    Sounds like you are missing the table object, eh?

    Please verify.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 21, 2011 at 5:48 pm

    #1356830

    (sorry for my bad english)

    Thanks for your fast response.

    Missing the table object ? From where ? At the query ? 'tablename' is just an example and is the table object name at the queries.

    The problem is that the queries works perfect on older server. For the queries to work on new server, we must add the username to the table name on queries.

    I'm lost searching for the problem.

    (Sorry for don't post on correct format. Try to do better next time.)

  • July 22, 2011 at 4:06 am

    #1356909

    Correction.

    You have to include the schema "username".

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 22, 2011 at 6:09 am

    #1356987

    1. Check compatibility ( it was in 80 | 90 | 100)

    2. Check it was schema name | user name

  • July 22, 2011 at 6:24 am

    #1356998

    isdn't the core issue that the user is not using the default dbo schema, so , for example, the user "Orange" creates a table Orange.Invoices instead of the expected dbo.Invoices.

    compatibility level won't make any differnece as far as that goes. most people get lazy and just make the user db_owner to avoid it, Let me test an example schema to be sure i've got a good example to post.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 22, 2011 at 6:34 am

    #1357006

    Finally found the problem.

    Yes, what was not had to add the user, but the scheme. So, adding the "default squeme" to the user properties solve the problem.

    Thanks to all !!!

  • July 22, 2011 at 6:35 am

    #1357007

    i beleive the fix is to change the users default schema...which doesn't necessarily allow them to create tables.

    http://msdn.microsoft.com/en-us/library/ms176060.aspx

    --from BOL ~ slightly changed

    ALTER USER Mary51 WITH DEFAULT_SCHEMA = dbo;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply