Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
************************************************
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
************************************************
Thank you in advance.
Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>
|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:

> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. that
> a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>
>
|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
****************************************
********
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
****************************************
********
Thank you in advance.Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
> Thank you in advance.
>|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:

> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. tha
t
> a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
> Thank you in advance.
>
>|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
************************************************
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
************************************************
Thank you in advance.Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. that
> a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>
>|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspxsql

Query Performance

Good Morning All!
I have a couple questions regarding query performance:
#1 - I have a VB6 program that allows the user to build
the where clause of a query to be ran against a SQL 2000
database. When the where clause is established, it is
sent into a stored procedure where there are 3 different
queries that can be executed based on the criteria being
used. Basically, the join structure of each query is
different. I created an index for a query that had been
running slow that executes 1 of the 3 queries in the
stored procedure. However, now whenever 1 of the other
queries are executed for a different set of criteria it is
3X longer to run. I remove the index, and it is fast
again, but my other query is slow again. Can someone
please explain to me why this happens, and why SQL chooses
a less optimized plan for some of the queries when a new
index is added?
#2 - In the same type of environment as explained above
(i.e. VB6, SQL 2000, and stored procedures), we have a
single user that tests query speed to determine what
indexes need built. We can get a query to run in 5
seconds, but when multiple users try to execute the same
or different queries together, it takes nearly 5X longer.
Once the first user gets results, then the others come
back one right after the other in no time. Does SQL only
allow 1 person to execute a stored procedure at any given
time? What effect does the mult-user environment have in
this scenerio.
Any help would be greatly appreciated, as I am new to
working with indexes.
Thank you,
Heidi
for number 1...
Posting the text of the procedure would allow a more precise answer, but I
suspect the following info will help you...
Procedure plans are cached and are not recompiled if there is a plan already
in cache that can be reused. (I'm greatly simplying, but that's accurate
enough for this discussion...)
In your case, you have a proc that might run a variety of different queries
and for each query it sounds like the parameters might be quite different
from run to run. A good plan for one of the queries might not be a good plan
for the other queries. However, if a plan is already cached, it may be
re-used even if it's not the best plan. You might want to experiment with
having the 'top level' procedure call one of 3 other procs where each of the
child procs accepts the parameters.
for number 2...
It's difficult to say without more data. The most likley scenarios are a)
blocking. Do the queries update,delete,insert data? or b) waiting on some
type of resource. Search www.sqlmag.com archieves for an article (with
scripts) by Tom Davidson from MS. Use the script to run dbcc
sqlperfwaitstats and see if you have a high wait in any area. I suspect you
may be having a high wait of pageiolatch_sh. ALso, while the queries are
'running' and are not returning data untilt he first query is done... you
could look at the row in master..sysprocesses for each of the 'waiting'
queries to see what the waittype is.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
|||Also have a look at
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
|||hdsjunk wrote:
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
If a query takes 5 seconds to run on a server with low utlization, then
the query is unlikely running efficiently as it is consuming 5 seconds
of duration and likely something similar in CPU. I would say you need to
performance tune the query to run in much less than 5 seconds. Something
in the 10's of milliseconds should be your goal.
As other posters have mentioned, if it's taking 5 seconds, it's likely
locking pages on the same tables other callers to the same procedure
require.
While 5 seconds may be an appropriate time for an individual end-user to
wait for a response from the application, it usually won't do in a
concurrent database environment. Use Profiler and Query Analyzer to see
what is taking so long in the query to execute and try and tune it.
Also, as others have said, you are risking recompiles sending 3
different where clauses for execution to the same stored procedure. The
way to eliminate this is to use sp_executesql inside the procedure to
execute the dynamic SQL.
David G.

Query Performance

Good Morning All!
I have a couple questions regarding query performance:
#1 - I have a VB6 program that allows the user to build
the where clause of a query to be ran against a SQL 2000
database. When the where clause is established, it is
sent into a stored procedure where there are 3 different
queries that can be executed based on the criteria being
used. Basically, the join structure of each query is
different. I created an index for a query that had been
running slow that executes 1 of the 3 queries in the
stored procedure. However, now whenever 1 of the other
queries are executed for a different set of criteria it is
3X longer to run. I remove the index, and it is fast
again, but my other query is slow again. Can someone
please explain to me why this happens, and why SQL chooses
a less optimized plan for some of the queries when a new
index is added?
#2 - In the same type of environment as explained above
(i.e. VB6, SQL 2000, and stored procedures), we have a
single user that tests query speed to determine what
indexes need built. We can get a query to run in 5
seconds, but when multiple users try to execute the same
or different queries together, it takes nearly 5X longer.
Once the first user gets results, then the others come
back one right after the other in no time. Does SQL only
allow 1 person to execute a stored procedure at any given
time? What effect does the mult-user environment have in
this scenerio.
Any help would be greatly appreciated, as I am new to
working with indexes.
Thank you,
Heidifor number 1...
Posting the text of the procedure would allow a more precise answer, but I
suspect the following info will help you...
Procedure plans are cached and are not recompiled if there is a plan already
in cache that can be reused. (I'm greatly simplying, but that's accurate
enough for this discussion...)
In your case, you have a proc that might run a variety of different queries
and for each query it sounds like the parameters might be quite different
from run to run. A good plan for one of the queries might not be a good plan
for the other queries. However, if a plan is already cached, it may be
re-used even if it's not the best plan. You might want to experiment with
having the 'top level' procedure call one of 3 other procs where each of the
child procs accepts the parameters.
for number 2...
It's difficult to say without more data. The most likley scenarios are a)
blocking. Do the queries update,delete,insert data? or b) waiting on some
type of resource. Search www.sqlmag.com archieves for an article (with
scripts) by Tom Davidson from MS. Use the script to run dbcc
sqlperfwaitstats and see if you have a high wait in any area. I suspect you
may be having a high wait of pageiolatch_sh. ALso, while the queries are
'running' and are not returning data untilt he first query is done... you
could look at the row in master..sysprocesses for each of the 'waiting'
queries to see what the waittype is.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi|||Also have a look at
http://www.sommarskog.se/dyn-search.html
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi|||hdsjunk wrote:
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
If a query takes 5 seconds to run on a server with low utlization, then
the query is unlikely running efficiently as it is consuming 5 seconds
of duration and likely something similar in CPU. I would say you need to
performance tune the query to run in much less than 5 seconds. Something
in the 10's of milliseconds should be your goal.
As other posters have mentioned, if it's taking 5 seconds, it's likely
locking pages on the same tables other callers to the same procedure
require.
While 5 seconds may be an appropriate time for an individual end-user to
wait for a response from the application, it usually won't do in a
concurrent database environment. Use Profiler and Query Analyzer to see
what is taking so long in the query to execute and try and tune it.
Also, as others have said, you are risking recompiles sending 3
different where clauses for execution to the same stored procedure. The
way to eliminate this is to use sp_executesql inside the procedure to
execute the dynamic SQL.
David G.

Query parameter drop-down not available once report is cached

Hi, I have a date parameter that is populated from a query to give me a
month drop-down on my report. When a user selects the month and runs
the report, it passes the parameter to a query to return a dataset.
This is all straight forward. Since the report runs quite slowly, I
wanted to cache the report witha dafault month, but allow the users to
then re-run the report with other months. I found that once I cached
the report on a shared schedule, the month drop-down is no longer
available for me to select a new query parameter to rerun the report.
Another filter parameter is available, so I am assuming it it because
it is a query parameter.
Is there a way to cache the reoprt, but still allow users to reselect
the query parameter and rerun the report?You can make the month parameter a filter parameter, bring back a all the
months data and then apply the month filter to the data regions in the
report..
Or if you are using Enterprise Edition you could preload the cache using a
data-driven subscription, see "Preloading the Cache"
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_processing_v1_2p89.asp
--
Douglas McDowell
douglas@.nospam.solidqualitylearning.com
"MDXQuery" <imgroup1@.hotmail.com> wrote in message
news:1109694129.517130.311640@.g14g2000cwa.googlegroups.com...
> Hi, I have a date parameter that is populated from a query to give me a
> month drop-down on my report. When a user selects the month and runs
> the report, it passes the parameter to a query to return a dataset.
> This is all straight forward. Since the report runs quite slowly, I
> wanted to cache the report witha dafault month, but allow the users to
> then re-run the report with other months. I found that once I cached
> the report on a shared schedule, the month drop-down is no longer
> available for me to select a new query parameter to rerun the report.
> Another filter parameter is available, so I am assuming it it because
> it is a query parameter.
> Is there a way to cache the reoprt, but still allow users to reselect
> the query parameter and rerun the report?
>

Query on large record

First thing I am new to write a query. I have two tables that I need
information from. The first table has the user, date and time. The second
table has the user, date, time and a record that contains 132 characters.
What I need to do is match the user, Date and time along with 7 characters
that are placed 7 positions in the record.
This is the begining of the record looks like and I only need the 0685043,
is this possible?
OVRIDE 0685043
Thanks in advance for any help.
I don't know what you're matching the 0685043 with, but
you will probably need the SUBSTRING function, which you
can learn about from Books Online, to extract that from the
rest of the 132 character string.
Generally, if substrings of a column have meaning of their own,
it is better to keep that information in a separate column of the
table.
Steve Kass
Drew University
Daniell wrote:

>First thing I am new to write a query. I have two tables that I need
>information from. The first table has the user, date and time. The second
>table has the user, date, time and a record that contains 132 characters.
>What I need to do is match the user, Date and time along with 7 characters
>that are placed 7 positions in the record.
>This is the begining of the record looks like and I only need the 0685043,
>is this possible?
>OVRIDE 0685043
>Thanks in advance for any help.
>
>
|||Thanks Steve I guess I should have explained a little better. I will give
the SUBSTRING a try.
"Steve Kass" wrote:

> I don't know what you're matching the 0685043 with, but
> you will probably need the SUBSTRING function, which you
> can learn about from Books Online, to extract that from the
> rest of the 132 character string.
> Generally, if substrings of a column have meaning of their own,
> it is better to keep that information in a separate column of the
> table.
> Steve Kass
> Drew University
> Daniell wrote:
>

Query notification problem

I've got an application that's using query notifications with a two-user setup (a user that starts the dependency running, and an application user that subscribes to it). I'm having problems getting the query notifications to go through however... according to the Profiler, when the application user does something that triggers a query notification, it causes this error:

This message could not be delivered because the user with ID [application user] in database ID [database] does not have permission to send to the service. Service name: SqlQueryNotificationService-[guid].

The problem is that [guid] is different each time, and there doesn't seem to be a generic "grant send on all services" statement, so I can't grant send on the particular service in question to the application user to get rid of the error. (If I grant db_owner to the application user, the problem goes away and everything works, but I'd rather not do that. The application user already has subscribe query notifications, receive on QueryNotificationErrorsQueue, and references on contract::...PostQueryNotification.)

Obviously I'm doing something wrong, but I am completely out of ideas and can't seem to find anything new to try. Any ideas? Thanks.

It is not clear to me how can an application separate the roles of creating the notification and getting the notification using SqlDependency (which is the component that creates the 'SqlQueryNotificationService-[guid]' named services). The SqlDependency is intended to be used by one appdomain that calls Start and then various callbacks (notification subscriptions) are being set up by the same appdomain.

Do you have a particular reason why you want to separate the two roles?

Utimately you can always go one level down and use the SqlNotification objects instead of SqlDependency. They are more primitive (no automated provisioning of a temp service/queue/procedure for each appdomain etc etc), but they allow more control as you can specify explicitly the service that is intended to receive the notifications and you can set up said service as your heart desires (including all the necessary GRANTs).

|||The reason for separating them is that the user starting the dependency has to have a bunch of create privileges in its default schema, and for security reasons we don't want the application user to have such access. By isolating the user doing the start in its own schema, we can keep the application user and the main schema locked down.

It turns out that there is a solution, however... by Reflecting through the SqlDependency class, I determined that it's hard-wired to look for a role called "sql_dependency_subscriber", and if that role exists it grants the necessary privileges to it upon startup. By creating the role and adding the application user to it, everything works. Seems like a major kludge on Microsoft's part to me, and it would have helped if it was documented somewhere, but it does the trick nonetheless.
|||Well, that is news for me as well. I never knew SqlDependency does that.

Query Notification

Hi,
I am using .net 2.0 and sql server 2005.
Now i want to notify user Application (C# Application) about the change in certain table.
For Example: if tbl1 be any table and when insert operation is carried out in that table.
Then after 100 or multiple of 100 record insertion in tbl1 i want the notification to be given to user Application (C# Application).
Is this possible through Query Notification.?
I even dont know if this is the right forum for this question..

Please Help

It is a common mistake to confuse Query Notifications with Notification Services.

Query Not Returning Values and process is waiting on CXPACKET

Good Morning All,
I have a query from a user that I have been asked to take a look at. I
have narrowed down the issue to one of the parameters in the query.
When the query covers 2002 thru 2003 one execution plan is issued when
the query covers only a portion of 2003 a second execution plan is
issued. The issue is that the 2002 thru 2003 executes fine, the query
that executes a portion over a portion of 2003 data does not. If I
check the process it has an item that has a wait type of CXPACKET and it
just hangs never returning. I was wondering if anyone has seen this
kind of behavior before. The two different plans where created by only
changing the data range.
An advise would help.
Thanks
Show Plan text (Non Working)
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[vendor_ident]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[opco_ident]))
| |
|--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
[fact_sales]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
| | | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]), ORDERED FORWARD)
| | | |--Index
Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
[fact_sales]),
SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
[fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
[fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
|
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like([Dim_Vendor].[opco_vendor_name],
'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
[Dim_Vendor].[opco_vendor_num]<>3136) AND
[Dim_Vendor].[opco_vendor_num]<>3571) AND
[Dim_Vendor].[opco_vendor_num]<>3572) AND
[Dim_Vendor].[opco_vendor_num]<>3573) AND
[Dim_Vendor].[opco_vendor_num]<>3673) AND
[Dim_Vendor].[opco_vendor_num]<>3860) AND
[Dim_Vendor].[opco_vendor_num]<>3861) AND
[Dim_Vendor].[opco_vendor_num]<>4290) AND
[Dim_Vendor].[opco_vendor_num]<>4530) AND
[Dim_Vendor].[opco_vendor_num]<>5373) AND
[Dim_Vendor].[opco_vendor_num]<>5760) AND
[Dim_Vendor].[opco_vendor_num]<>5797) AND
[Dim_Vendor].[opco_vendor_num]<>6064) AND
[Dim_Vendor].[opco_vendor_num]<>6065) AND
[Dim_Vendor].[opco_vendor_num]<>6066) AND
[Dim_Vendor].[opco_vendor_num]<>6073) AND
[Dim_Vendor].[opco_vendor_num]<>6329) AND
[Dim_Vendor].[opco_vendor_num]<>6525) AND
[Dim_Vendor].[opco_vendor_num]<>6607) AND
[Dim_Vendor].[opco_vendor_num]<>6684) AND
[Dim_Vendor].[opco_vendor_num]<>7240) AND
[Dim_Vendor].[opco_vendor_num]<>7241) AND
[Dim_Vendor].[opco_vendor_num]<>8325) AND
[Dim_Vendor].[opco_vendor_num]<>8326) AND
[Dim_Vendor].[opco_vendor_num]<>16229) AND
[Dim_Vendor].[opco_vendor_num]<>16230) AND
[Dim_Vendor].[opco_vendor_num]<>17756) AND
[Dim_Vendor].[opco_vendor_num]<>18121) AND
[Dim_Vendor].[opco_vendor_num]<>19552) AND
[Dim_Vendor].[opco_vendor_num]<>23852) AND
[Dim_Vendor].[opco_vendor_num]<>23853) AND
[Dim_Vendor].[opco_vendor_num]<>25307) AND
[Dim_Vendor].[opco_vendor_num]<>25558) AND
[Dim_Vendor].[opco_vendor_num]<>30003) AND
[Dim_Vendor].[opco_vendor_num]<>30004) AND
[Dim_Vendor].[opco_vendor_num]<>33068) AND
[Dim_Vendor].[opco_vendor_num]<>36230) AND
[Dim_Vendor].[opco_vendor_num]<>36236) AND
[Dim_Vendor].[opco_vendor_num]<>37756) AND
[Dim_Vendor].[opco_vendor_num]<>49365) AND
[Dim_Vendor].[opco_vendor_num]<>62380) AND
[Dim_Vendor].[opco_vendor_num]<>69485))
| |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]),
SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
[Dim_Vendor].[opco_vendor_num]<>372) AND
[Dim_Vendor].[opco_vendor_num]<>429) AND
[Dim_Vendor].[opco_vendor_num]<>448) AND
[Dim_Vendor].[opco_vendor_num]<>600) AND
[Dim_Vendor].[opco_vendor_num]<>617) AND
[Dim_Vendor].[opco_vendor_num]<>641) AND
[Dim_Vendor].[opco_vendor_num]<>713) AND
[Dim_Vendor].[opco_vendor_num]<>1325) AND
[Dim_Vendor].[opco_vendor_num]<>1672) AND
[Dim_Vendor].[opco_vendor_num]<>1850) AND
[Dim_Vendor].[opco_vendor_num]<>1940) AND
[Dim_Vendor].[opco_vendor_num]<>2210) AND
[Dim_Vendor].[opco_vendor_num]<>2215) AND
[Dim_Vendor].[opco_vendor_num]<>3130) AND
[Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED FORWARD)
Show plan text (working)
|--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Hash Match(Inner Join,
HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
|--Parallelism(Broadcast)
| |--Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
|--Hash Match(Inner Join,
HASH:([fact_sales].[opco_num],
[fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
[dim_inv_info_tbl].[item_num]),
RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
[fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
| |--Hash Match(Inner Join,
HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident])=([fact_sales].[opco_num],
[fact_sales].[item_ident]),
RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
[Dim_item].[item_ident]=[fact_sales].[item_ident]))
| |--Bitmap(HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
| | |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident]))
| | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
| |--Parallelism(Repartition Streams,
PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
WHERE:(PROBE([Bitmap1006])=TRUE))
| |--Hash Match(Inner Join,
HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
| |--Parallelism(Broadcast)
| | |--Nested Loops(Left
Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
| |
|--Parallelism(Gather Streams)
| | |
|--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
| | |
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
| | |--Constant Scan
| |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
[fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]))This is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C3B993.52F36600
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Sounds like parallelism is the problem. Try adding the following to the end
of your query:
OPTION (MAXDOP 1)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"sfibich" <sfibich@.pfgc.com> wrote in message
news:#ISwrybuDHA.2180@.TK2MSFTNGP09.phx.gbl...
Good Morning All,
I have a query from a user that I have been asked to take a look at. I
have narrowed down the issue to one of the parameters in the query.
When the query covers 2002 thru 2003 one execution plan is issued when
the query covers only a portion of 2003 a second execution plan is
issued. The issue is that the 2002 thru 2003 executes fine, the query
that executes a portion over a portion of 2003 data does not. If I
check the process it has an item that has a wait type of CXPACKET and it
just hangs never returning. I was wondering if anyone has seen this
kind of behavior before. The two different plans where created by only
changing the data range.
An advise would help.
Thanks
Show Plan text (Non Working)
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[vendor_ident]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[opco_ident]))
| |
|--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
[fact_sales]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
| | | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]), ORDERED FORWARD)
| | | |--Index
Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
[fact_sales]),
SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
[fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
[fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
|
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like
([Dim_Vendor].[opco_vendor_name],
'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
[Dim_Vendor].[opco_vendor_num]<>3136) AND
[Dim_Vendor].[opco_vendor_num]<>3571) AND
[Dim_Vendor].[opco_vendor_num]<>3572) AND
[Dim_Vendor].[opco_vendor_num]<>3573) AND
[Dim_Vendor].[opco_vendor_num]<>3673) AND
[Dim_Vendor].[opco_vendor_num]<>3860) AND
[Dim_Vendor].[opco_vendor_num]<>3861) AND
[Dim_Vendor].[opco_vendor_num]<>4290) AND
[Dim_Vendor].[opco_vendor_num]<>4530) AND
[Dim_Vendor].[opco_vendor_num]<>5373) AND
[Dim_Vendor].[opco_vendor_num]<>5760) AND
[Dim_Vendor].[opco_vendor_num]<>5797) AND
[Dim_Vendor].[opco_vendor_num]<>6064) AND
[Dim_Vendor].[opco_vendor_num]<>6065) AND
[Dim_Vendor].[opco_vendor_num]<>6066) AND
[Dim_Vendor].[opco_vendor_num]<>6073) AND
[Dim_Vendor].[opco_vendor_num]<>6329) AND
[Dim_Vendor].[opco_vendor_num]<>6525) AND
[Dim_Vendor].[opco_vendor_num]<>6607) AND
[Dim_Vendor].[opco_vendor_num]<>6684) AND
[Dim_Vendor].[opco_vendor_num]<>7240) AND
[Dim_Vendor].[opco_vendor_num]<>7241) AND
[Dim_Vendor].[opco_vendor_num]<>8325) AND
[Dim_Vendor].[opco_vendor_num]<>8326) AND
[Dim_Vendor].[opco_vendor_num]<>16229) AND
[Dim_Vendor].[opco_vendor_num]<>16230) AND
[Dim_Vendor].[opco_vendor_num]<>17756) AND
[Dim_Vendor].[opco_vendor_num]<>18121) AND
[Dim_Vendor].[opco_vendor_num]<>19552) AND
[Dim_Vendor].[opco_vendor_num]<>23852) AND
[Dim_Vendor].[opco_vendor_num]<>23853) AND
[Dim_Vendor].[opco_vendor_num]<>25307) AND
[Dim_Vendor].[opco_vendor_num]<>25558) AND
[Dim_Vendor].[opco_vendor_num]<>30003) AND
[Dim_Vendor].[opco_vendor_num]<>30004) AND
[Dim_Vendor].[opco_vendor_num]<>33068) AND
[Dim_Vendor].[opco_vendor_num]<>36230) AND
[Dim_Vendor].[opco_vendor_num]<>36236) AND
[Dim_Vendor].[opco_vendor_num]<>37756) AND
[Dim_Vendor].[opco_vendor_num]<>49365) AND
[Dim_Vendor].[opco_vendor_num]<>62380) AND
[Dim_Vendor].[opco_vendor_num]<>69485))
| |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]),
SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
[Dim_Vendor].[opco_vendor_num]<>372) AND
[Dim_Vendor].[opco_vendor_num]<>429) AND
[Dim_Vendor].[opco_vendor_num]<>448) AND
[Dim_Vendor].[opco_vendor_num]<>600) AND
[Dim_Vendor].[opco_vendor_num]<>617) AND
[Dim_Vendor].[opco_vendor_num]<>641) AND
[Dim_Vendor].[opco_vendor_num]<>713) AND
[Dim_Vendor].[opco_vendor_num]<>1325) AND
[Dim_Vendor].[opco_vendor_num]<>1672) AND
[Dim_Vendor].[opco_vendor_num]<>1850) AND
[Dim_Vendor].[opco_vendor_num]<>1940) AND
[Dim_Vendor].[opco_vendor_num]<>2210) AND
[Dim_Vendor].[opco_vendor_num]<>2215) AND
[Dim_Vendor].[opco_vendor_num]<>3130) AND
[Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED FORWARD)
Show plan text (working)
|--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Hash Match(Inner Join,
HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
|--Parallelism(Broadcast)
| |--Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
|--Hash Match(Inner Join,
HASH:([fact_sales].[opco_num],
[fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
[dim_inv_info_tbl].[item_num]),
RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
[fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
| |--Hash Match(Inner Join,
HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident])=([fact_sales].[opco_num],
[fact_sales].[item_ident]),
RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
[Dim_item].[item_ident]=[fact_sales].[item_ident]))
| |--Bitmap(HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
| | |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident]))
| | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
| |--Parallelism(Repartition Streams,
PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
WHERE:(PROBE([Bitmap1006])=TRUE))
| |--Hash Match(Inner Join,
HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
| |--Parallelism(Broadcast)
| | |--Nested Loops(Left
Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
| |
|--Parallelism(Gather Streams)
| | |
|--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
| | |
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
| | |--Constant Scan
| |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
[fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]))
--=_NextPart_000_02C4_01C3B993.52F36600
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Sounds like parallelism is the =problem. Try adding the following to the end of your query:
OPTION (MAXDOP 1)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"sfibich" wrote in message news:#ISwrybuDHA.2180=@.TK2MSFTNGP09.phx.gbl...Good Morning All,I have a query from a user that I have been asked to =take a look at. I have narrowed down the issue to one of the parameters in =the query. When the query covers 2002 thru 2003 one execution plan is =issued when the query covers only a portion of 2003 a second execution plan =is issued. The issue is that the 2002 thru 2003 executes fine, =the query that executes a portion over a portion of 2003 data does not. =If I check the process it has an item that has a wait type of CXPACKET =and it just hangs never returning. I was wondering if anyone has seen =this kind of behavior before. The two different plans where created =by only changing the data range.An advise would help.ThanksShow Plan text (Non =Working) |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC, =[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) =|--Parallelism(Gather Streams) &=nbsp; |--Nested Loops(Inner Join, OUTER =REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num])) &n=bsp; |--Nested Loops(Inner Join, OUTER REFERENCES:([fact_sales].[vendor_ident])) = &=nbsp; | |--Nested Loops(Inner Join, OUTER REFERENCES:([fact_sales].[opco_ident])) &n=bsp; &nb=sp; | | |--Filter(WHERE:([fact_sales].[item_num]=3D[dim_inv_info_tbl].[item_n=um])) &nbs=p; | | | |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS [fact_sales])) &nb=sp; | |
20
| |--Nested Loops(Inner =Join, OUTER REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH) = | | | = |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_=01] AS [dim_inv_info_tbl]), ORDERED FORWARD) &=nbsp; | | | = |--Index =Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS [fact_sales]), SEEK:([fact_sales].[opco_num]=3D[dim_inv_info_tbl].[opco_num] AND [fact_sales].[calendar_date] >=3D 'Nov 5 2003 12:00AM' AND [fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD) &=nbsp; | | |--Clustered Index Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]), =SEEK:([Dim_OPCO].[opco_ident]=3D[fact_sales].[opco_ident]) ORDERED FORWARD) &=nbsp; | |--Filter(WHERE:(((((((((((((((((((((((((((((((((((((((((((((((((((((=(((like([Dim_Vendor].[opco_vendor_name], 'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], ='Awrey%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND [Dim_Vendor].[opco_vendor_num]3136) AND [Dim_Vendor].[opco_vendor_num]3571) AND [Dim_Vendor].[opco_vendor_num]3572) AND [Dim_Vendor].[opco_vendor_num]3573) AND [Dim_Vendor].[opco_vendor_num]3673) AND [Dim_Vendor].[opco_vendor_num]3860) AND [Dim_Vendor].[opco_vendor_num]3861) AND [Dim_Vendor].[opco_vendor_num]4290) AND [Dim_Vendor].[opco_vendor_num]4530) AND [Dim_Vendor].[opco_vendor_num]5373) AND [Dim_Vendor].[opco_vendor_num]5760) AND [Dim_Vendor].[opco_vendor_num]5797) AND [Dim_Vendor].[opco_vendor_num]6064) AND [Dim_Vendor].[opco_vendor_num]6065) AND [Dim_Vendor].[opco_vendor_num]6066) AND [Dim_Vendor].[opco_vendor_num]6073) AND [Dim_Vendor].[opco_vendor_num]6329) AND [Dim_Vendor].[opco_vendor_num]6525) AND [Dim_Vendor].[opco_vendor_num]6607) AND [Dim_Vendor].[opco_vendor_num]6684) AND [Dim_Vendor].[opco_vendor_num]7240) AND [Dim_Vendor].[opco_vendor_num]7241) AND [Dim_Vendor].[opco_vendor_num]8325) AND [Dim_Vendor].[opco_vendor_num]8326) AND [Dim_Vendor].[opco_vendor_num]16229) AND [Dim_Vendor].[opco_vendor_num]16230) AND [Dim_Vendor].[opco_vendor_num]17756) AND [Dim_Vendor].[opco_vendor_num]18121) AND [Dim_Vendor].[opco_vendor_num]19552) AND [Dim_Vendor].[opco_vendor_num]23852) AND [Dim_Vendor].[opco_vendor_num]23853) AND [Dim_Vendor].[opco_vendor_num]25307) AND [Dim_Vendor].[opco_vendor_num]25558) AND [Dim_Vendor].[opco_vendor_num]30003) AND [Dim_Vendor].[opco_vendor_num]30004) AND [Dim_Vendor].[opco_vendor_num]33068) AND [Dim_Vendor].[opco_vendor_num]36230) AND [Dim_Vendor].[opco_vendor_num]36236) AND [Dim_Vendor].[opco_vendor_num]37756) AND [Dim_Vendor].[opco_vendor_num]49365) AND [Dim_Vendor].[opco_vendor_num]62380) AND [Dim_Vendor].[opco_vendor_num]69485)) &n=bsp; &nb=sp; | |--Clustered Index Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS [Dim_Vendor]), SEEK:([Dim_Vendor].[vendor_ident]=3D[fact_sales].[vendor_ident]), WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]173 AND [Dim_Vendor].[opco_vendor_num]372) AND [Dim_Vendor].[opco_vendor_num]429) AND [Dim_Vendor].[opco_vendor_num]448) AND [Dim_Vendor].[opco_vendor_num]600) AND [Dim_Vendor].[opco_vendor_num]617) AND [Dim_Vendor].[opco_vendor_num]641) AND [Dim_Vendor].[opco_vendor_num]713) AND [Dim_Vendor].[opco_vendor_num]1325) AND [Dim_Vendor].[opco_vendor_num]1672) AND [Dim_Vendor].[opco_vendor_num]1850) AND [Dim_Vendor].[opco_vendor_num]1940) AND [Dim_Vendor].[opco_vendor_num]2210) AND [Dim_Vendor].[opco_vendor_num]2215) AND [Dim_Vendor].[opco_vendor_num]3130) AND [Dim_Vendor].[opco_vendor_num]3131) ORDERED FORWARD) &=nbsp; |--Clustered Index =Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]), =SEEK:([Dim_item].[item_ident]=3D[fact_sales].[item_ident]), WHERE:([Dim_item].[opco_num]=3D[dim_inv_info_tbl].[opco_num]) =ORDERED FORWARD)Show plan text (working) |--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC, [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC, [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) &nbs=p; |--Hash Match(Inner Join, HASH:([Dim_OPCO].[opco_ident])=3D([fact_sales].[opco_ident]), RESIDUAL:([fact_sales].[opco_ident]=3D[Dim_OPCO].[opco_ident]))&n=bsp; &nb=sp; |--Parallelism(Broadcast) &n=bsp; | |--Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO])) &nb=sp; |--Hash Match(Inner Join, HASH:([fact_sales].[opco_num], [fact_sales].[item_num])=3D([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]), RESIDUAL:([dim_inv_info_tbl].[opco_num]=3D[fact_sales].[opco_num] =AND [fact_sales].[item_num]=3D[dim_inv_info_tbl].[item_num])) &n=bsp; &nb=sp; |--Parallelism(Repartition Streams, PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num])) &n=bsp; &nb=sp; | |--Hash Match(Inner Join, =HASH:([Dim_item].[opco_num], [Dim_item].[item_ident])=3D([fact_sales].[opco_num], [fact_sales].[item_ident]), RESIDUAL:([Dim_item].[opco_num]=3D[fact_sales].[opco_num] AND [Dim_item].[item_ident]=3D[fact_sales].[item_ident])) = &=nbsp; | |--Bitmap(HASH:([Dim_item].[opco_num], [Dim_item].[item_ident]), DEFINE:([Bitmap1006]))  =; = | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident])) &n=bsp; &nb=sp; | | |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item])) &nb=sp; &nbs=p; | =|--Parallelism(Repartition Streams, PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]), WHERE:(PROBE([Bitmap1006])=3DTRUE)) = &=nbsp; | = |--Hash Match(Inner Join, HASH:([Dim_Vendor].[vendor_ident])=3D([fact_sales].[vendor_ident]), RESIDUAL:([fact_sales].[vendor_ident]=3D[Dim_Vendor].[vendor_ident]))= &nb=sp; | = |--Parallelism(Broadcast) &n=bsp; &nb=sp; | = | |--Nested Loops(Left Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=3D[Expr1005]))  =; = | = | |--Parallelism(Gather Streams) &=nbsp; | = | | |--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], ='All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', =NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL))) &n=bsp; | = | | |--Clustered =Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS [Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]173)) &=nbsp; &n=bsp; | = | |--Constant Scan  =; | = |--Clustered Index =Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS [fact_sales]), =WHERE:([fact_sales].[calendar_date]>=3D'Nov 5 2002 12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))  =; = |--Parallelism(Repartition Streams, PARTITION COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num])) &n=bsp; &nb=sp; |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_=01] AS [dim_inv_info_tbl]))

---

--=_NextPart_000_02C4_01C3B993.52F36600--|||1. I would update stats to make sure that's not an issue...
2. CXPacket indicates a waittime assoicated with parrallel query plans. Try
setting the MAXDOP to 1 as a test. The slow plan is probably parallel and
the fast plan may be serial.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"sfibich" <sfibich@.pfgc.com> wrote in message
news:%23ISwrybuDHA.2180@.TK2MSFTNGP09.phx.gbl...
> Good Morning All,
> I have a query from a user that I have been asked to take a look at. I
> have narrowed down the issue to one of the parameters in the query.
> When the query covers 2002 thru 2003 one execution plan is issued when
> the query covers only a portion of 2003 a second execution plan is
> issued. The issue is that the 2002 thru 2003 executes fine, the query
> that executes a portion over a portion of 2003 data does not. If I
> check the process it has an item that has a wait type of CXPACKET and it
> just hangs never returning. I was wondering if anyone has seen this
> kind of behavior before. The two different plans where created by only
> changing the data range.
> An advise would help.
> Thanks
>
> Show Plan text (Non Working)
> |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Parallelism(Gather Streams)
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[vendor_ident]))
> | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[opco_ident]))
> | |
> |--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
> | | | |--Bookmark
> Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
> [fact_sales]))
> | | | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
> | | | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
> AS [dim_inv_info_tbl]), ORDERED FORWARD)
> | | | |--Index
> Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
> [fact_sales]),
> SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
> [fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
> [fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
> | | |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
> SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
> |
>
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like
([Dim_Vendor].[opco_vendor_name],
> 'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
> NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
> [Dim_Vendor].[opco_vendor_num]<>3136) AND
> [Dim_Vendor].[opco_vendor_num]<>3571) AND
> [Dim_Vendor].[opco_vendor_num]<>3572) AND
> [Dim_Vendor].[opco_vendor_num]<>3573) AND
> [Dim_Vendor].[opco_vendor_num]<>3673) AND
> [Dim_Vendor].[opco_vendor_num]<>3860) AND
> [Dim_Vendor].[opco_vendor_num]<>3861) AND
> [Dim_Vendor].[opco_vendor_num]<>4290) AND
> [Dim_Vendor].[opco_vendor_num]<>4530) AND
> [Dim_Vendor].[opco_vendor_num]<>5373) AND
> [Dim_Vendor].[opco_vendor_num]<>5760) AND
> [Dim_Vendor].[opco_vendor_num]<>5797) AND
> [Dim_Vendor].[opco_vendor_num]<>6064) AND
> [Dim_Vendor].[opco_vendor_num]<>6065) AND
> [Dim_Vendor].[opco_vendor_num]<>6066) AND
> [Dim_Vendor].[opco_vendor_num]<>6073) AND
> [Dim_Vendor].[opco_vendor_num]<>6329) AND
> [Dim_Vendor].[opco_vendor_num]<>6525) AND
> [Dim_Vendor].[opco_vendor_num]<>6607) AND
> [Dim_Vendor].[opco_vendor_num]<>6684) AND
> [Dim_Vendor].[opco_vendor_num]<>7240) AND
> [Dim_Vendor].[opco_vendor_num]<>7241) AND
> [Dim_Vendor].[opco_vendor_num]<>8325) AND
> [Dim_Vendor].[opco_vendor_num]<>8326) AND
> [Dim_Vendor].[opco_vendor_num]<>16229) AND
> [Dim_Vendor].[opco_vendor_num]<>16230) AND
> [Dim_Vendor].[opco_vendor_num]<>17756) AND
> [Dim_Vendor].[opco_vendor_num]<>18121) AND
> [Dim_Vendor].[opco_vendor_num]<>19552) AND
> [Dim_Vendor].[opco_vendor_num]<>23852) AND
> [Dim_Vendor].[opco_vendor_num]<>23853) AND
> [Dim_Vendor].[opco_vendor_num]<>25307) AND
> [Dim_Vendor].[opco_vendor_num]<>25558) AND
> [Dim_Vendor].[opco_vendor_num]<>30003) AND
> [Dim_Vendor].[opco_vendor_num]<>30004) AND
> [Dim_Vendor].[opco_vendor_num]<>33068) AND
> [Dim_Vendor].[opco_vendor_num]<>36230) AND
> [Dim_Vendor].[opco_vendor_num]<>36236) AND
> [Dim_Vendor].[opco_vendor_num]<>37756) AND
> [Dim_Vendor].[opco_vendor_num]<>49365) AND
> [Dim_Vendor].[opco_vendor_num]<>62380) AND
> [Dim_Vendor].[opco_vendor_num]<>69485))
> | |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
> [Dim_Vendor]),
> SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
> WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
> [Dim_Vendor].[opco_vendor_num]<>372) AND
> [Dim_Vendor].[opco_vendor_num]<>429) AND
> [Dim_Vendor].[opco_vendor_num]<>448) AND
> [Dim_Vendor].[opco_vendor_num]<>600) AND
> [Dim_Vendor].[opco_vendor_num]<>617) AND
> [Dim_Vendor].[opco_vendor_num]<>641) AND
> [Dim_Vendor].[opco_vendor_num]<>713) AND
> [Dim_Vendor].[opco_vendor_num]<>1325) AND
> [Dim_Vendor].[opco_vendor_num]<>1672) AND
> [Dim_Vendor].[opco_vendor_num]<>1850) AND
> [Dim_Vendor].[opco_vendor_num]<>1940) AND
> [Dim_Vendor].[opco_vendor_num]<>2210) AND
> [Dim_Vendor].[opco_vendor_num]<>2215) AND
> [Dim_Vendor].[opco_vendor_num]<>3130) AND
> [Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
> |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
> SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
> WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED
FORWARD)
>
> Show plan text (working)
> |--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Hash Match(Inner Join,
> HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
> RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
> |--Parallelism(Broadcast)
> | |--Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
> |--Hash Match(Inner Join,
> HASH:([fact_sales].[opco_num],
> [fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
> [dim_inv_info_tbl].[item_num]),
> RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
> [fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
> | |--Hash Match(Inner Join,
> HASH:([Dim_item].[opco_num],
> [Dim_item].[item_ident])=([fact_sales].[opco_num],
> [fact_sales].[item_ident]),
> RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
> [Dim_item].[item_ident]=[fact_sales].[item_ident]))
> | |--Bitmap(HASH:([Dim_item].[opco_num],
> [Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
> | | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS:([Dim_item].[opco_num],
[Dim_item].[item_ident]))
> | | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
> | |--Parallelism(Repartition Streams,
> PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
> WHERE:(PROBE([Bitmap1006])=TRUE))
> | |--Hash Match(Inner Join,
> HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
> RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
> | |--Parallelism(Broadcast)
> | | |--Nested Loops(Left
> Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
> | |
> |--Parallelism(Gather Streams)
> | | |
> |--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
> Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
> OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
> | | |
> |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
> [Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
> | | |--Constant Scan
> | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
> [fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
> 12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
> |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
> AS [dim_inv_info_tbl]))
>|||Brian Moran wrote:
> 1. I would update stats to make sure that's not an issue...
> 2. CXPacket indicates a waittime assoicated with parrallel query plans. Try
> setting the MAXDOP to 1 as a test. The slow plan is probably parallel and
> the fast plan may be serial.
>
Thanks,
I guess I should have specified that MAXDOP is not an option, this
query is being run through a tool that does not allow direct
modification of the query. I will check the stats, thanks

Query LDAP user group membership from SQL Server

this is driving me nuts!
all i want is a simple list of groups and members for each group in
Active Directory. I read numorous postings on this topic and no one
seems to know how to query it from sql server directly.
why can't Microsoft provide some kind of schema (views) for that?
i can issue a query like this
SELECT a.name, a.adspath, b.name, b.adspath
FROM OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://server/ DC=mydomain,DC=com''
WHERE objectCategory = ''person''') a,
OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://server/ DC=mydomain,DC=com''
WHERE objectCategory = ''group''') b
but there is no relationship i can join between the two to connect the
dots between groups and users.
the problems i have is that i'm not a VB programmer, and i am not a
network admin and don't knwo how to use some of the vb code samples
provided in the newsgroup (see below). unless someone has a more
comprehesive link for how to set those scripts up.
---
To the best of my knowledge, you can retrieve MemberOf in
your list of attributes, but you cannot query on it. You
must return a recordset with memberof among the
attributes, then enumerate the recordset and look for the
info you need. MemberOf will be an array. Use:
'<LDAP://myServer/cn=users,dc=myDomain,dc=com>;(&
(objectCategory=Person)(objectClass=user
))
;displayname, memberOf, objectCategory, cn,
adspath;subtree'
In your example, that means returning a recordset of all
users. If RS is the recordset, I code the following in
VBScript.
colMembers = RS.Fields("MemberOf")
For Each Item in colMembers
Wscript.Echo Item
---
either that, or does anyone knows how to script out that info from
Active Directory and output it to a text file for sql to pick up? I
just want a simple two column file to tell me all the groups and
members for each group. why would it be difficult?Hi
You need to look at the memberof attribute.
http://msdn.microsoft.com/library/d...ace_mapping.asp
This may also help
http://www.rlmueller.net/List%20User%20Groups.htm
John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1108757700.331401.193420@.o13g2000cwo.googlegroups.com...
> this is driving me nuts!
> all i want is a simple list of groups and members for each group in
> Active Directory. I read numorous postings on this topic and no one
> seems to know how to query it from sql server directly.
> why can't Microsoft provide some kind of schema (views) for that?
> i can issue a query like this
> SELECT a.name, a.adspath, b.name, b.adspath
> FROM OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://server/ DC=mydomain,DC=com''
> WHERE objectCategory = ''person''') a,
> OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://server/ DC=mydomain,DC=com''
> WHERE objectCategory = ''group''') b
> but there is no relationship i can join between the two to connect the
> dots between groups and users.
> the problems i have is that i'm not a VB programmer, and i am not a
> network admin and don't knwo how to use some of the vb code samples
> provided in the newsgroup (see below). unless someone has a more
> comprehesive link for how to set those scripts up.
> ---
> To the best of my knowledge, you can retrieve MemberOf in
> your list of attributes, but you cannot query on it. You
> must return a recordset with memberof among the
> attributes, then enumerate the recordset and look for the
> info you need. MemberOf will be an array. Use:
> '<LDAP://myServer/cn=users,dc=myDomain,dc=com>;(&
> (objectCategory=Person)(objectClass=user
))
> ;displayname, memberOf, objectCategory, cn,
> adspath;subtree'
> In your example, that means returning a recordset of all
> users. If RS is the recordset, I code the following in
> VBScript.
> colMembers = RS.Fields("MemberOf")
> For Each Item in colMembers
> Wscript.Echo Item
> ---
> either that, or does anyone knows how to script out that info from
> Active Directory and output it to a text file for sql to pick up? I
> just want a simple two column file to tell me all the groups and
> members for each group. why would it be difficult?
>|||thanks for the reply. but i'm not trying to look up a window user
account's group info. i can see that in Active directory. what i need
is a way to script out all the groups and users info from AD and there
should a membership relationship like
user memberof
u1 grp1
u1 grp2
u1 grp3
u2 grp2...
and so on.
again, i'm not a vb programmer, so i need some intructions as to how to
run a script and so on. i have seen those links you posted before. i
couldnt' get them to work. something is missing from the instructions.
can anyone fill the gap?
thank you.|||Yeah John but have u been able to retrieve GROUPS from the Active
Diretory?
For example i want to get a USER and retrieve the GROUPS they belong to.
Any LUCK using OPEN QUERY?
*** Sent via Developersdex http://www.examnotes.net ***