Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57

    Unanswered: Changing Field names is SQL Server 2000

    Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.

    Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Not had this issue before. Did you try querying syscolumns for Emp#?

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    I just queried syscolumns and there was a field name Emp# in an adhoc view that was unrelated to any other queries. I deleted the adhoc view but I'm still getting the the error on my other view for invalid column name Emp#.

    One thing I didn't mention in my other post is that I'm also using an MS Access ADP with this database. I've heard that access can sometimes cause problems with SQL Server. I've been making view, procs and table design in both MS Access and SQL Server - it depends on what I'm doing. I don't Access could possibly cause this kind of problems.

    Any other advice or possible solutions to this problem would be appreciated.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    When you say you opened the view and changed the view, what were you using? Where is the error message coming from? From Access, from SSMS (or SQL EM)?

    Is there an associated error number?

    If you are using MS Access and the view is defined as a linked table, you will have to delete and recreate the link.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    Thanks for your imput hmscott but I’m fairly new to SQL Server so I really don’t know what you’re talking about when you say SSMS (or SQL EM)?

    I don’t remember whether or not I actually changed the view in Access or Enterprise manager. I’m using an Access project that connects directly to the SQL Server database, it’s not like ODBC linked tables, I can make changes to any of the sql database objects directly through the Access UI.

    I don’t receive an error number , all I get is the error message “Invalid field name”. I get the same error in Access when I try to open the view -- when I try to open the view in enterprise manager -- and I have even copied the sql statement for the view and run it in Query analyzer and still get the same error.

    I’m completely baffled.

    Thanks,
    GEM

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As was said, you need to drop and recreate the table link. This is an Access problem, not a SQL Server problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    As I said before in my previous post - there is no link to drop. This is a MS Access Project (an ADP) not an ms access database (an .mdb) that has SQL tables and views linked via ODBC. If you are not familar with what an Access Project(ADP) is, then check it out - it's different than just having ODBC linked tables and views.

    I get the same error regardless of whether it's through the MS Access Project(ADP), enterprise manager or run the sql through query analyzer.....

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry. Did not notice you were using an Access Data Project.

    Post the code for the view that is giving you the error.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gem1204
    Thanks for your imput hmscott but I’m fairly new to SQL Server so I really don’t know what you’re talking about when you say SSMS (or SQL EM)?
    EM = ENterprise Manager (SQL 2000). SSMS = SQL Server Management Studio (SQL 2005). In case anyone blindsides with another, QA = Query Analyser (SQL 2000)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    EM = ENterprise Manager (SQL 2000). SSMS = SQL Server Management Studio (SQL 2005). In case anyone blindsides with another, QA = Query Analyser (SQL 2000)
    EMP - Elecromagnetic Pulse
    BOHICA - Bend Over, Here It Comes Again
    OSWATB - Oh, sh##! Where are the backups?



    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •