making JOINS

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join
>
nice weekeend
>
Table 1
>
10 5012 10
1600502
100 502 3
1500800 2
>
>
>
>
Table 2
>
1500503 1
14004000
100502 10
100600
100502 3
>

Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)|||Hi Daniel,
thanks alot for the prompt reply. this is what i had:

100 502
100 502
1500 800

as seen in the table 100 502was ordered and shipped but more articles
were shipped not ordered by customerID(100). I do not need this
information. I need a list where a customer ordered and received
shippment where no product ordered was shipped. in this case
customer(1500) should be the right answer. He ordered and recived
shippement but within the list no article oredered was shiped. but
customer (100) received the ordered product plus some extra
Hope to read from u and any one else

DA Morgan wrote:

Quote:

Originally Posted by

MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3

>
Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)

|||On 23 Oct 2006 00:52:26 -0700, MORALBAROMETER wrote:

Quote:

Originally Posted by

>Hi Daniel,
>thanks alot for the prompt reply. this is what i had:
>
>100 502
>100 502
>1500 800
>
>as seen in the table 100 502was ordered and shipped but more articles
>were shipped not ordered by customerID(100). I do not need this
>information. I need a list where a customer ordered and received
>shippment where no product ordered was shipped. in this case
>customer(1500) should be the right answer. He ordered and recived
>shippement but within the list no article oredered was shiped. but
>customer (100) received the ordered product plus some extra
>Hope to read from u and any one else

Hi MORALBAROMETER,

Maybe something like this?

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
LEFT JOIN Shipments AS s
ON s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID
WHERE s.CustomerID IS NULL

or the following (logically equivalent, but easier to understand for
beginning SQL coders):

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
WHERE NOT EXISTS
(SELECT *
FROM Shipments AS s
WHERE s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID)

If these are not what you're after, then I recommend that you post your
table structure (as CREATE TABLE statements, including constraints,
properties and indexes), some rows of sample data (as INSERT statements)
and expected results. See www.aspfaq.com/5006 for more info.

--
Hugo Kornelis, SQL Server MVP

Making field read only

Goodday all

I have a details view on a vb form .

Now that i have been working on the program , i realise that i would like to make some fields " read only '

Is that possible , and if so how?

Thanks

Rob

You could:

1 - Set permissions in SQL Server for SELECT only.
2. -Set the form properties to 'Read Only' (Enabled = False) for that textbox.

sql

making a date field read as MMDDYYYY

Hello:
I am trying to get a field that reads as 6/29/1983 as 06291983, including a
leading zero where any month fields are not double digit months. It needs t
o
read as MMDDYYYY.
Is there a way to do this? I tried the following statement but for some
reason it returned only a bogus year and not even the month, day, or even th
e
correct year.
Please help!
select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
'+DATEPART(yyyy,BRTHDATE) from UPR00100Here's one method (assuming datatype is datetime):
SELECT REPLACE(CONVERT(varchar(20), GETDATE(), 105), '-', '')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
a
> leading zero where any month fields are not double digit months. It needs
to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||select replace(convert(varchar(10), getdate(), 101), '/', '')
Your Query doesn't work because the Datepart function returns Integers.
Even if you Cast them as Varchar, leading zeros would be dropped.
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
> a
> leading zero where any month fields are not double digit months. It needs
> to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
> the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||DATEPART returns an int, so you need to convert it to char or varchar to
create the string.
"childothe1980s" wrote:

> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
a
> leading zero where any month fields are not double digit months. It needs
to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||Come from VBScript? I sympathize. In T-SQL, you can't just add integers to
strings and expect SQL Server to know what you are trying to do... T-SQL is
slightly more strongly typed than VBScript.
Give the following a try instead:
SELECT RIGHT(d,4) + LEFT(d, 4) FROM
(SELECT d = CONVERT(CHAR(8), GETDATE(), 112)) x;
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
> a
> leading zero where any month fields are not double digit months. It needs
> to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
> the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||Try:
select
replace (convert (varchar (20), getdate (), 101), '/', '')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
Hello:
I am trying to get a field that reads as 6/29/1983 as 06291983, including a
leading zero where any month fields are not double digit months. It needs
to
read as MMDDYYYY.
Is there a way to do this? I tried the following statement but for some
reason it returned only a bogus year and not even the month, day, or even
the
correct year.
Please help!
select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
'+DATEPART(yyyy,BRTHDATE) from UPR00100

make table cell text - BOLD

hi there, i have question..i have a table which i fill with data form
a database...
Col1 Col2 Col3
---
=Fields!.. =Fields!... =Fields!
Col1 is filled with some text and has like 15 rows, and Col2 and Col3
are its values...So my question is - is it possible to have certain
values from Col1 to be BOLD, for example i want to have the fourth and
sixth row value for Col1 to be BOLD text...
THNX!Maybe you can place each of your 15 rows in Col1 in one detail row in your
table, so you can apply specific format in every separated row.
Does it help?
"ApeX" <jkdmaster_5@.hotmail.com> escribió en el mensaje
news:1187248049.501166.88240@.19g2000hsx.googlegroups.com...
> hi there, i have question..i have a table which i fill with data form
> a database...
> Col1 Col2 Col3
> ---
> =Fields!.. =Fields!... =Fields!
>
> Col1 is filled with some text and has like 15 rows, and Col2 and Col3
> are its values...So my question is - is it possible to have certain
> values from Col1 to be BOLD, for example i want to have the fourth and
> sixth row value for Col1 to be BOLD text...
> THNX!
>|||On Aug 16, 3:07 am, ApeX <jkdmaste...@.hotmail.com> wrote:
> hi there, i have question..i have a table which i fill with data form
> a database...
> Col1 Col2 Col3
> ---
> =Fields!.. =Fields!... =Fields!
> Col1 is filled with some text and has like 15 rows, and Col2 and Col3
> are its values...So my question is - is it possible to have certain
> values from Col1 to be BOLD, for example i want to have the fourth and
> sixth row value for Col1 to be BOLD text...
> THNX!
Try inserting this expression into the FontWeight property for the
cell you want:
=IIF(RowNumber(nothing) = 4 or RowNumber(nothing) = 6, "Bold",
"Normal")
That should work
toolman|||Your not going to be able to do it particularly gracefully if there's no
logic behind the decision.
What you can do is us a custom code block and put an expression on the
FontWeight property of yuor column.
Public Function GetFontWeight( _
ByVal columnValue As String) As String
If columnValue = "Col1" Or columnValue = "Col2 Then Return "Bold"
Return "Normal"
End Function
Expression on FontWeight property:
=Code.GetFontWeight(Fields!YourColumn.Value)
Joe
"ApeX" wrote:
> hi there, i have question..i have a table which i fill with data form
> a database...
> Col1 Col2 Col3
> ---
> =Fields!.. =Fields!... =Fields!
>
> Col1 is filled with some text and has like 15 rows, and Col2 and Col3
> are its values...So my question is - is it possible to have certain
> values from Col1 to be BOLD, for example i want to have the fourth and
> sixth row value for Col1 to be BOLD text...
> THNX!
>|||To follow up with what Joe said, if you have logic that determines which rows
you want bold it is possible. Simply put that logic in the FontWeight
property of the cell (either using the custom code joe suggested or a simple
IIF statement). You will, however, have to bold the entire contents of that
cell. You won't be able to just bold certain words (wasn't sure if this was
your question).
Hope this helps...
"Joe" wrote:
> Your not going to be able to do it particularly gracefully if there's no
> logic behind the decision.
> What you can do is us a custom code block and put an expression on the
> FontWeight property of yuor column.
> Public Function GetFontWeight( _
> ByVal columnValue As String) As String
> If columnValue = "Col1" Or columnValue = "Col2 Then Return "Bold"
> Return "Normal"
> End Function
> Expression on FontWeight property:
> =Code.GetFontWeight(Fields!YourColumn.Value)
> Joe
> "ApeX" wrote:
> > hi there, i have question..i have a table which i fill with data form
> > a database...
> >
> > Col1 Col2 Col3
> > ---
> > =Fields!.. =Fields!... =Fields!
> >
> >
> > Col1 is filled with some text and has like 15 rows, and Col2 and Col3
> > are its values...So my question is - is it possible to have certain
> > values from Col1 to be BOLD, for example i want to have the fourth and
> > sixth row value for Col1 to be BOLD text...
> >
> > THNX!
> >
> >

make drop-down checkbox wider

Is there a way to make my drop-down checkbox wider? I have a label that is a
concatenation of several fields and I'd like the user to be able to see all
of the data in a row.
Thanks!
StephanieOn May 7, 2:05 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> Is there a way to make my drop-down checkbox wider? I have a label that is a
> concatenation of several fields and I'd like the user to be able to see all
> of the data in a row.
> Thanks!
> Stephanie
If you are referring to the multi-select parameter drop-down control,
I'm afraid not. Sorry that I could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Make a validate rule in a table

Hi

In access i can make a rule
like if i have a Coloumn to date
i can make a rule to say that this fields data
shall be > date

can i do this also in sql and how?

regards

alvin

You can create a Check Constraint on a column in a table. Check Constraints can compare a column's value to other values within the same row or to functions, but cannot compare to other rows.

In your example, are you asking how to compare a column named to_date to one called date? Or are you asking how to compare to_date to the value of the curent date returned from a function? There are some wrinkles with the latter, since a row that passes today, might be invalid tomorrow. The constraint will be re-evaluated if the row is modified, even if it is not the to_date field that is changed.

Constraints can be defined in the Create Table statement:

Create Table myTable(

from_date datetime Check( from_date < GetDate() ), -- Unnamed constraint using a function

to_date datetime

Constraint Range_Check Check( to_date > from_date ) -- Named table constraint comparing two columns

)

You can add Constraints with Alter Table as well. See the Books-online for more information.

Alter Table myTable Add Constraint Min_Range Check( DateDiff( dd, from_date, to_date ) > 10 )

Make a document repository application with blob fields?

Hello, my company is thinking in make a document repository application and I have read many times that its not recommended to save the files to the database, because it adds ovverhead to other users that are using transactions or something else.

I need to convince my boss that its a bad practice to put the files on BLOB fields inside the database.

Any ideas!!

Well, my first question would be "how would you plan on getting them OUT of the database?". That is always the problem with BLOBs.

In order to display the "file" from the BLOB, you will need to convert it back to a file. So just keep it a file, and use the database to index to it. If you are concerned about security, then create a process which copies the file from a secure area to a place the user can get it, and then delete it when done.

Major Problems with Calculated Fields

Is anyone else having problems with Calculated Members? Everything was working fine with viewing the cubes in the browser, in Reporting Services, and via Pivot tables...but once I put in some simple calculated members (simple parallel period functions), query times are extremely slow, everything locks up,unexpected Visual Studio crashes occur...SSAS pretty much becomes unusable.

Anyone with similar experiences?

You should see some performance improvements in SP1.

In limited number of cases you can use NON_EMPTY_BEHAVIOR when defining you calculated member. You got to be careful with it for parallel period such that you might not have a value for certain measure in parallel period. But if you can guarantee that, you should see way better performance.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Do you know if this is a problem with SSAS, Reporting Services, or the OLAP 9.0 Provider? I seem to be having the same problem when using Reporting Services 2000 and the 9.0 Provider. If I can't report on SSAS 2005 easily, I simply can't upgrade until the issues are resolved.

major bug in SQL 2000

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

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

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

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

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

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.
>.
>

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
>