Joined view does not contain all colums

The joined view is named "dbo.viewExecView" and is like:

SELECT Bank_No, data_center
FROM [ALPHA\SQL2000].ev_db.dbo.Bank

The new view that joins to the above view is like:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.

I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.

Regards,

Joe

It looks ok to me. Does your simplified query (below) work?

Joe G wrote:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

|||

first thing, try running ALTER VIEW with their current defenitions. A view is built at runtime like a table. It has records in syscolumns etc. Thus this thread brings up the old "what does SELECT * return in a view" questions. I got this wrong not too long ago when IView with MSFT...I could have killed myself lol.

I am telling you this info. because whenever someone says "a column that I recently added is not showing up in view", this is usually the problem. rerun THE ALTERVIEW statement.

HTH,

Derek

if this does not solve your problem, let me know and I will dig into it w/you further.

|||

Derek, Thank you very much! That indeed was the solution to my problem. Thanks to Skippy also for responding.

Joe