Hi,
I do not see any guideline or a thorough article for ISVs who will be using MSDE for deploying their application on desktops using application security roles. There definetely is information here and there but a comprehensive writeup is needed. Any plan
s by any of you SQL Server gurus.
Thank you
Read the BOL about database security roles. There should be all the
information you need. In general you define the security roles you need
based on your application security model. Then set the permissions to your
database objects using the roles. Users of the database are then give a role
membership, rather than explicit permissions.
Jim
"qa" <anonymous@.discussions.microsoft.com> wrote in message
news:30E7FFAA-A6C6-4333-B3AD-382B25689330@.microsoft.com...
> Hi,
> I do not see any guideline or a thorough article for ISVs who will be
using MSDE for deploying their application on desktops using application
security roles. There definetely is information here and there but a
comprehensive writeup is needed. Any plans by any of you SQL Server gurus.
> Thank you
>
|||can all this be done even if I am using the integrated security. See I am implementing my own custom identity and principal classes and here is how it works:
1> my app's first screen will be the login screen.
2>once the user enters the username and password, these 2 fields will be verified from a table in my app's database. If the user does exist in the table then I assign this user as my app domain's current user and use it's id for further interaction with
the objects in the app. I even read the roles the user is part of from a role table, for e.g. dataentryclerk, poweruser etc.. these roles will be used to control the app's UI behaviour too.
3> I am using integrated security to access the database but instead of windows giving me the current user's id token, I am using my role based security (as explained above) and assigning the currect user to the app domain.
This was needed because it is not necessary that every user who accesses my app will be a windows user. My app's requirements state that anyone who has been entered in the database (we will provide a UI to enter users and their passwords) can access the
application.
So in this situation, how can I protect my database, tables and stored procedures from anyone who has some MSDE Manager type of tool.
sql
Showing posts with label msde. Show all posts
Showing posts with label msde. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Post Setup Maintenance
Hi all,
I have designed and implimented my 2K pubs and msde subscribers, anonymous
pull.
There isn't much docs on maintenance steps and less on the "Validate all
users." Does that just show validation stats in the history or will it set
the user to reinitialize if something is off or something else.
My goal would be minimal work to catch invalid data each week or day and
make it right? Suggestions ?
Mike
It will show up in the distribution agent history. You can configure it to
send a new snapshot if it fails validation or just to stop.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:69656639-A716-4E15-8857-289050ED6F61@.microsoft.com...
> Hi all,
> I have designed and implimented my 2K pubs and msde subscribers, anonymous
> pull.
> There isn't much docs on maintenance steps and less on the "Validate all
> users." Does that just show validation stats in the history or will it set
> the user to reinitialize if something is off or something else.
> My goal would be minimal work to catch invalid data each week or day and
> make it right? Suggestions ?
>
> --
> Mike
>
|||So it's the same as changing each agent to the validation agent profile.
It seems like the best option is to validate all regularly, say weekly and
have it apply a send the snapshot automatically, to keep it clean. A job to
run on weekends for the main pub.
Does that make good sense?
The other pubs have filter joins which it apparently won't validate
correcly. A subscriber side job to check table counts based on the joins
might work. Any simpler suggestions for filter join health checking?
Thanks !
Mike
If data falls in the woods and nobody is there to see it ...... ?
"Hilary Cotter" wrote:
> It will show up in the distribution agent history. You can configure it to
> send a new snapshot if it fails validation or just to stop.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
> news:69656639-A716-4E15-8857-289050ED6F61@.microsoft.com...
>
>
|||I seldom run validations as I have confidence replication works well.
AFAIK - validations work with join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:8DEB0974-6669-4985-A479-7EB5B935E68E@.microsoft.com...[vbcol=seagreen]
> So it's the same as changing each agent to the validation agent profile.
> It seems like the best option is to validate all regularly, say weekly and
> have it apply a send the snapshot automatically, to keep it clean. A job
> to
> run on weekends for the main pub.
> Does that make good sense?
> The other pubs have filter joins which it apparently won't validate
> correcly. A subscriber side job to check table counts based on the joins
> might work. Any simpler suggestions for filter join health checking?
> Thanks !
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "Hilary Cotter" wrote:
I have designed and implimented my 2K pubs and msde subscribers, anonymous
pull.
There isn't much docs on maintenance steps and less on the "Validate all
users." Does that just show validation stats in the history or will it set
the user to reinitialize if something is off or something else.
My goal would be minimal work to catch invalid data each week or day and
make it right? Suggestions ?
Mike
It will show up in the distribution agent history. You can configure it to
send a new snapshot if it fails validation or just to stop.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:69656639-A716-4E15-8857-289050ED6F61@.microsoft.com...
> Hi all,
> I have designed and implimented my 2K pubs and msde subscribers, anonymous
> pull.
> There isn't much docs on maintenance steps and less on the "Validate all
> users." Does that just show validation stats in the history or will it set
> the user to reinitialize if something is off or something else.
> My goal would be minimal work to catch invalid data each week or day and
> make it right? Suggestions ?
>
> --
> Mike
>
|||So it's the same as changing each agent to the validation agent profile.
It seems like the best option is to validate all regularly, say weekly and
have it apply a send the snapshot automatically, to keep it clean. A job to
run on weekends for the main pub.
Does that make good sense?
The other pubs have filter joins which it apparently won't validate
correcly. A subscriber side job to check table counts based on the joins
might work. Any simpler suggestions for filter join health checking?
Thanks !
Mike
If data falls in the woods and nobody is there to see it ...... ?
"Hilary Cotter" wrote:
> It will show up in the distribution agent history. You can configure it to
> send a new snapshot if it fails validation or just to stop.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
> news:69656639-A716-4E15-8857-289050ED6F61@.microsoft.com...
>
>
|||I seldom run validations as I have confidence replication works well.
AFAIK - validations work with join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:8DEB0974-6669-4985-A479-7EB5B935E68E@.microsoft.com...[vbcol=seagreen]
> So it's the same as changing each agent to the validation agent profile.
> It seems like the best option is to validate all regularly, say weekly and
> have it apply a send the snapshot automatically, to keep it clean. A job
> to
> run on weekends for the main pub.
> Does that make good sense?
> The other pubs have filter joins which it apparently won't validate
> correcly. A subscriber side job to check table counts based on the joins
> might work. Any simpler suggestions for filter join health checking?
> Thanks !
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "Hilary Cotter" wrote:
Monday, February 20, 2012
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
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
Subscribe to:
Posts (Atom)