major bug in SQL 2000

Hi,
today I discovered a major and dangerous bug in SQL server. I've
created a view with many fields. When looking in the design manager
everything looks fine. However when I use the view, one of the column
headings is missing and the column names are shift to the left. The
values however are still on their own place. This results in a major
issue that the values appear under the wrong column. In design view
everything looks fine, but when you open the query it display the
wrong values. Thus:
In design view it looks like this:
Field1 Field2 Field3 Field4
1 2 3 4
When opening the query it looks like this:
Field1 Field3 Field4
1 2 3
How the hell is this possible. Changing one thing in the view saving
it again and then opneing it, everything is fine. But how do I know
which views are affected ?
Franc.I have screenshots available fot those interested. So far I've found 4
views which were affected. They were all based on the same tables. I
ran a dbcc checkdb and no error are reported on the database.
Franc.

>Hi,
>today I discovered a major and dangerous bug in SQL server. I've
>created a view with many fields. When looking in the design manager
>everything looks fine. However when I use the view, one of the column
>headings is missing and the column names are shift to the left. The
>values however are still on their own place. This results in a major
>issue that the values appear under the wrong column. In design view
>everything looks fine, but when you open the query it display the
>wrong values. Thus:
>In design view it looks like this:
>Field1 Field2 Field3 Field4
>1 2 3 4
>When opening the query it looks like this:
>Field1 Field3 Field4
>1 2 3
>How the hell is this possible. Changing one thing in the view saving
>it again and then opneing it, everything is fine. But how do I know
>which views are affected ?
>Franc.|||Can we see your code please.
J

>--Original Message--
>I have screenshots available fot those interested. So far
I've found 4
>views which were affected. They were all based on the
same tables. I
>ran a dbcc checkdb and no error are reported on the
database.
>Franc.
>
server. I've[vbcol=seagreen]
design manager[vbcol=seagreen]
of the column[vbcol=seagreen]
the left. The[vbcol=seagreen]
results in a major[vbcol=seagreen]
design view[vbcol=seagreen]
display the[vbcol=seagreen]
view saving[vbcol=seagreen]
how do I know[vbcol=seagreen]
>.
>|||Hi Franc
Are you altering the objects underneath the view? This can cause the
behaviour you're describing. That SQL Server lets you simply change objects
underneath views without warning is not great, but this isn't a bug as such.
You should look into using the SCHEMABINDING option in your CREATE VIEW
statements. This was designed to ensure that the objects that views
reference are not permitted to change underneath those views. That way, you
don't have to trawl through lists of dependent objects to make sure
everything is in synch..
Regards,
Greg Linwood
SQL Server MVP
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a34498.84859734@.msnews.microsoft.com...
> Hi,
> today I discovered a major and dangerous bug in SQL server. I've
> created a view with many fields. When looking in the design manager
> everything looks fine. However when I use the view, one of the column
> headings is missing and the column names are shift to the left. The
> values however are still on their own place. This results in a major
> issue that the values appear under the wrong column. In design view
> everything looks fine, but when you open the query it display the
> wrong values. Thus:
> In design view it looks like this:
> Field1 Field2 Field3 Field4
> 1 2 3 4
> When opening the query it looks like this:
> Field1 Field3 Field4
> 1 2 3
> How the hell is this possible. Changing one thing in the view saving
> it again and then opneing it, everything is fine. But how do I know
> which views are affected ?
> Franc.|||Hi,
this is the SQL statement I'm using:
SELECT TOP 100 PERCENT dbo.tblWoningen.*, dbo.Huren.*,
dbo.tblPuntenTotaal.*,
dbo.Woningen.Perceel AS Expr1
FROM dbo.Woningen LEFT OUTER JOIN
dbo.Huren ON
dbo.Woningen.Perceel = dbo.Huren.Perceelnr# LEFT
OUTER JOIN
dbo.tblPuntenTotaal ON dbo.Woningen.Perceel =
dbo.tblPuntenTotaal.VHE_nummer
ORDER BY dbo.Woningen.Perceel
Franc.
[vbcol=seagreen]
>Can we see your code please.
>J
>
>I've found 4
>same tables. I
>database.
>server. I've
>design manager
>of the column
>the left. The
>results in a major
>design view
>display the
>view saving
>how do I know|||Hi Greg,
no I haven't altered the tables below the views, only added data to
it. What I don't get is, why it looks correctly when run from design
view, but as soon as you run the view it goes wrong. After saving the
view from design view it displays the correct values again. Looks like
SQL is recompiling the view. Is there a way to force recompilation of
all view to ensure that everything is correct. Last week everything
worked fine, today we noticed that the wrong values appeared in field
on our mailing letters.
Franc.
On Thu, 13 May 2004 20:40:12 +1000, "Greg Linwood"
<g_linwoodQhotmail.com> wrote:

>Hi Franc
>Are you altering the objects underneath the view? This can cause the
>behaviour you're describing. That SQL Server lets you simply change objects
>underneath views without warning is not great, but this isn't a bug as such
.
>You should look into using the SCHEMABINDING option in your CREATE VIEW
>statements. This was designed to ensure that the objects that views
>reference are not permitted to change underneath those views. That way, you
>don't have to trawl through lists of dependent objects to make sure
>everything is in synch..
>Regards,
>Greg Linwood
>SQL Server MVP
>"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
>news:40a34498.84859734@.msnews.microsoft.com...
>|||Hi Franc
Using * in views is a bad idea. If the columns in the tables underneath the
view change, the view is not automatically updated. schemabinding would at
least help you by forcing the person who alters the columns in the
underlying tables to recompile the view.
Regards,
Greg Linwood
SQL Server MVP
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a35050.87859203@.msnews.microsoft.com...
> Hi,
> this is the SQL statement I'm using:
> SELECT TOP 100 PERCENT dbo.tblWoningen.*, dbo.Huren.*,
> dbo.tblPuntenTotaal.*,
> dbo.Woningen.Perceel AS Expr1
> FROM dbo.Woningen LEFT OUTER JOIN
> dbo.Huren ON
> dbo.Woningen.Perceel = dbo.Huren.Perceelnr# LEFT
> OUTER JOIN
> dbo.tblPuntenTotaal ON dbo.Woningen.Perceel =
> dbo.tblPuntenTotaal.VHE_nummer
> ORDER BY dbo.Woningen.Perceel
> Franc.
>
>|||Hi Franc
Sorry for repeating a little in this thread, but this is what schemabinding
is for. It forces the person who's altering the table / view underneath the
view to recompile the view at the time the underlying object is altered.
Regards,
Greg Linwood
SQL Server MVP
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a35148.88107390@.msnews.microsoft.com...
> Hi Greg,
> no I haven't altered the tables below the views, only added data to
> it. What I don't get is, why it looks correctly when run from design
> view, but as soon as you run the view it goes wrong. After saving the
> view from design view it displays the correct values again. Looks like
> SQL is recompiling the view. Is there a way to force recompilation of
> all view to ensure that everything is correct. Last week everything
> worked fine, today we noticed that the wrong values appeared in field
> on our mailing letters.
> Franc.
> On Thu, 13 May 2004 20:40:12 +1000, "Greg Linwood"
> <g_linwoodQhotmail.com> wrote:
>
objects[vbcol=seagreen]
such.[vbcol=seagreen]
you[vbcol=seagreen]
>|||If you post a complete repro script, we can try it on our SQL Servers and se
e if it reproduces...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a35050.87859203@.msnews.microsoft.com...
> Hi,
> this is the SQL statement I'm using:
> SELECT TOP 100 PERCENT dbo.tblWoningen.*, dbo.Huren.*,
> dbo.tblPuntenTotaal.*,
> dbo.Woningen.Perceel AS Expr1
> FROM dbo.Woningen LEFT OUTER JOIN
> dbo.Huren ON
> dbo.Woningen.Perceel = dbo.Huren.Perceelnr# LEFT
> OUTER JOIN
> dbo.tblPuntenTotaal ON dbo.Woningen.Perceel =
> dbo.tblPuntenTotaal.VHE_nummer
> ORDER BY dbo.Woningen.Perceel
> Franc.
>
>|||Hi Greg,
ok, I'll alter the view by using schemabindings. However, I don't get
why SQL just moves the column names and not the data. If it would
remove the data also it's fine. Now you get the wrong data in the
wrong columns whcih is much more dangerous.
Franc.

>Hi Franc
>Sorry for repeating a little in this thread, but this is what schemabinding
>is for. It forces the person who's altering the table / view underneath the
>view to recompile the view at the time the underlying object is altered.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
>news:40a35148.88107390@.msnews.microsoft.com...
>objects
>such.
>you
>