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.
>>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 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.
>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.
>>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 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,
>> 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 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.
> >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.
> >>
> >>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 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:
> >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.
> >
> >
>|||If you post a complete repro script, we can try it on our SQL Servers and see 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.
> >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.
> >>
> >>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 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...
>> 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,
>> >>
>> >> 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'm not sure & couldn't answer that precisely with the info at hand. Maybe
someone else might chip in with an explanation on that bit. Hopefully it's
clear for you now what schemabinding's about & why it's important to the
stability of views though.
Regards,
Greg Linwood
SQL Server MVP
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a36040.91940250@.msnews.microsoft.com...
> 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...
> >> 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,
> >> >>
> >> >> 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.
> >> >
> >> >
> >>
> >
> >
>|||SQL Server is not moving anything. It is the client application that is
just not displaying it properly.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
no that's not the case. I can prove it to you with some screen shots.
Those clearly show that when run from the design manger it looks fine
but as soon as I use query analyzer the wrong data is displayed. And
the wrong data is also presented to our intranet application which
uses the view and a word mailing document which also uses the view.
It's definitely something wrong with SQL server. I'm not using a
client application to fill or manipulate the data.
Franc.
>SQL Server is not moving anything. It is the client application that is
>just not displaying it properly.
>Rand
>This posting is provided "as is" with no warranties and confers no rights.|||Hi Franc,
Would it be possible to send me screen shots in .jpeg
format.
My email is little_flowery_me@.hotmail.com
J
>--Original Message--
>Hi,
>no that's not the case. I can prove it to you with some
screen shots.
>Those clearly show that when run from the design manger
it looks fine
>but as soon as I use query analyzer the wrong data is
displayed. And
>the wrong data is also presented to our intranet
application which
>uses the view and a word mailing document which also
uses the view.
> It's definitely something wrong with SQL server. I'm
not using a
>client application to fill or manipulate the data.
>Franc.
>>SQL Server is not moving anything. It is the client
application that is
>>just not displaying it properly.
>>Rand
>>This posting is provided "as is" with no warranties and
confers no rights.
>.
>