Posible bugs in MSDE 2000 Service packs

Currently, i am conducting some assessment to upgrade from
MSDE 1.0 to MSDE 2000. I have discover a posible bugs when
i install MSDE 2000 SP 1. When i run the below store
procedure (i execute it from custom application), it has
different result.
In MSDE 2000 without SP, the result is 40 rows, in
MSDE 2000 with SP (I have tried to install SP 1 until SP
3a),
the result is 6 rows. If i check using SQL Profiler,
both (MSDE 2000 original and MSDE 2000 with SP)
executing the following line :
exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL" @.P1 ,
@.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
(3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'.
I still can not figure it out why the same query give
different result. My assumption is, that there are
something wrong in sp_executesql Store Procedure.
To make it more obvius, here i include the store procedure
that i call.
CREATE Procedure SP1004_GET_USER_DETAIL
(
@.strUserIDVarchar(50),
@.strUserTypeVarchar(50),
@.strUserRoleVarchar(50),
@.strLocationVarchar(50),
@.strBarcodeIDVarchar(50)
)
As
Declare @.strSQL varchar(3000)
Declare @.strCondition varchar(1000)
IF (@.strUserID = 'ALL')
SET @.strCondition = 'A.USER_ID LIKE ''%'''
ELSE
SET @.strCondition = ' A.USER_ID LIKE ''%' +
@.strUserID + '%'''
IF (@.strUserType= 'ALL')
SET @.strCondition = @.strCondition + 'AND
B.USER_TYPE_NAME LIKE ''%'''
ELSE
SET @.strCondition = @.strCondition + ' AND
B.USER_TYPE_NAME LIKE ''%' + @.strUserType + '%'''
IF (@.strUserRole= 'ALL')
SET @.strCondition = @.strCondition + 'AND
C.USER_ROLE_NAME LIKE ''%'''
ELSE
SET @.strCondition = @.strCondition + ' AND
C.USER_ROLE_NAME LIKE ''%' + @.strUserRole + '%'''
IF (@.strLocation= 'ALL')
SET @.strCondition = @.strCondition + 'AND
D.LOCATION_NAME LIKE ''%'''
ELSE
SET @.strCondition = @.strCondition + ' AND
D.LOCATION_NAME LIKE ''%' + @.strLocation + '%'''
IF (@.strBarcodeID= 'ALL')
SET @.strCondition = @.strCondition + 'AND
E.BARCODE_ID LIKE ''%'''
ELSE
SET @.strCondition = @.strCondition + ' AND
D.BARCODE_ID LIKE ''%' + @.strBarcodeID + '%'''
set @.strSQL = '
SELECTA.*
FROMT_USER A
LEFT JOIN T_USER_TYPE B ON A.USER_TYPE_ID =
B.USER_TYPE_ID
LEFT JOIN T_USER_ROLE C ON A.USER_ROLE_ID =
C.USER_ROLE_ID
LEFT JOIN T_LOCATION D ON A.LOCATION_ID =
D.LOCATION_ID
LEFT JOIN T_EMPLOYEE E ON A.BARCODE_ID =
E.BARCODE_ID
WHERE'+@.strCondition
execute(@.strSQL)
return
GO
Thanks In Advance for the help.
Do queries run by an SP show up in Profiler?
| exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL" @.P1 ,
| @.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
| (3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
| N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'.
So the query should be:
SELECT A.*
FROM T_USER A
LEFT JOIN T_USER_TYPE B ON A.USER_TYPE_ID =
B.USER_TYPE_ID
LEFT JOIN T_USER_ROLE C ON A.USER_ROLE_ID =
C.USER_ROLE_ID
LEFT JOIN T_LOCATION D ON A.LOCATION_ID =
D.LOCATION_ID
LEFT JOIN T_EMPLOYEE E ON A.BARCODE_ID =
E.BARCODE_ID
WHERE
A.USER_ID LIKE ''%''
AND
B.USER_TYPE_NAME LIKE ''%''
AND
C.USER_ROLE_NAME LIKE ''%"
AND
D.LOCATION_NAME LIKE ''%''
AND
E.BARCODE_ID LIKE ''%''
What do you get if you execute that query in QA?
Carl Karsten
|||
>--Original Message--
>Do queries run by an SP show up in Profiler?
>| exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL"
@.P1 ,
>| @.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
>| (3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
>| N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'.
>So the query should be:
>SELECT A.*
> FROM T_USER A
> LEFT JOIN T_USER_TYPE B ON A.USER_TYPE_ID =
> B.USER_TYPE_ID
> LEFT JOIN T_USER_ROLE C ON A.USER_ROLE_ID =
> C.USER_ROLE_ID
> LEFT JOIN T_LOCATION D ON A.LOCATION_ID =
> D.LOCATION_ID
> LEFT JOIN T_EMPLOYEE E ON A.BARCODE_ID =
> E.BARCODE_ID
> WHERE
>A.USER_ID LIKE ''%''
>AND
>B.USER_TYPE_NAME LIKE ''%''
>AND
>C.USER_ROLE_NAME LIKE ''%"
>AND
>D.LOCATION_NAME LIKE ''%''
>AND
>E.BARCODE_ID LIKE ''%''
>
Nope, in profiler, all i see is this. :
exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL" @.P1 ,
@.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
(3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'

>What do you get if you execute that query in QA?
>Carl Karsten
>.
I run this :
exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL" @.P1 ,
@.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
(3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'
in query analyzer and i got 40 rows for MSDE 2000 original
(no Service Pack) and when i execute the query in MSDE
2000 SP 1 until SP3a, i only get 6 rows. That's why i
suspect there are bugs in sp_executesql.
Thank you for your attention.
|||Sorry, i forget to write my name.. below is my answer
[vbcol=seagreen]
>@.P1 ,
nvarchar[vbcol=seagreen]
Nope, in profiler, all i see is this. :
exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL" @.P1 ,
@.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
(3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'
[vbcol=seagreen]
I run this :
exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL" @.P1 ,
@.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
(3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'
in query analyzer and i got 40 rows for MSDE 2000 original
(no Service Pack) and when i execute the query in MSDE
2000 SP 1, SP2, even SP3a, i only get 6 rows. That's why i
suspect there are bugs in sp_executesql.
Thank you for your attention.
>.
>
|||"Abraham" <abraham_bukit-NOSPAM-@.hotmail.com> wrote in message
news:287fc01c463f3$0d89bd70$a301280a@.phx.gbl...
| Sorry, i forget to write my name.. below is my answer
|
|
| >>--Original Message (Carl Karsten)--
| >>Do queries run by an SP show up in Profiler?
| >>
| >>| exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL"
| >@.P1 ,
| >>| @.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2
| nvarchar
| >>| (3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
| >>| N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'.
| >>
| >>So the query should be:
| >>
| >>SELECT A.*
| >> FROM T_USER A
| >> LEFT JOIN T_USER_TYPE B ON A.USER_TYPE_ID =
| >> B.USER_TYPE_ID
| >> LEFT JOIN T_USER_ROLE C ON A.USER_ROLE_ID =
| >> C.USER_ROLE_ID
| >> LEFT JOIN T_LOCATION D ON A.LOCATION_ID =
| >> D.LOCATION_ID
| >> LEFT JOIN T_EMPLOYEE E ON A.BARCODE_ID =
| >> E.BARCODE_ID
| >> WHERE
| >>A.USER_ID LIKE ''%''
| >>AND
| >>B.USER_TYPE_NAME LIKE ''%''
| >>AND
| >>C.USER_ROLE_NAME LIKE ''%"
| >>AND
| >>D.LOCATION_NAME LIKE ''%''
| >>AND
| >>E.BARCODE_ID LIKE ''%''
| >>
|
| Nope, in profiler, all i see is this. :
| exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL" @.P1 ,
| @.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
| (3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
| N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'
|
| >>
| >>What do you get if you execute that query in QA?
| >>
| >>Carl Karsten
| >>.
|
| I run this :
| exec sp_executesql N' EXEC "SP1004_GET_USER_DETAIL" @.P1 ,
| @.P2 , @.P3 , @.P4 , @.P5 ', N'@.P1 nvarchar(3),@.P2 nvarchar
| (3),@.P3 nvarchar(3),@.P4 nvarchar(3),@.P5 nvarchar(3)',
| N'ALL', N'ALL', N'ALL', N'ALL', N'ALL'
| in query analyzer and i got 40 rows for MSDE 2000 original
| (no Service Pack) and when i execute the query in MSDE
| 2000 SP 1, SP2, even SP3a, i only get 6 rows. That's why i
| suspect there are bugs in sp_executesql.
| Thank you for your attention.
| >
| >.
Lets try this again...
What do you get when you run the query I posted?
|||
>--Original Message--
>"Abraham" <abraham_bukit-NOSPAM-@.hotmail.com> wrote in
message
>news:287fc01c463f3$0d89bd70$a301280a@.phx.gbl...
>| Sorry, i forget to write my name.. below is my answer
>|

>| >>So the query should be:
>| >>
>| >>SELECT A.*
>| >> FROM T_USER A
>| >> LEFT JOIN T_USER_TYPE B ON A.USER_TYPE_ID =
>| >> B.USER_TYPE_ID
>| >> LEFT JOIN T_USER_ROLE C ON A.USER_ROLE_ID =
>| >> C.USER_ROLE_ID
>| >> LEFT JOIN T_LOCATION D ON A.LOCATION_ID =
>| >> D.LOCATION_ID
>| >> LEFT JOIN T_EMPLOYEE E ON A.BARCODE_ID =
>| >> E.BARCODE_ID
>| >> WHERE
>| >>A.USER_ID LIKE ''%''
>| >>AND
>| >>B.USER_TYPE_NAME LIKE ''%''
>| >>AND
>| >>C.USER_ROLE_NAME LIKE ''%"
>| >>AND
>| >>D.LOCATION_NAME LIKE ''%''
>| >>AND
>| >>E.BARCODE_ID LIKE ''%''
>| >>
>|

>Lets try this again...
>What do you get when you run the query I posted?
>.
>
I try the query in QA and it has the same result, in MSDE
2000 original has 40 rows, and in MSDE 2000 SP 1 has 6
rows. I guess my first assumpsion is wrong, it is not
sp_executesql that need to be fixed. But does this mean
there are something wrong with left join?
|||| >| >>SELECT A.*
| >| >> FROM T_USER A
| >| >> LEFT JOIN T_USER_TYPE B ON A.USER_TYPE_ID =
| >| >> B.USER_TYPE_ID
| >| >> LEFT JOIN T_USER_ROLE C ON A.USER_ROLE_ID =
| >| >> C.USER_ROLE_ID
| >| >> LEFT JOIN T_LOCATION D ON A.LOCATION_ID =
| >| >> D.LOCATION_ID
| >| >> LEFT JOIN T_EMPLOYEE E ON A.BARCODE_ID =
| >| >> E.BARCODE_ID
| >| >> WHERE
| >| >>A.USER_ID LIKE ''%''
| >| >>AND
| >| >>B.USER_TYPE_NAME LIKE ''%''
| >| >>AND
| >| >>C.USER_ROLE_NAME LIKE ''%"
| >| >>AND
| >| >>D.LOCATION_NAME LIKE ''%''
| >| >>AND
| >| >>E.BARCODE_ID LIKE ''%''
| I try the query in QA and it has the same result, in MSDE
| 2000 original has 40 rows, and in MSDE 2000 SP 1 has 6
| rows. I guess my first assumpsion is wrong, it is not
| sp_executesql that need to be fixed. But does this mean
| there are something wrong with left join?
Wow.
A tough question is: Which server returns the correct results?
But don't try to answer that yet. there is probably a much simpler question.
A few easy questions:
0. apply SP3 and see if that make it the same as RTM. if so, I am guessing it
is a bug in SP1 that was fixed and we should just forget all this.
1. Do any of the fields in the WHERE have a null in the table?
select count(*) from T_USER where USER_ID is null
(repeat for USER_TYPE_NAM, .USER_ROLE_NAME , LOCATION_NAME, BARCODE_ID)
2. Start removing expressions from the WHERE and see if the results change in
either server
( remove the AND E.BARCODE_ID LIKE ''%''... then the next...)3
3. in QA change the result type to text: Menu, Query, Results in Text Ctrl-T.
change SELECT A.* to SELECT USER_ID,USER_TYPE_NAM, .USER_ROLE_NAME ,
LOCATION_NAME, BARCODE_ID run the query on both servers. find a row in the RTM
(that's what a server with no SP is called) that doesn't exist in SP1. paste it
here.
Carl Karsten
|||
>--Original Message--
>Wow.
>A tough question is: Which server returns the correct
results?
>But don't try to answer that yet. there is probably a
much simpler question.
>A few easy questions:
>0. apply SP3 and see if that make it the same as RTM. if
so, I am guessing it
>is a bug in SP1 that was fixed and we should just forget
all this.
>1. Do any of the fields in the WHERE have a null in the
table?
>select count(*) from T_USER where USER_ID is null
>(repeat for USER_TYPE_NAM, .USER_ROLE_NAME ,
LOCATION_NAME, BARCODE_ID)
>2. Start removing expressions from the WHERE and see if
the results change in
>either server
>( remove the AND E.BARCODE_ID LIKE ''%''... then the
next...)3
>3. in QA change the result type to text: Menu, Query,
Results in Text Ctrl-T.
>change SELECT A.* to SELECT
USER_ID,USER_TYPE_NAM, .USER_ROLE_NAME ,
>LOCATION_NAME, BARCODE_ID run the query on both servers.
find a row in the RTM
>(that's what a server with no SP is called) that doesn't
exist in SP1. paste it
>here.
>Carl Karsten
>
I check the table and i found that the query only display
result for user that has barcode_id (barcode_id not null)
in SP1, SP2 and SP3a. If i run it in RTM, it will display
all user regardles they have barcode_id or not. If i
remove the AND E.BARCODE_ID LIKE '%' in MSDE with SP
(SP1,SP2, and SP3a), then the result is correct (40 rows).
I wonder, is it a bugs or fixes? If it is a fixes,
considering that with MSDE RTM i can run my queries
without a problem, maybe i prefer not to install the
Service Pack at all and use MSDE 2000 RTM for my
application. But, if it is a bugs, i hope microsoft can
fix it in the next Service Pack.
Thank you,
Abraham
||||
| I check the table and i found that the query only display
| result for user that has barcode_id (barcode_id not null)
| in SP1, SP2 and SP3a. If i run it in RTM, it will display
| all user regardles they have barcode_id or not. If i
| remove the AND E.BARCODE_ID LIKE '%' in MSDE with SP
| (SP1,SP2, and SP3a), then the result is correct (40 rows).
|
| I wonder, is it a bugs or fixes? If it is a fixes,
| considering that with MSDE RTM i can run my queries
| without a problem, maybe i prefer not to install the
| Service Pack at all and use MSDE 2000 RTM for my
| application. But, if it is a bugs, i hope microsoft can
| fix it in the next Service Pack.
I think you have it backwards: RTM had a bug, it was fixed in SP1.
If part of your query is AND E.BARCODE_ID LIKE '%' then an item where BARCODE_ID
is null should not be included, because null is not like anything. So it should
not be included.
If you need it to be included, then your query needs to specify that:
AND ( E.BARCODE_ID LIKE '%' or E.BARCODE_ID is null )
Carl K