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?Not had this issue before. Did you try querying syscolumns for Emp#?

Regards,

hmscott|||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.|||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|||Thanks for your imput hmscott but Im fairly new to SQL Server so I really dont know what youre talking about when you say SSMS (or SQL EM)?

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

I dont 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.

Im completely baffled.

Thanks,
GEM|||As was said, you need to drop and recreate the table link. This is an Access problem, not a SQL Server problem.|||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....|||Sorry. Did not notice you were using an Access Data Project.

Post the code for the view that is giving you the error.|||Thanks for your imput hmscott but Im fairly new to SQL Server so I really dont know what youre 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) :)|||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?

:D

Regards,

hmscott