Questions re: changes to views in our db

Discussion in 'Databases' started by RealgyEnergy, Dec 30, 2009.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. RealgyEnergy

    RealgyEnergy

    I am running our web application from DASP for the first time this morning. I am getting this error:
    Invalid object name 'RES_NEW.dbo.cstmr_addr'.
    Could not use view or function 'vw_RES_usage' because of binding errors​
    I believe this is because in our views, all our tables are prefaced with owner dbo. Can you confirm? If that is the case, I need to edit all our views and remove the dbo preface. What is the best way to do this? Do I have to change the views locally and export them?

    We are using SQL Server 2008. Any instruction you can provide on making/exporting these changes would be appreciated as we upgraded to 2008 as part of our migration to DASP and I have not used it much yet (I am most familiar with SQL Server 2000).

    Thanks.
     
    RealgyEnergy, Dec 30, 2009
    #1
  2. dmitri

    dmitri DiscountASP.NET Staff

    When you reference a table from a view, you must use two-part name such as [SchemaName].[TableName]. In your case you should use dbo.cstmr_addr whre 'dbo' is the schema containing your cstmr_addr table. RES_NEW.dbo.cstmr_addr is a three-part name and it is not valid inside a view.

    You will receive a 'binding error' if you use invalid table name, if the table was renamed after the view was created, or the table was dropped.
     
    dmitri, Dec 30, 2009
    #2
  3. RealgyEnergy

    RealgyEnergy

    The cause was actually because of the reference to RES_NEW which is a database so named locally, but the name has changed at your site to follow your naming conventions. So we have to edit our views to the new names.

    Also - sorry for the cross-post. I thought I had refreshed recently, but I did not see the reply sent 30 minutes ago.
     
    RealgyEnergy, Dec 30, 2009
    #3
  4. dmitri

    dmitri DiscountASP.NET Staff

    You don't have to include database prefix unless you have more then one database and make cross-database referencing. Just use [schema].[table] naming pattern and you won't need to rename anything.
     
    dmitri, Dec 30, 2009
    #4
  5. RealgyEnergy

    RealgyEnergy

    We do have more than one database and are cross-referencing. We are having a problem with the user of one database being able to access the other database. I believe my coworker has opened a ticket.
     
    RealgyEnergy, Dec 30, 2009
    #5
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page