Weird SQl problem

[thermolaney]

Hi,
I am using sql server 2003 and have come up on something I have not seen. I have views and if I add a field (to a table in the view) the view starts returning the wrong fields. ANy idias?

 

[mrdenny]

First we need to figure out what version of SQL your using.

There is no such thing as SQL Server 2003.

In query analyzer run:

select @@VERSION

It will return something like this.

Microsoft SQL Server 2000 - 8.00.937 (Intel X86) Apr 29 2004 16:09:37 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

The part is blue is the version that you are running.

Please also post the create view statement.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[thermolaney]

Version is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: )
Here is the view
REATE VIEW dbo.wpindex_detail_LU
AS
SELECT dbo.wpindex.*, dbo.Controls.ControlName , dbo.ControlAreas.CArea , dbo.sites.site , dbo.Systems.SysName ,
dbo.ctrl_cfg.type AS CTRL_TYPE, dbo.Controls.ICID AS TCID
FROM dbo.sites RIGHT OUTER JOIN
dbo.Systems RIGHT OUTER JOIN
dbo.ctrl_cfg RIGHT OUTER JOIN
dbo.wpindex ON dbo.ctrl_cfg.sysctrl_id = dbo.wpindex.sysctrlid LEFT OUTER JOIN
dbo.ControlAreas RIGHT OUTER JOIN
dbo.Controls ON dbo.ControlAreas.CAreaID = dbo.Controls.CotrolArea ON dbo.ctrl_cfg.ctrl_id = dbo.Controls.ControlID ON
dbo.Systems.SysID = dbo.wpindex.Sys_id ON dbo.sites.site_id = dbo.wpindex.site_id

 

[SQLBill]

EWWW, an almost totally unpatched SQL Server. That's not good. Install SQL Server SP3a. That will bring your system pretty much up to date. It "might" also solve your problem if it's caused by a bug that MS fixed.

8.00.194

The 8 means SQL Server 2000, the 194 is the update version and should be at least 760.

-SQLBill

Posting advice: FAQ481-4875

 

[thermolaney]

Its a test server so it does not get patched much since it gets rebuilt regularly

 

[SQLBill]

Possible suggestion for you....next time you build the SQL Server, apply SP3a and GHOST the server. That way it doesn't have to be rebuilt from scratch everytime and you have the latest patch.

(I know that's not solving your problem...)

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

View are compiled objects in the database. When you add a field to a table that the view is referencing, you need to recreate the view. (There may be other ways of doing this that I am not aware of.)

Using Query Analyzer, open your list of view.
Right click on the view.
Click Edit
A new window will open. press F5 on your keyboard.

The view will be re-created and will *probably* start returning the correct data.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

When an underlying table is droped or altered the view must be dropped and recreated.

If a view depends on a table (or view) that was dropped, SQL Server produces an error message if anyone tries to use the view. If a new table (or view) is created, and the table structure does not change from the previous base table, to replace the one dropped, the view again becomes usable. If the new table (or view) structure changes, then the view must be dropped and recreated.

I'd also recommend not useing the "select dbo.wpindex.* " in your view. Only pull back the columns that you need. It will make your view faster.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)