Boot.ini file: int

Showing posts with label int. Show all posts

Thursday, March 22, 2012

Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
--
markusThat looks like a bug with ISNULL; I switched it to use COALESCE instead and
it fixed the problem...
Hopefully someone reading this knows how to submit a SQL bug report to MS?
"mark" <wimark@.smsNOSAPM.at> wrote in message
news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> Hi,
> there is a small test case (SQL Server 2000 - 8.00.760):
> -- create a small table
> create table test1 (nr int null)
> insert into test1 values (1)
> insert into test1 values (2)
> -- now create a view
> create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> If you perform this select, the isnull_jn col is always 'J',
> although it should be 'N' in the second row:
> select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn isnull_jn
> -- -- --
> 1 J J
> 2 NULL J <<<=== error, should be N!
>
> If you redefine the query to:
> select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn case_jn
> -- -- --
> 1 J J
> 2 NULL N <<== ok!
> everything works fine.
> Any suggestions?
> --
> markus
>|||ISNULL is kind of notoriously NOT the same thing as COALESCE and CASE, so I
was skeptical that this was really a bug. But below is a repro which uses
ISNULL with a view and an "inline view" or derived table.
Now despite any differences between INSULL and COALESCE, these 2 queries
should return the same data. They do not.
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview as select nr, 'J' jn from test1 where nr = 1
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join (select nr, 'J' jn from test1 where nr = 1) testview
on test1.nr = testview.nr
where test1.nr=2
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join testview
on test1.nr = testview.nr
where test1.nr=2
--
nr jn isnull_jn
2 N
(1 row(s) affected)
nr jn isnull_jn
2 J
(1 row(s) affected)
David
Should definitely return the same data
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ebxMLqvJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> That looks like a bug with ISNULL; I switched it to use COALESCE instead
and
> it fixed the problem...
> Hopefully someone reading this knows how to submit a SQL bug report to MS?
>
> "mark" <wimark@.smsNOSAPM.at> wrote in message
> news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > there is a small test case (SQL Server 2000 - 8.00.760):
> >
> > -- create a small table
> > create table test1 (nr int null)
> > insert into test1 values (1)
> > insert into test1 values (2)
> > -- now create a view
> > create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> > If you perform this select, the isnull_jn col is always 'J',
> > although it should be 'N' in the second row:
> > select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn isnull_jn
> > -- -- --
> > 1 J J
> > 2 NULL J <<<=== error, should be N!
> >
> >
> > If you redefine the query to:
> > select test1.nr, jn, case when jn is null then 'N' else jn end as
> 'case_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn case_jn
> > -- -- --
> > 1 J J
> > 2 NULL N <<== ok!
> > everything works fine.
> >
> > Any suggestions?
> > --
> > markus
> >
> >
>|||On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.
>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
--
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.4ax.com...
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
> >-- create a small table
> >create table test1 (nr int null)
> >insert into test1 values (1)
> >insert into test1 values (2)
> >-- now create a view
> >create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> >If you perform this select, the isnull_jn col is always 'J',
> >although it should be 'N' in the second row:
> >select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn isnull_jn
> >-- -- --
> >1 J J
> >2 NULL J <<<=== error, should be N!
> >
> >
> >If you redefine the query to:
> >select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn case_jn
> >-- -- --
> >1 J J
> >2 NULL N <<== ok!
> >everything works fine.
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
> >
> >Any suggestions?
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:
>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Bug?: left outer join in combination with a view

Tuesday, March 20, 2012

BUG: Internal Server Error

Hi all!
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
--
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Roman S. Golubin" wrote:

> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not nul
l
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>|||Hi, Chandra!

> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username

BUG: Internal Server Error

Hi all!
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?
hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Roman S. Golubin" wrote:

> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not null
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>
|||Hi, Chandra!

> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username

Sunday, March 11, 2012

Bug in UPDATE statement and 1 : n cardinality ?

The problem is in one-to-many cardinality and UPDATE statement :
( SQL server 2005 developer edition )

create table #table1( id int, firstCol int)
insert #table1 values ( 1,0)
insert #table1 values ( 2,0)
go

create table #table2( id int, secondCol int)
insert #table2 values ( 1,10)
insert #table2 values ( 1,20)
insert #table2 values ( 1,30)
insert #table2 values ( 3,100)
go

1. shape UPDATE

update #table1 set firstCol = firstCol +( select secondCol from #table2
where #table1.id = #table2.id )

return error message :

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.

2. shape UPDATE

update r set firstCol = firstCol + s.secondCol
from #table1 r
join ( select id,secondCol from #table2 ) s
on r.id = s.id

return message : (1 row(s) affected)
and #table1 is updated only one of row ( generally random ) from #table2 for ID = 1

select * from #table1

id firstCol
-- --
1 10
2 0

3. shape UPDATE

update #table1 set firstCol = firstCol + secondCol
from #table2 where #table1.id = #table2.id

return message : (1 row(s) affected)
and #table1 is updated only one of row ( generally random ) from #table2 for ID = 1

Is it normal ? I expected error message or summary value for ID = 1 from #table2 .

The behavior of SQL Server's proprietary UPDATE .. FROM statement

is undefined in this situation, and this is documented in Books

Online:

UPDATE (Transact-SQL)

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/40e63302-0c68-4593-af3e-6d190181fee7.htm

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.

For example, in the UPDATE statement in the following script,

both rows in Table1 meet the qualifications of the FROM clause

in the UPDATE statement; but it is undefined which row from

Table1 is used to update the row in Table2.

Tuesday, February 14, 2012

Breaking up a string column into multiple records

Hopefully someone can help me. I have a table of records with the following fields:

AnswerID (int)
MultipleChoiceMultipleAnswer (varchar)
QuestionID (int)

now the multipleChoiceMultipleAnswer field data is in the following format:

1234,5678,99867
2345,456,7891

I want to break that field up into multiple records using the same AnswerID and QuestionID for each new multiple choice answer, so it would look like this:

10001 1234 9999
10001 5678 9999
10001 99867 9999
10002 2345 9998
10002 456 9998
10002 7891 9998

Is there an optimized method of doing this without using a cursor to iterate through each record?

Any help would be greatly appreciated.

ThanksLook at the following link - if you need something else, let me know -

link (http://dbforums.com/showthread.php?threadid=586248)

Breaking a nested trigger

Hi,
I have the following 2 tables:
tbl_CustomerEmail1
(
CustomerID1 int,
EmailAddress varchar(200),
Unsubscribe bit
)
tbl_CustomerEmail2
(
CustomerID2 int,
EmailAddress varchar(200),
Unsubscribe bit
)
On each table I have a trigger to subscribe/unsubscribe a customer in the
other table if it exists:
CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
begin
update tbl_Customer1
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
and:
CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
begin
update tbl_Customer2
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
These 2 triggers fire each other to the nested limit (32) if I try to
subscribe/unsubscribe a customer which exists in both tables yet I can't
disallow the nested trigger property on the server because it may break othe
r
applications in other DB's. The return statement used doesn't work. Is there
a way I can do this without disabling the nested trigger property on the
whole server?
Many thanks for your help.Elisabeth,
If the updates that fire these triggers are never
called from yet other triggers, you should be able
to handle this by putting the following line of code
at the very beginning of each trigger:
if trigger_nestlevel() > 2 return
A better solution is probably to redesign these tables
so that there is no need to store the same information
in two separate places, but for now, using the system
function trigger_nestlevel() may take care of things.
Steve Kass
Drew University
Elisabeth wrote:

>Hi,
>I have the following 2 tables:
>tbl_CustomerEmail1
>(
>CustomerID1 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>tbl_CustomerEmail2
>(
>CustomerID2 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>On each table I have a trigger to subscribe/unsubscribe a customer in the
>other table if it exists:
>
>CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
> begin
> update tbl_Customer1
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>and:
>
>CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
> begin
> update tbl_Customer2
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>
>These 2 triggers fire each other to the nested limit (32) if I try to
>subscribe/unsubscribe a customer which exists in both tables yet I can't
>disallow the nested trigger property on the server because it may break oth
er
>applications in other DB's. The return statement used doesn't work. Is ther
e
>a way I can do this without disabling the nested trigger property on the
>whole server?
>Many thanks for your help.
>
>
>|||worked a treat! cheers mate.
"Steve Kass" wrote:

> Elisabeth,
> If the updates that fire these triggers are never
> called from yet other triggers, you should be able
> to handle this by putting the following line of code
> at the very beginning of each trigger:
> if trigger_nestlevel() > 2 return
> A better solution is probably to redesign these tables
> so that there is no need to store the same information
> in two separate places, but for now, using the system
> function trigger_nestlevel() may take care of things.
> Steve Kass
> Drew University
>
> Elisabeth wrote:
>
>

Thursday, March 22, 2012

Bug?: left outer join in combination with a view

Bug?: left outer join in combination with a view

Bug?: left outer join in combination with a view

Tuesday, March 20, 2012

BUG: Internal Server Error

BUG: Internal Server Error

Sunday, March 11, 2012

Bug in UPDATE statement and 1 : n cardinality ?

Tuesday, February 14, 2012

Breaking up a string column into multiple records

Breaking a nested trigger

Boot.ini file

Blog Archive

About Me