table hosed: bug

Showing posts with label bug. Show all posts

Wednesday, March 28, 2012

Is there a line feed bug in QA?

Hi,
I am having a heck of a problem on 2 machines (2k & XPPro). SQLServer
2000SP4
Query Analyzer keeps giving me problems and I lose hours tracking down the
problem.
Now I don't know exactly what QA is doing, but I do know that QA will show a
line break, but the proc is behaving as if the line break is not there.
For example, this will not work process the line following the comment;
-- comment
Exec sp_dosomething
But this will
/* comment */
Exec sp_dosomething
Does anybody know of a solution for this?
Thanks,
John
You might want to try find one CHAR(13) or only CHAR(10). A proper line break is CHAR(13) +
CHAR(10), aka CRLF. I recall some natyiness if you only had one of those and not the other, where QA
would display as line break but it didn't behave like a linebreak.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John MacIntyre" <pls@.reply.here.com> wrote in message news:eMdmLv9wFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am having a heck of a problem on 2 machines (2k & XPPro). SQLServer 2000SP4
> Query Analyzer keeps giving me problems and I lose hours tracking down the problem.
> Now I don't know exactly what QA is doing, but I do know that QA will show a line break, but the
> proc is behaving as if the line break is not there.
> For example, this will not work process the line following the comment;
> -- comment
> Exec sp_dosomething
> But this will
> /* comment */
> Exec sp_dosomething
> Does anybody know of a solution for this?
> Thanks,
> John
>
|||Hi John,
I had this same problem, where my script would never enter a while loop
because it thought it was a comment.
All i did in the end was delete all white space near the affected area, so
that all the text is joined, then just add the line breaks back in manually.
Ben
"John MacIntyre" <pls@.reply.here.com> wrote in message
news:eMdmLv9wFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am having a heck of a problem on 2 machines (2k & XPPro). SQLServer
> 2000SP4
> Query Analyzer keeps giving me problems and I lose hours tracking down the
> problem.
> Now I don't know exactly what QA is doing, but I do know that QA will show
> a line break, but the proc is behaving as if the line break is not there.
> For example, this will not work process the line following the comment;
> -- comment
> Exec sp_dosomething
> But this will
> /* comment */
> Exec sp_dosomething
> Does anybody know of a solution for this?
> Thanks,
> John
>
|||Hi
If you open the script in an editor such as textpad you may see some
non-printing character(s).
John
"John MacIntyre" wrote:

> Hi,
> I am having a heck of a problem on 2 machines (2k & XPPro). SQLServer
> 2000SP4
> Query Analyzer keeps giving me problems and I lose hours tracking down the
> problem.
> Now I don't know exactly what QA is doing, but I do know that QA will show a
> line break, but the proc is behaving as if the line break is not there.
> For example, this will not work process the line following the comment;
> -- comment
> Exec sp_dosomething
> But this will
> /* comment */
> Exec sp_dosomething
> Does anybody know of a solution for this?
> Thanks,
> John
>
>
sql

Is there a line feed bug in QA?

Hi,
I am having a heck of a problem on 2 machines (2k & XPPro). SQLServer
2000SP4
Query Analyzer keeps giving me problems and I lose hours tracking down the
problem.
Now I don't know exactly what QA is doing, but I do know that QA will show a
line break, but the proc is behaving as if the line break is not there.
For example, this will not work process the line following the comment;
-- comment
Exec sp_dosomething
But this will
/* comment */
Exec sp_dosomething
Does anybody know of a solution for this?
Thanks,
JohnYou might want to try find one CHAR(13) or only CHAR(10). A proper line brea
k is CHAR(13) +
CHAR(10), aka CRLF. I recall some natyiness if you only had one of those and
not the other, where QA
would display as line break but it didn't behave like a linebreak.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John MacIntyre" <pls@.reply.here.com> wrote in message news:eMdmLv9wFHA.2540@.TK2MSFTNGP09.p
hx.gbl...
> Hi,
> I am having a heck of a problem on 2 machines (2k & XPPro). SQLServer 200
0SP4
> Query Analyzer keeps giving me problems and I lose hours tracking down the
problem.
> Now I don't know exactly what QA is doing, but I do know that QA will show
a line break, but the
> proc is behaving as if the line break is not there.
> For example, this will not work process the line following the comment;
> -- comment
> Exec sp_dosomething
> But this will
> /* comment */
> Exec sp_dosomething
> Does anybody know of a solution for this?
> Thanks,
> John
>|||Hi John,
I had this same problem, where my script would never enter a while loop
because it thought it was a comment.
All i did in the end was delete all white space near the affected area, so
that all the text is joined, then just add the line breaks back in manually.
Ben
"John MacIntyre" <pls@.reply.here.com> wrote in message
news:eMdmLv9wFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am having a heck of a problem on 2 machines (2k & XPPro). SQLServer
> 2000SP4
> Query Analyzer keeps giving me problems and I lose hours tracking down the
> problem.
> Now I don't know exactly what QA is doing, but I do know that QA will show
> a line break, but the proc is behaving as if the line break is not there.
> For example, this will not work process the line following the comment;
> -- comment
> Exec sp_dosomething
> But this will
> /* comment */
> Exec sp_dosomething
> Does anybody know of a solution for this?
> Thanks,
> John
>|||Hi
If you open the script in an editor such as textpad you may see some
non-printing character(s).
John
"John MacIntyre" wrote:

> Hi,
> I am having a heck of a problem on 2 machines (2k & XPPro). SQLServer
> 2000SP4
> Query Analyzer keeps giving me problems and I lose hours tracking down the
> problem.
> Now I don't know exactly what QA is doing, but I do know that QA will show
a
> line break, but the proc is behaving as if the line break is not there.
> For example, this will not work process the line following the comment;
> -- comment
> Exec sp_dosomething
> But this will
> /* comment */
> Exec sp_dosomething
> Does anybody know of a solution for this?
> Thanks,
> John
>
>

Monday, March 26, 2012

Is there a known NOLOCK bug?

NOLOCK locking hint and SET TRANSACTION ISOLATION LEVEL UNCOMMITTED
should be both semantically the same - dirty read.
However, I have heard that because of a bug, SQL Server ignores SET command
and produces a high number of database locks. NOLOCK hint is supposed to
work all the time.
As a result of this bug, it is possible to reduce the number locks by
replacing SET to NOLOCK.
Has anybody seen this behavior?
Are there any cases when SET does not work but NOLOCK does? What are the
conditions when this occurs?
If this is a bug, was it fixed?
Can somebody point me to a KB article?
I really want to find an answer for this before I start changing hundreds of
stored procedures...
Thanks,
-Stan> However, I have heard that because of a bug, SQL Server ignores SET
command
Where did you "hear" this? Did they provide a repro?
I have not seen or heard this, but it's certainly possible... if you can
provide a repro, or give us the source of where you saw this information, it
can probably be refuted more easily. Tough to disprove something
hypethetical...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I actually found something:
PSS ID Number: 171322
Q171322
BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc
Has it been fixed?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4Jbdh$1EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > However, I have heard that because of a bug, SQL Server ignores SET
> command
> Where did you "hear" this? Did they provide a repro?
> I have not seen or heard this, but it's certainly possible... if you can
> provide a repro, or give us the source of where you saw this information,
it
> can probably be refuted more easily. Tough to disprove something
> hypethetical...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||This looks like it only affects SQL Server 6.5. Are you using SQL Server
6.5?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Stan" <nospam@.yahoo.com> wrote in message
news:emnpck$1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> I actually found something:
> PSS ID Number: 171322
> Q171322
> BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc
> Has it been fixed?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:e4Jbdh$1EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > However, I have heard that because of a bug, SQL Server ignores SET
> > command
> >
> > Where did you "hear" this? Did they provide a repro?
> >
> > I have not seen or heard this, but it's certainly possible... if you can
> > provide a repro, or give us the source of where you saw this
information,
> it
> > can probably be refuted more easily. Tough to disprove something
> > hypethetical...
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
>|||No, 2000
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uC4%23mn$1EHA.3120@.TK2MSFTNGP12.phx.gbl...
> This looks like it only affects SQL Server 6.5. Are you using SQL Server
> 6.5?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Stan" <nospam@.yahoo.com> wrote in message
> news:emnpck$1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> > I actually found something:
> >
> > PSS ID Number: 171322
> > Q171322
> >
> > BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc
> >
> > Has it been fixed?
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:e4Jbdh$1EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > > However, I have heard that because of a bug, SQL Server ignores SET
> > > command
> > >
> > > Where did you "hear" this? Did they provide a repro?
> > >
> > > I have not seen or heard this, but it's certainly possible... if you
can
> > > provide a repro, or give us the source of where you saw this
> information,
> > it
> > > can probably be refuted more easily. Tough to disprove something
> > > hypethetical...
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> >
> >
>|||Then you don't have to worry about it.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Stan" <nospam@.yahoo.com> wrote in message
news:OxXHE8G2EHA.1124@.tk2msftngp13.phx.gbl...
> No, 2000
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uC4%23mn$1EHA.3120@.TK2MSFTNGP12.phx.gbl...
> > This looks like it only affects SQL Server 6.5. Are you using SQL
Server
> > 6.5?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:emnpck$1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> > > I actually found something:
> > >
> > > PSS ID Number: 171322
> > > Q171322
> > >
> > > BUG: "SET TRANSACTION ISOLATION LEVEL" Ignored in Stored Proc
> > >
> > > Has it been fixed?
> > >
> > > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > > news:e4Jbdh$1EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > > > > However, I have heard that because of a bug, SQL Server ignores
SET
> > > > command
> > > >
> > > > Where did you "hear" this? Did they provide a repro?
> > > >
> > > > I have not seen or heard this, but it's certainly possible... if you
> can
> > > > provide a repro, or give us the source of where you saw this
> > information,
> > > it
> > > > can probably be refuted more easily. Tough to disprove something
> > > > hypethetical...
> > > >
> > > > --
> > > > http://www.aspfaq.com/
> > > > (Reverse address to reply.)
> > > >
> > > >
> > >
> > >
> >
> >
>

Is there a bug with Views and Select *?

Hi,
I added some new columns to a table and they do not show up in a View that
references the table "*" selector. If I go into Enterprise Manager and
re-save the view they show up. Has anyone else experienced this problem?
The syntax for the view is :
Select Table1.itemID, Table1.Value, Table2.* where
Table1.ItemID=Table2.ItemID
The new columns are in Table2.
Thanks,
Jerry
Hi,
This is not a bug, this is by design.
If you create a view and then modify the underlying table, the view
definition is not automatically updated. How will a view know if the changes
are also meant for the view definition? Your view could potentially have less
columns than the base table.
If you change the underlying tables, its always best to make sure that your
view's definition is not effected. If it is, you must re-create the view.
Actually, you are re-saving the view definition when you are re-saving the
view.
hth
DeeJay
"JerryK" wrote:

> Hi,
> I added some new columns to a table and they do not show up in a View that
> references the table "*" selector. If I go into Enterprise Manager and
> re-save the view they show up. Has anyone else experienced this problem?
> The syntax for the view is :
> Select Table1.itemID, Table1.Value, Table2.* where
> Table1.ItemID=Table2.ItemID
> The new columns are in Table2.
> Thanks,
> Jerry
>
>
>
|||Jerry,
When a view is created, the name of the view is stored in the sysobjects
table. Information about the columns defined in a view is added to the
syscolumns table, and information about the view dependencies is added to
the sysdepends table. In addition, the text of the CREATE VIEW statement is
added to the syscomments table.
So, when you added the column to the Table2, information in syscolumns for
the view has not been changed. That's why you need to "recompile" (reapply)
the view. The same happens when you drop the column from the Table2. Any
reference to the view produces an error until you reapply the view.
Thanks
Oleg
Message posted via http://www.sqlmonster.com
|||> I added some new columns to a table and they do not show up in a View that
> references the table "*" selector.
Why are you using SELECT *?

> Has anyone else experienced this problem?
It's not a problem, per se; it's actually documented.
You can get around this by actually naming your columns, or using WITH
SCHEMABINDING when you create the view. The former keeps views consistent
with interfaces to them until you have time to attend to both; the latter
forces you to recognize all of the views you will need to change when
considering a change to a base table.
In the meantime, you can issue sp_refreshview 'viewname' instead of using
Enterprise Mangler...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

Friday, March 23, 2012

Is there a bug with Views and Select *?

Hi,
I added some new columns to a table and they do not show up in a View that
references the table "*" selector. If I go into Enterprise Manager and
re-save the view they show up. Has anyone else experienced this problem?
The syntax for the view is :
Select Table1.itemID, Table1.Value, Table2.* where
Table1.ItemID=Table2.ItemID
The new columns are in Table2.
Thanks,
JerryHi,
This is not a bug, this is by design.
If you create a view and then modify the underlying table, the view
definition is not automatically updated. How will a view know if the changes
are also meant for the view definition? Your view could potentially have les
s
columns than the base table.
If you change the underlying tables, its always best to make sure that your
view's definition is not effected. If it is, you must re-create the view.
Actually, you are re-saving the view definition when you are re-saving the
view.
hth
DeeJay
"JerryK" wrote:

> Hi,
> I added some new columns to a table and they do not show up in a View that
> references the table "*" selector. If I go into Enterprise Manager and
> re-save the view they show up. Has anyone else experienced this problem?
> The syntax for the view is :
> Select Table1.itemID, Table1.Value, Table2.* where
> Table1.ItemID=Table2.ItemID
> The new columns are in Table2.
> Thanks,
> Jerry
>
>
>|||Jerry,
When a view is created, the name of the view is stored in the sysobjects
table. Information about the columns defined in a view is added to the
syscolumns table, and information about the view dependencies is added to
the sysdepends table. In addition, the text of the CREATE VIEW statement is
added to the syscomments table.
So, when you added the column to the Table2, information in syscolumns for
the view has not been changed. That's why you need to "recompile" (reapply)
the view. The same happens when you drop the column from the Table2. Any
reference to the view produces an error until you reapply the view.
Thanks
Oleg
Message posted via http://www.droptable.com|||> I added some new columns to a table and they do not show up in a View that
> references the table "*" selector.
Why are you using SELECT *?

> Has anyone else experienced this problem?
It's not a problem, per se; it's actually documented.
You can get around this by actually naming your columns, or using WITH
SCHEMABINDING when you create the view. The former keeps views consistent
with interfaces to them until you have time to attend to both; the latter
forces you to recognize all of the views you will need to change when
considering a change to a base table.
In the meantime, you can issue sp_refreshview 'viewname' instead of using
Enterprise Mangler...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql

Is there a bug with Views and Select *?

Hi,
I added some new columns to a table and they do not show up in a View that
references the table "*" selector. If I go into Enterprise Manager and
re-save the view they show up. Has anyone else experienced this problem?
The syntax for the view is :
Select Table1.itemID, Table1.Value, Table2.* where
Table1.ItemID=Table2.ItemID
The new columns are in Table2.
Thanks,
JerryJerry,
When a view is created, the name of the view is stored in the sysobjects
table. Information about the columns defined in a view is added to the
syscolumns table, and information about the view dependencies is added to
the sysdepends table. In addition, the text of the CREATE VIEW statement is
added to the syscomments table.
So, when you added the column to the Table2, information in syscolumns for
the view has not been changed. That's why you need to "recompile" (reapply)
the view. The same happens when you drop the column from the Table2. Any
reference to the view produces an error until you reapply the view.
Thanks
Oleg
--
Message posted via http://www.sqlmonster.com|||Hi,
This is not a bug, this is by design.
If you create a view and then modify the underlying table, the view
definition is not automatically updated. How will a view know if the changes
are also meant for the view definition? Your view could potentially have less
columns than the base table.
If you change the underlying tables, its always best to make sure that your
view's definition is not effected. If it is, you must re-create the view.
Actually, you are re-saving the view definition when you are re-saving the
view.
hth
DeeJay
"JerryK" wrote:
> Hi,
> I added some new columns to a table and they do not show up in a View that
> references the table "*" selector. If I go into Enterprise Manager and
> re-save the view they show up. Has anyone else experienced this problem?
> The syntax for the view is :
> Select Table1.itemID, Table1.Value, Table2.* where
> Table1.ItemID=Table2.ItemID
> The new columns are in Table2.
> Thanks,
> Jerry
>
>
>|||> I added some new columns to a table and they do not show up in a View that
> references the table "*" selector.
Why are you using SELECT *?
> Has anyone else experienced this problem?
It's not a problem, per se; it's actually documented.
You can get around this by actually naming your columns, or using WITH
SCHEMABINDING when you create the view. The former keeps views consistent
with interfaces to them until you have time to attend to both; the latter
forces you to recognize all of the views you will need to change when
considering a change to a base table.
In the meantime, you can issue sp_refreshview 'viewname' instead of using
Enterprise Mangler...
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

Wednesday, March 21, 2012

Is the behavior of this UPDATE SQL expected or a Bug?

Hi All,
I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
following Update SQLis a bug or expected behavior?
CREATE TABLE [Table1] (
[col1] [int] NULL ,
[col2] [int] NULL
)
GO
CREATE TABLE [Table2] (
[col1] [int] NULL ,
[col3] [int] NULL
)
GO
insert into table1 (col1, col2) values (1,null)
insert into table1 (col1, col2) values (2,null)
insert into table2 (col1, col3) values (1,11)
insert into table2 (col1, col3) values (1,12)
insert into table2 (col1, col3) values (2,22)
go
-- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
-- col3 of Table2
update table1
set col2=b.col3
from table1 a, table2 b
where a.col1=b.col1
I was expecting that the above SQL should fail because in this JOIN between
two tables there are two rows fetched from table2 for first row in table1
But against to my expectations that SQL was successful and got message
(2 row(s) affected)
And first row in table1, it has updated col2 with value 12
Am I making sense?
Thanks in advance,
Vinod"VM" <VM@.discussions.microsoft.com> wrote in message
news:4F375926-71A9-4CCD-9119-7D336F7809A4@.microsoft.com...
> Hi All,
> I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
> following Update SQLis a bug or expected behavior?
> CREATE TABLE [Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> )
> GO
> CREATE TABLE [Table2] (
> [col1] [int] NULL ,
> [col3] [int] NULL
> )
> GO
> insert into table1 (col1, col2) values (1,null)
> insert into table1 (col1, col2) values (2,null)
> insert into table2 (col1, col3) values (1,11)
> insert into table2 (col1, col3) values (1,12)
> insert into table2 (col1, col3) values (2,22)
> go
> -- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
> -- col3 of Table2
> update table1
> set col2=b.col3
> from table1 a, table2 b
> where a.col1=b.col1
>
> I was expecting that the above SQL should fail because in this JOIN
> between
> two tables there are two rows fetched from table2 for first row in table1
> But against to my expectations that SQL was successful and got message
> (2 row(s) affected)
>
> And first row in table1, it has updated col2 with value 12
> Am I making sense?
> Thanks in advance,
> Vinod
>
You are absolutely right to be concerned. Unfortunately this is the expected
behaviour. Books Online:
"The results of an UPDATE statement are undefined if the statement includes
a FROM clause that is not specified in such a way that only one value is
available for each column occurrence that is updated, that is if the UPDATE
statement is not deterministic."
So you get random results by design! If you want to use the UPDATE FROM
syntax then be very sure your join returns unique rows. If in doubt you may
prefer to use the ANSI standard syntax, which does fail safe:
UPDATE table1
SET col2 =
(SELECT col3
FROM table2
WHERE col1 = table1.col1);
Result:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Vinod,
The first thing to understand about this situation is that the ANSI
standard does not allow a FROM clause in an UPDATE command. The
reason it does not is the ambiguity that you are asking about - if
there are two matching rows, which one provides the value?
The answer is that when using a FROM clause in an UPDATE, and joining
to a table with multiple rows matching a single row being updated, you
can not predict which row will contribute the value that ends up in
the matching row.
I have not tested in recent years, but at least in release 6.5 and
beyond there would have been TWO rows in the log, one for each match,
even though the "row(s) affected" would only count the row updated
once. That used to be - may still be - a great way to make the log
get really big very quickly; update OrderMaster by joining to
OrderItem, with an average of ten items per master, and log ten
updates for each row.
Hope that helps.
Roy
On Wed, 15 Feb 2006 15:55:27 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:

>Hi All,
>I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
>following Update SQLis a bug or expected behavior?
>CREATE TABLE [Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> )
>GO
>CREATE TABLE [Table2] (
> [col1] [int] NULL ,
> [col3] [int] NULL
> )
>GO
>insert into table1 (col1, col2) values (1,null)
>insert into table1 (col1, col2) values (2,null)
>insert into table2 (col1, col3) values (1,11)
>insert into table2 (col1, col3) values (1,12)
>insert into table2 (col1, col3) values (2,22)
>go
>-- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
>-- col3 of Table2
>update table1
>set col2=b.col3
>from table1 a, table2 b
>where a.col1=b.col1
>
>I was expecting that the above SQL should fail because in this JOIN between
>two tables there are two rows fetched from table2 for first row in table1
>But against to my expectations that SQL was successful and got message
>(2 row(s) affected)
>
>And first row in table1, it has updated col2 with value 12
>Am I making sense?
>Thanks in advance,
>Vinod
>|||David and Roy,
Thankyou so much for your replies. Now I have good picture about the FROM
clause in UPDATE statement. I will be very careful from now.
Again, Thanks guys!!
Vinod
"Roy Harvey" wrote:

> Vinod,
> The first thing to understand about this situation is that the ANSI
> standard does not allow a FROM clause in an UPDATE command. The
> reason it does not is the ambiguity that you are asking about - if
> there are two matching rows, which one provides the value?
> The answer is that when using a FROM clause in an UPDATE, and joining
> to a table with multiple rows matching a single row being updated, you
> can not predict which row will contribute the value that ends up in
> the matching row.
> I have not tested in recent years, but at least in release 6.5 and
> beyond there would have been TWO rows in the log, one for each match,
> even though the "row(s) affected" would only count the row updated
> once. That used to be - may still be - a great way to make the log
> get really big very quickly; update OrderMaster by joining to
> OrderItem, with an average of ten items per master, and log ten
> updates for each row.
> Hope that helps.
> Roy
>
> On Wed, 15 Feb 2006 15:55:27 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
>
>

Friday, February 24, 2012

IS NULL bug(?)

Hello,
My problem is; weird results with a IS NULL clause in a query.
I have a sql2000 dbase, table, 2 columns and the following query:
dt_column = type: datetime
computed: no
length:8
nullable: no
TrimTrailingBlanks:n/a
FixedLenNullnSource:n/a
Collation: null
vc_column = type:varchar
computed: no
length:15
nullable: yes
TrimTrailingBlanks:no
FixedLenNullnSource: no
Collation: SQL_Latin1_General_CP1_CI_AS
q1: select dt_column,vc_colomn from mytable where vc_column is null
->result; >20.000 records where vc_column indeed is null
but, if i expand the query with a date clause like so:
q2: select dt_column,vc_column from mytable where vc_column is null and
year(dt_column) = '2006'
->result; <20.000 records where SOME of the results DO have a value for
the vc_column!
This is strange isnt it ?
I already have a solution to overcome this; but I do not see why i have
to do this:
When I cast the vc_column to a varchar like so:
q3: select dt_column,vc_column from mytable where cast(vc_column as
varchar) and year(dt_column) ='2006'
->the result is good! only null values for the vc_column !
but why do i have to cast a varchar column to a varchar before the is
null will work '?
Thanks very much,
JeroenThis sounds a bit familiar. I ran into a similar problem where unexpected
results were returned from a query (that was a bit more complex than the one
you posted).
What version of SQL Server 2000 are you running?
execute this within Query Analyzer:
SELECT @.@.version
and compare the results to the build list here:
http://www.aspfaq.com/sql2000Builds.asp
In our case we experienced the error on SP4 (SP3 worked fine). Applying the
.2187 hotfix solved our problem. Do you have a test system that you can use
to reproduce the probem and test various patches to see if they solve your
problem?
Keith Kratochvil
"Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
> Hello,
> My problem is; weird results with a IS NULL clause in a query.
> I have a sql2000 dbase, table, 2 columns and the following query:
> dt_column => type: datetime
> computed: no
> length:8
> nullable: no
> TrimTrailingBlanks:n/a
> FixedLenNullnSource:n/a
> Collation: null
> vc_column => type:varchar
> computed: no
> length:15
> nullable: yes
> TrimTrailingBlanks:no
> FixedLenNullnSource: no
> Collation: SQL_Latin1_General_CP1_CI_AS
> q1: select dt_column,vc_colomn from mytable where vc_column is null
> ->result; >20.000 records where vc_column indeed is null
> but, if i expand the query with a date clause like so:
> q2: select dt_column,vc_column from mytable where vc_column is null and
> year(dt_column) = '2006'
> ->result; <20.000 records where SOME of the results DO have a value for
> the vc_column!
> This is strange isnt it ?
> I already have a solution to overcome this; but I do not see why i have
> to do this:
> When I cast the vc_column to a varchar like so:
> q3: select dt_column,vc_column from mytable where cast(vc_column as
> varchar) and year(dt_column) ='2006'
> ->the result is good! only null values for the vc_column !
> but why do i have to cast a varchar column to a varchar before the is
> null will work '?
> Thanks very much,
> Jeroen
>|||Hi Keith,
Thanks for the quick reply;
The version of sql server is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
hotfixes available for this release...?
(seems to stop at build 2187)...
Maybe the 2187 hotfix works here to; I'll test; thanks again.
Jeroen
Keith Kratochvil wrote:
> This sounds a bit familiar. I ran into a similar problem where unexpected
> results were returned from a query (that was a bit more complex than the one
> you posted).
> What version of SQL Server 2000 are you running?
> execute this within Query Analyzer:
> SELECT @.@.version
> and compare the results to the build list here:
> http://www.aspfaq.com/sql2000Builds.asp
> In our case we experienced the error on SP4 (SP3 worked fine). Applying the
> .2187 hotfix solved our problem. Do you have a test system that you can use
> to reproduce the probem and test various patches to see if they solve your
> problem?
>
> --
> Keith Kratochvil
>
> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
> > Hello,
> >
> > My problem is; weird results with a IS NULL clause in a query.
> >
> > I have a sql2000 dbase, table, 2 columns and the following query:
> > dt_column => > type: datetime
> > computed: no
> > length:8
> > nullable: no
> > TrimTrailingBlanks:n/a
> > FixedLenNullnSource:n/a
> > Collation: null
> >
> > vc_column => > type:varchar
> > computed: no
> > length:15
> > nullable: yes
> > TrimTrailingBlanks:no
> > FixedLenNullnSource: no
> > Collation: SQL_Latin1_General_CP1_CI_AS
> >
> > q1: select dt_column,vc_colomn from mytable where vc_column is null
> > ->result; >20.000 records where vc_column indeed is null
> >
> > but, if i expand the query with a date clause like so:
> > q2: select dt_column,vc_column from mytable where vc_column is null and
> > year(dt_column) = '2006'
> > ->result; <20.000 records where SOME of the results DO have a value for
> > the vc_column!
> >
> > This is strange isnt it ?
> >
> > I already have a solution to overcome this; but I do not see why i have
> > to do this:
> > When I cast the vc_column to a varchar like so:
> > q3: select dt_column,vc_column from mytable where cast(vc_column as
> > varchar) and year(dt_column) ='2006'
> > ->the result is good! only null values for the vc_column !
> > but why do i have to cast a varchar column to a varchar before the is
> > null will work '?
> >
> > Thanks very much,
> > Jeroen
> >|||Hi
You are currently on service pack 3a, you can apply service pack 4
http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
followed by the subsequent hot fixes. I would recommend at least going to
build 2040. This is the link for the 2187 download:
http://www.microsoft.com/downloads/details.aspx?familyid=9C9AB140-BDEE-44DF-B7A3-E6849297754A&displaylang=en
John
"Shuwi" wrote:
> Hi Keith,
> Thanks for the quick reply;
> The version of sql server is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
> hotfixes available for this release...?
> (seems to stop at build 2187)...
> Maybe the 2187 hotfix works here to; I'll test; thanks again.
> Jeroen
> Keith Kratochvil wrote:
> > This sounds a bit familiar. I ran into a similar problem where unexpected
> > results were returned from a query (that was a bit more complex than the one
> > you posted).
> >
> > What version of SQL Server 2000 are you running?
> > execute this within Query Analyzer:
> > SELECT @.@.version
> > and compare the results to the build list here:
> > http://www.aspfaq.com/sql2000Builds.asp
> >
> > In our case we experienced the error on SP4 (SP3 worked fine). Applying the
> > .2187 hotfix solved our problem. Do you have a test system that you can use
> > to reproduce the probem and test various patches to see if they solve your
> > problem?
> >
> >
> > --
> > Keith Kratochvil
> >
> >
> > "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> > news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
> > > Hello,
> > >
> > > My problem is; weird results with a IS NULL clause in a query.
> > >
> > > I have a sql2000 dbase, table, 2 columns and the following query:
> > > dt_column => > > type: datetime
> > > computed: no
> > > length:8
> > > nullable: no
> > > TrimTrailingBlanks:n/a
> > > FixedLenNullnSource:n/a
> > > Collation: null
> > >
> > > vc_column => > > type:varchar
> > > computed: no
> > > length:15
> > > nullable: yes
> > > TrimTrailingBlanks:no
> > > FixedLenNullnSource: no
> > > Collation: SQL_Latin1_General_CP1_CI_AS
> > >
> > > q1: select dt_column,vc_colomn from mytable where vc_column is null
> > > ->result; >20.000 records where vc_column indeed is null
> > >
> > > but, if i expand the query with a date clause like so:
> > > q2: select dt_column,vc_column from mytable where vc_column is null and
> > > year(dt_column) = '2006'
> > > ->result; <20.000 records where SOME of the results DO have a value for
> > > the vc_column!
> > >
> > > This is strange isnt it ?
> > >
> > > I already have a solution to overcome this; but I do not see why i have
> > > to do this:
> > > When I cast the vc_column to a varchar like so:
> > > q3: select dt_column,vc_column from mytable where cast(vc_column as
> > > varchar) and year(dt_column) ='2006'
> > > ->the result is good! only null values for the vc_column !
> > > but why do i have to cast a varchar column to a varchar before the is
> > > null will work '?
> > >
> > > Thanks very much,
> > > Jeroen
> > >
>|||Don't be confused by "Service Pack 4" in the results. That is the WINDOWS
service pack, not the SQL Server Service pack.
Here is another select statement that returns database service pack level:
select serverproperty('productlevel')
By the way,
.760 is SP3a
There are a few publically available hotfixes for SP3:
.818
.878
You could upgrade to SP4 (version 2039)
There are two publically available hotfixes for SP4: 2040 and 2187
--
Keith Kratochvil
"Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
news:1160056347.136146.322120@.h48g2000cwc.googlegroups.com...
> Hi Keith,
> Thanks for the quick reply;
> The version of sql server is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
> hotfixes available for this release...?
> (seems to stop at build 2187)...
> Maybe the 2187 hotfix works here to; I'll test; thanks again.
> Jeroen
> Keith Kratochvil wrote:
>> This sounds a bit familiar. I ran into a similar problem where
>> unexpected
>> results were returned from a query (that was a bit more complex than the
>> one
>> you posted).
>> What version of SQL Server 2000 are you running?
>> execute this within Query Analyzer:
>> SELECT @.@.version
>> and compare the results to the build list here:
>> http://www.aspfaq.com/sql2000Builds.asp
>> In our case we experienced the error on SP4 (SP3 worked fine). Applying
>> the
>> .2187 hotfix solved our problem. Do you have a test system that you can
>> use
>> to reproduce the probem and test various patches to see if they solve
>> your
>> problem?
>>
>> --
>> Keith Kratochvil
>>
>> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
>> news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
>> > Hello,
>> >
>> > My problem is; weird results with a IS NULL clause in a query.
>> >
>> > I have a sql2000 dbase, table, 2 columns and the following query:
>> > dt_column =>> > type: datetime
>> > computed: no
>> > length:8
>> > nullable: no
>> > TrimTrailingBlanks:n/a
>> > FixedLenNullnSource:n/a
>> > Collation: null
>> >
>> > vc_column =>> > type:varchar
>> > computed: no
>> > length:15
>> > nullable: yes
>> > TrimTrailingBlanks:no
>> > FixedLenNullnSource: no
>> > Collation: SQL_Latin1_General_CP1_CI_AS
>> >
>> > q1: select dt_column,vc_colomn from mytable where vc_column is null
>> > ->result; >20.000 records where vc_column indeed is null
>> >
>> > but, if i expand the query with a date clause like so:
>> > q2: select dt_column,vc_column from mytable where vc_column is null and
>> > year(dt_column) = '2006'
>> > ->result; <20.000 records where SOME of the results DO have a value for
>> > the vc_column!
>> >
>> > This is strange isnt it ?
>> >
>> > I already have a solution to overcome this; but I do not see why i have
>> > to do this:
>> > When I cast the vc_column to a varchar like so:
>> > q3: select dt_column,vc_column from mytable where cast(vc_column as
>> > varchar) and year(dt_column) ='2006'
>> > ->the result is good! only null values for the vc_column !
>> > but why do i have to cast a varchar column to a varchar before the is
>> > null will work '?
>> >
>> > Thanks very much,
>> > Jeroen
>> >
>|||Thanks all;
Seems I have some upgrading to do...
Kind regards,
Jeroen
Keith Kratochvil wrote:
> Don't be confused by "Service Pack 4" in the results. That is the WINDOWS
> service pack, not the SQL Server Service pack.
> Here is another select statement that returns database service pack level:
> select serverproperty('productlevel')
> By the way,
> .760 is SP3a
> There are a few publically available hotfixes for SP3:
> .818
> .878
> You could upgrade to SP4 (version 2039)
> There are two publically available hotfixes for SP4: 2040 and 2187
> --
> Keith Kratochvil
>
> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> news:1160056347.136146.322120@.h48g2000cwc.googlegroups.com...
> > Hi Keith,
> >
> > Thanks for the quick reply;
> > The version of sql server is:
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> > 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> > Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> > on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
> > hotfixes available for this release...?
> > (seems to stop at build 2187)...
> >
> > Maybe the 2187 hotfix works here to; I'll test; thanks again.
> >
> > Jeroen
> > Keith Kratochvil wrote:
> >> This sounds a bit familiar. I ran into a similar problem where
> >> unexpected
> >> results were returned from a query (that was a bit more complex than the
> >> one
> >> you posted).
> >>
> >> What version of SQL Server 2000 are you running?
> >> execute this within Query Analyzer:
> >> SELECT @.@.version
> >> and compare the results to the build list here:
> >> http://www.aspfaq.com/sql2000Builds.asp
> >>
> >> In our case we experienced the error on SP4 (SP3 worked fine). Applying
> >> the
> >> .2187 hotfix solved our problem. Do you have a test system that you can
> >> use
> >> to reproduce the probem and test various patches to see if they solve
> >> your
> >> problem?
> >>
> >>
> >> --
> >> Keith Kratochvil
> >>
> >>
> >> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> >> news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
> >> > Hello,
> >> >
> >> > My problem is; weird results with a IS NULL clause in a query.
> >> >
> >> > I have a sql2000 dbase, table, 2 columns and the following query:
> >> > dt_column => >> > type: datetime
> >> > computed: no
> >> > length:8
> >> > nullable: no
> >> > TrimTrailingBlanks:n/a
> >> > FixedLenNullnSource:n/a
> >> > Collation: null
> >> >
> >> > vc_column => >> > type:varchar
> >> > computed: no
> >> > length:15
> >> > nullable: yes
> >> > TrimTrailingBlanks:no
> >> > FixedLenNullnSource: no
> >> > Collation: SQL_Latin1_General_CP1_CI_AS
> >> >
> >> > q1: select dt_column,vc_colomn from mytable where vc_column is null
> >> > ->result; >20.000 records where vc_column indeed is null
> >> >
> >> > but, if i expand the query with a date clause like so:
> >> > q2: select dt_column,vc_column from mytable where vc_column is null and
> >> > year(dt_column) = '2006'
> >> > ->result; <20.000 records where SOME of the results DO have a value for
> >> > the vc_column!
> >> >
> >> > This is strange isnt it ?
> >> >
> >> > I already have a solution to overcome this; but I do not see why i have
> >> > to do this:
> >> > When I cast the vc_column to a varchar like so:
> >> > q3: select dt_column,vc_column from mytable where cast(vc_column as
> >> > varchar) and year(dt_column) ='2006'
> >> > ->the result is good! only null values for the vc_column !
> >> > but why do i have to cast a varchar column to a varchar before the is
> >> > null will work '?
> >> >
> >> > Thanks very much,
> >> > Jeroen
> >> >
> >

Wednesday, March 28, 2012

Is there a line feed bug in QA?

Is there a line feed bug in QA?

Monday, March 26, 2012

Is there a known NOLOCK bug?

Is there a known NOLOCK bug?

Is there a known NOLOCK bug?

Is there a bug with Views and Select *?

Friday, March 23, 2012

Is there a bug with Views and Select *?

Is there a bug with Views and Select *?

Wednesday, March 21, 2012

Is the behavior of this UPDATE SQL expected or a Bug?

Friday, February 24, 2012

IS NULL bug(?)

IS NULL bug(?)

IS NULL bug(?)

table hosed

Blog Archive

About Me