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
>
Showing posts with label bug. Show all posts
Showing posts with label bug. Show all posts
Friday, March 9, 2012
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.
>.
>
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.
>.
>
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:
Field1Field2Field3Field4
1234
When opening the query it looks like this:
Field1Field3Field4
123
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:
>Field1Field2Field3Field4
>1234
>When opening the query it looks like this:
>Field1Field3Field4
>123
>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.[vbcol=seagreen]
>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
>.
>
|||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...[vbcol=seagreen]
> 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
>
|||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.
>
|||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
>
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:
Field1Field2Field3Field4
1234
When opening the query it looks like this:
Field1Field3Field4
123
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:
>Field1Field2Field3Field4
>1234
>When opening the query it looks like this:
>Field1Field3Field4
>123
>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.[vbcol=seagreen]
>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
>.
>
|||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...[vbcol=seagreen]
> 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
>
|||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.
>
|||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
>
Monday, February 20, 2012
maintenance plan wizard - security question, possible bug?
Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way to
specify that (concering that I get IS package, very hard to modify anything)?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:
> Pedja
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000).
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed the
> password
> In other words , your SQL Agent runs under the same account as you logged ,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> > Hi All,
> > I am trying to create maintenance plan for updating statistics, checking
> > database integrity and database backup, using wizard. First of all, I
> > don't
> > see any option for how long do I want to keep backup files on the system
> > (like we had on 2000). I find it very inconvinient. Is there any other way
> > to
> > specify that (concering that I get IS package, very hard to modify
> > anything)?
> > More important question is regarding security issue with this wizard: I
> > have
> > only 'sa' access to the server (not member of local admin group, and I'm
> > not
> > able to use windows authentication). Each month we change 'sa' password on
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> > am I
> > missing something?
> > Thanks in advance,
> > Pedja
> >
> > P.S. The same thing would happen even if I created maintenance plan
> > manually, because I would have to create connection objects manually (and
> > specify 'sa' again)...
>
>
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way to
specify that (concering that I get IS package, very hard to modify anything)?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:
> Pedja
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000).
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed the
> password
> In other words , your SQL Agent runs under the same account as you logged ,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> > Hi All,
> > I am trying to create maintenance plan for updating statistics, checking
> > database integrity and database backup, using wizard. First of all, I
> > don't
> > see any option for how long do I want to keep backup files on the system
> > (like we had on 2000). I find it very inconvinient. Is there any other way
> > to
> > specify that (concering that I get IS package, very hard to modify
> > anything)?
> > More important question is regarding security issue with this wizard: I
> > have
> > only 'sa' access to the server (not member of local admin group, and I'm
> > not
> > able to use windows authentication). Each month we change 'sa' password on
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> > am I
> > missing something?
> > Thanks in advance,
> > Pedja
> >
> > P.S. The same thing would happen even if I created maintenance plan
> > manually, because I would have to create connection objects manually (and
> > specify 'sa' again)...
>
>
maintenance plan wizard - security question, possible bug?
Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way t
o
specify that (concering that I get IS package, very hard to modify anything)
?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am
I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:
> Pedja
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed t
he
> password
> In other words , your SQL Agent runs under the same account as you logged
,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
>
>
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way t
o
specify that (concering that I get IS package, very hard to modify anything)
?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am
I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:
> Pedja
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed t
he
> password
> In other words , your SQL Agent runs under the same account as you logged
,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)