I have been trying to set up permissions for a SQL user ID
on a specific table. The user will access this table using
MS Access 2000 with a standard ODBC connection. I only
want the user to be able to view data from this table.
When I set up a user role in SQL associated with this
user, the permissions on this role are set to deny insert,
update and delete capabilities. This does not appear to
work. However, if I change the permissions on the public
role to deny insert, update, and delete capabilities this
works.
I know every user assumes the public role but I can't seem
to override the permissions set on the public role.
The SQL books online documentation states that a denied
permission always takes prcedent. I cannot seem to make
this work when I have a specific role for this user which
has the deny permissions set.
Thanks,
Jim d'HulstIs the user perhaps an admin on the server and thus inheriting sysadmin via
the Builtin\System role created by default? ( I normall remove this pretty q
uickly)
Alicia
www.sqlporn.co.uk|||Alicia, the user is jnot an admin on the server nor does
this SQL user id belong to any system administration roles.
What I find is if I use windows NT authentication and
assign the permissions the a windows users it works fine.
It is just when I use a SQL user ID that this doesn't seem
to work.
Thanks,
Jim
>--Original Message--
>Is the user perhaps an admin on the server and thus
inheriting sysadmin via the Builtin\System role created by
default? ( I normall remove this pretty quickly)
>Alicia
>www.sqlporn.co.uk
>.
>|||Jim,
Overriding public should be quite doable. Since it is working for your
domain account, I would expect it to work for a SQL account as well. With
all due deference, I suggest that it is probably something simple, but just
hard to see. Silly possibilities:
There is a guest account and the user name is misspelled such that the user
comes in as guest.
The user is also in the db_owner role for the database.
Russell Fields
"jim.dhulst@.am.dynonobel.com" <anonymous@.discussions.microsoft.com> wrote in
message news:fba601c43e7d$96ba1c70$a301280a@.phx.gbl...[vbcol=seagreen]
> Alicia, the user is jnot an admin on the server nor does
> this SQL user id belong to any system administration roles.
> What I find is if I use windows NT authentication and
> assign the permissions the a windows users it works fine.
> It is just when I use a SQL user ID that this doesn't seem
> to work.
> Thanks,
> Jim
> inheriting sysadmin via the Builtin\System role created by
> default? ( I normall remove this pretty quickly)
Showing posts with label resolving. Show all posts
Showing posts with label resolving. Show all posts
Friday, March 30, 2012
Resolving function USER
If I log in to Query Analyzer using Windows Auth and run "PRINT USER", it returns "dbo". Is there any way to have the USER function actually return <Domain>\<login> for myself?Look up suser_name and suser_sname.
Resolving duplicates entries in table among 10 databases
Hi
Our product uses MS-SQL Server 2000. One of our customer has 10
installations with each installation stroring data in its own database.
Now the customer wants to consolidate these databases into one and we
already have plan for that by consolidating one DB at a time. But first
they want to find how many unique or duplicate entries they have across
all the 10 databases
Assumptions:
1. All the databases reside on the same server. (This is just an
assumption, not the real environment at customer site)
2. Databases can not be merged before it is found how many unique or
duplicate rows exist.
Table under consideration:
Message
(
HashID PK,
...
)
# of rows in Message table in each of databases: 1 Million
Here is my question: How can I find how many unique or duplicate
entries they have across all the 10 databases. I easily find unique
rows for two databases with a query like this:
SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL
How can I do this for 10 databases. This will require factorial of 10
queries to solve this problem.
I will appreciate if someone can provide hint on this.
Regards
AK> Here is my question: How can I find how many unique or duplicate
> entries they have across all the 10 databases.
The following will list the count of unique values (Duplicates = 0) as well
has the non-unique values grouped by the number of duplicates (1-9).
SELECT
Duplicates,
(Duplicates + 1) * COUNT(*) AS TotalHashIDCount
FROM (
SELECT HashID, COUNT(*) - 1 AS Duplicates
FROM (
SELECT HashID FROM db1.dbo.Message
UNION ALL SELECT HashID FROM db2.dbo.Message
UNION ALL SELECT HashID FROM db3.dbo.Message
UNION ALL SELECT HashID FROM db4.dbo.Message
UNION ALL SELECT HashID FROM db5.dbo.Message
UNION ALL SELECT HashID FROM db6.dbo.Message
UNION ALL SELECT HashID FROM db7.dbo.Message
UNION ALL SELECT HashID FROM db8.dbo.Message
UNION ALL SELECT HashID FROM db9.dbo.Message
UNION ALL SELECT HashID FROM db10.dbo.Message
) AS Messages
GROUP BY HashID) AS HashIDCounts
GROUP BY Duplicates
ORDER BY Duplicates
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AK" <ambkh@.yahoo.com> wrote in message
news:1139679057.443925.49210@.g44g2000cwa.googlegro ups.com...
> Hi
> Our product uses MS-SQL Server 2000. One of our customer has 10
> installations with each installation stroring data in its own database.
> Now the customer wants to consolidate these databases into one and we
> already have plan for that by consolidating one DB at a time. But first
> they want to find how many unique or duplicate entries they have across
> all the 10 databases
> Assumptions:
> 1. All the databases reside on the same server. (This is just an
> assumption, not the real environment at customer site)
> 2. Databases can not be merged before it is found how many unique or
> duplicate rows exist.
> Table under consideration:
> Message
> (
> HashID PK,
> ...
> )
> # of rows in Message table in each of databases: 1 Million
> Here is my question: How can I find how many unique or duplicate
> entries they have across all the 10 databases. I easily find unique
> rows for two databases with a query like this:
> SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
> db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL
> How can I do this for 10 databases. This will require factorial of 10
> queries to solve this problem.
> I will appreciate if someone can provide hint on this.
> Regards
> AK|||Thank you Dan. This is exactly what I needed (in fact more than what I
needed :)
Regards
AK
Dan Guzman wrote:
> > Here is my question: How can I find how many unique or duplicate
> > entries they have across all the 10 databases.
> The following will list the count of unique values (Duplicates = 0) as well
> has the non-unique values grouped by the number of duplicates (1-9).
> SELECT
> Duplicates,
> (Duplicates + 1) * COUNT(*) AS TotalHashIDCount
> FROM (
> SELECT HashID, COUNT(*) - 1 AS Duplicates
> FROM (
> SELECT HashID FROM db1.dbo.Message
> UNION ALL SELECT HashID FROM db2.dbo.Message
> UNION ALL SELECT HashID FROM db3.dbo.Message
> UNION ALL SELECT HashID FROM db4.dbo.Message
> UNION ALL SELECT HashID FROM db5.dbo.Message
> UNION ALL SELECT HashID FROM db6.dbo.Message
> UNION ALL SELECT HashID FROM db7.dbo.Message
> UNION ALL SELECT HashID FROM db8.dbo.Message
> UNION ALL SELECT HashID FROM db9.dbo.Message
> UNION ALL SELECT HashID FROM db10.dbo.Message
> ) AS Messages
> GROUP BY HashID) AS HashIDCounts
> GROUP BY Duplicates
> ORDER BY Duplicates
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AK" <ambkh@.yahoo.com> wrote in message
> news:1139679057.443925.49210@.g44g2000cwa.googlegro ups.com...
> > Hi
> > Our product uses MS-SQL Server 2000. One of our customer has 10
> > installations with each installation stroring data in its own database.
> > Now the customer wants to consolidate these databases into one and we
> > already have plan for that by consolidating one DB at a time. But first
> > they want to find how many unique or duplicate entries they have across
> > all the 10 databases
> > Assumptions:
> > 1. All the databases reside on the same server. (This is just an
> > assumption, not the real environment at customer site)
> > 2. Databases can not be merged before it is found how many unique or
> > duplicate rows exist.
> > Table under consideration:
> > Message
> > (
> > HashID PK,
> > ...
> > )
> > # of rows in Message table in each of databases: 1 Million
> > Here is my question: How can I find how many unique or duplicate
> > entries they have across all the 10 databases. I easily find unique
> > rows for two databases with a query like this:
> > SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
> > db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL
> > How can I do this for 10 databases. This will require factorial of 10
> > queries to solve this problem.
> > I will appreciate if someone can provide hint on this.
> > Regards
> > AK|||Better too much than too little :-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AK" <ambkh@.yahoo.com> wrote in message
news:1139936166.210532.262820@.g43g2000cwa.googlegr oups.com...
> Thank you Dan. This is exactly what I needed (in fact more than what I
> needed :)
> Regards
> AK
> Dan Guzman wrote:
>> > Here is my question: How can I find how many unique or duplicate
>> > entries they have across all the 10 databases.
>>
>> The following will list the count of unique values (Duplicates = 0) as
>> well
>> has the non-unique values grouped by the number of duplicates (1-9).
>>
>> SELECT
>> Duplicates,
>> (Duplicates + 1) * COUNT(*) AS TotalHashIDCount
>> FROM (
>> SELECT HashID, COUNT(*) - 1 AS Duplicates
>> FROM (
>> SELECT HashID FROM db1.dbo.Message
>> UNION ALL SELECT HashID FROM db2.dbo.Message
>> UNION ALL SELECT HashID FROM db3.dbo.Message
>> UNION ALL SELECT HashID FROM db4.dbo.Message
>> UNION ALL SELECT HashID FROM db5.dbo.Message
>> UNION ALL SELECT HashID FROM db6.dbo.Message
>> UNION ALL SELECT HashID FROM db7.dbo.Message
>> UNION ALL SELECT HashID FROM db8.dbo.Message
>> UNION ALL SELECT HashID FROM db9.dbo.Message
>> UNION ALL SELECT HashID FROM db10.dbo.Message
>> ) AS Messages
>> GROUP BY HashID) AS HashIDCounts
>> GROUP BY Duplicates
>> ORDER BY Duplicates
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "AK" <ambkh@.yahoo.com> wrote in message
>> news:1139679057.443925.49210@.g44g2000cwa.googlegro ups.com...
>> > Hi
>>> > Our product uses MS-SQL Server 2000. One of our customer has 10
>> > installations with each installation stroring data in its own database.
>> > Now the customer wants to consolidate these databases into one and we
>> > already have plan for that by consolidating one DB at a time. But first
>> > they want to find how many unique or duplicate entries they have across
>> > all the 10 databases
>>> > Assumptions:
>> > 1. All the databases reside on the same server. (This is just an
>> > assumption, not the real environment at customer site)
>> > 2. Databases can not be merged before it is found how many unique or
>> > duplicate rows exist.
>>> > Table under consideration:
>> > Message
>> > (
>> > HashID PK,
>> > ...
>> > )
>>> > # of rows in Message table in each of databases: 1 Million
>>> > Here is my question: How can I find how many unique or duplicate
>> > entries they have across all the 10 databases. I easily find unique
>> > rows for two databases with a query like this:
>>> > SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
>> > db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL
>>> > How can I do this for 10 databases. This will require factorial of 10
>> > queries to solve this problem.
>>> > I will appreciate if someone can provide hint on this.
>>> > Regards
>> > AK
>sql
Resolving DNS names from extended stored procedures
I've developed an extended stored procedure in C and want to resolve a
hostname from within the stored procedure using DNS. I use getaddrinfo() to
try and resolve the name. It never works from within the extended stored
procedure when running within SQL Server 2000 (SP3a applied). A hard-coded
IP address does work. The error I get back is WSAHOST_NOT_FOUND (11001). If
I do an NSLOOKUP of the same hostname on the same machine, it works fine, so
the server is configured OK. SQL Server 2000 is running using an account
that does have network access; when I specify an IP address to my stored
procedure, which turns around and establishes a TCP connection to a
specified port and sends XML over the connection, it works.
Are there any known issues resolving DNS names from within extended stored
procedures?
Thanks in advance.
Rick
mailto:rgenter "at" silverlink.com
Are you doing a WSAStartup in your XP?
I have no problems doing gethostbyname to resolve the hostname back to an IP
address.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Rick Genter" <rgenter@.silverlink.com> wrote in message
news:eJhtjM%23qEHA.2604@.TK2MSFTNGP10.phx.gbl...
> I've developed an extended stored procedure in C and want to resolve a
> hostname from within the stored procedure using DNS. I use getaddrinfo()
> to try and resolve the name. It never works from within the extended
> stored procedure when running within SQL Server 2000 (SP3a applied). A
> hard-coded IP address does work. The error I get back is WSAHOST_NOT_FOUND
> (11001). If I do an NSLOOKUP of the same hostname on the same machine, it
> works fine, so the server is configured OK. SQL Server 2000 is running
> using an account that does have network access; when I specify an IP
> address to my stored procedure, which turns around and establishes a TCP
> connection to a specified port and sends XML over the connection, it
> works.
> Are there any known issues resolving DNS names from within extended stored
> procedures?
> Thanks in advance.
> Rick
> --
> mailto:rgenter "at" silverlink.com
>
hostname from within the stored procedure using DNS. I use getaddrinfo() to
try and resolve the name. It never works from within the extended stored
procedure when running within SQL Server 2000 (SP3a applied). A hard-coded
IP address does work. The error I get back is WSAHOST_NOT_FOUND (11001). If
I do an NSLOOKUP of the same hostname on the same machine, it works fine, so
the server is configured OK. SQL Server 2000 is running using an account
that does have network access; when I specify an IP address to my stored
procedure, which turns around and establishes a TCP connection to a
specified port and sends XML over the connection, it works.
Are there any known issues resolving DNS names from within extended stored
procedures?
Thanks in advance.
Rick
mailto:rgenter "at" silverlink.com
Are you doing a WSAStartup in your XP?
I have no problems doing gethostbyname to resolve the hostname back to an IP
address.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Rick Genter" <rgenter@.silverlink.com> wrote in message
news:eJhtjM%23qEHA.2604@.TK2MSFTNGP10.phx.gbl...
> I've developed an extended stored procedure in C and want to resolve a
> hostname from within the stored procedure using DNS. I use getaddrinfo()
> to try and resolve the name. It never works from within the extended
> stored procedure when running within SQL Server 2000 (SP3a applied). A
> hard-coded IP address does work. The error I get back is WSAHOST_NOT_FOUND
> (11001). If I do an NSLOOKUP of the same hostname on the same machine, it
> works fine, so the server is configured OK. SQL Server 2000 is running
> using an account that does have network access; when I specify an IP
> address to my stored procedure, which turns around and establishes a TCP
> connection to a specified port and sends XML over the connection, it
> works.
> Are there any known issues resolving DNS names from within extended stored
> procedures?
> Thanks in advance.
> Rick
> --
> mailto:rgenter "at" silverlink.com
>
Resolving DNS names from extended stored procedures
I've developed an extended stored procedure in C and want to resolve a
hostname from within the stored procedure using DNS. I use getaddrinfo() to
try and resolve the name. It never works from within the extended stored
procedure when running within SQL Server 2000 (SP3a applied). A hard-coded
IP address does work. The error I get back is WSAHOST_NOT_FOUND (11001). If
I do an NSLOOKUP of the same hostname on the same machine, it works fine, so
the server is configured OK. SQL Server 2000 is running using an account
that does have network access; when I specify an IP address to my stored
procedure, which turns around and establishes a TCP connection to a
specified port and sends XML over the connection, it works.
Are there any known issues resolving DNS names from within extended stored
procedures?
Thanks in advance.
Rick
--
mailto:rgenter "at" silverlink.comAre you doing a WSAStartup in your XP?
I have no problems doing gethostbyname to resolve the hostname back to an IP
address.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Rick Genter" <rgenter@.silverlink.com> wrote in message
news:eJhtjM%23qEHA.2604@.TK2MSFTNGP10.phx.gbl...
> I've developed an extended stored procedure in C and want to resolve a
> hostname from within the stored procedure using DNS. I use getaddrinfo()
> to try and resolve the name. It never works from within the extended
> stored procedure when running within SQL Server 2000 (SP3a applied). A
> hard-coded IP address does work. The error I get back is WSAHOST_NOT_FOUND
> (11001). If I do an NSLOOKUP of the same hostname on the same machine, it
> works fine, so the server is configured OK. SQL Server 2000 is running
> using an account that does have network access; when I specify an IP
> address to my stored procedure, which turns around and establishes a TCP
> connection to a specified port and sends XML over the connection, it
> works.
> Are there any known issues resolving DNS names from within extended stored
> procedures?
> Thanks in advance.
> Rick
> --
> mailto:rgenter "at" silverlink.com
>
hostname from within the stored procedure using DNS. I use getaddrinfo() to
try and resolve the name. It never works from within the extended stored
procedure when running within SQL Server 2000 (SP3a applied). A hard-coded
IP address does work. The error I get back is WSAHOST_NOT_FOUND (11001). If
I do an NSLOOKUP of the same hostname on the same machine, it works fine, so
the server is configured OK. SQL Server 2000 is running using an account
that does have network access; when I specify an IP address to my stored
procedure, which turns around and establishes a TCP connection to a
specified port and sends XML over the connection, it works.
Are there any known issues resolving DNS names from within extended stored
procedures?
Thanks in advance.
Rick
--
mailto:rgenter "at" silverlink.comAre you doing a WSAStartup in your XP?
I have no problems doing gethostbyname to resolve the hostname back to an IP
address.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Rick Genter" <rgenter@.silverlink.com> wrote in message
news:eJhtjM%23qEHA.2604@.TK2MSFTNGP10.phx.gbl...
> I've developed an extended stored procedure in C and want to resolve a
> hostname from within the stored procedure using DNS. I use getaddrinfo()
> to try and resolve the name. It never works from within the extended
> stored procedure when running within SQL Server 2000 (SP3a applied). A
> hard-coded IP address does work. The error I get back is WSAHOST_NOT_FOUND
> (11001). If I do an NSLOOKUP of the same hostname on the same machine, it
> works fine, so the server is configured OK. SQL Server 2000 is running
> using an account that does have network access; when I specify an IP
> address to my stored procedure, which turns around and establishes a TCP
> connection to a specified port and sends XML over the connection, it
> works.
> Are there any known issues resolving DNS names from within extended stored
> procedures?
> Thanks in advance.
> Rick
> --
> mailto:rgenter "at" silverlink.com
>
Resolving deadlock
Hi All,
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBAHi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:
> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I have
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBAHi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:
> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I have
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>
Resolving deadlock
Hi All,
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBA
Hi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:
> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I have
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBA
Hi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:
> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I have
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>
Resolving deadlock
Hi All,
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBAHi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:
> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I hav
e
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>sql
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBAHi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:
> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I hav
e
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>sql
Resolving an attribute member from a member property
What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?
You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.
E.g.
select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget
This is not what I was asking. Consider the following key attribute and member properties
Product (attribute key)
- ProductOriginal Key (member property and/or attribute)
The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?
|||Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using
Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])
HTH,
Mosha (http://www.mosha.com/msolap)
|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:
1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]
4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')
Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.
HTH,
Mosha (http://www.mosha.com/msolap)
|||This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?
Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')
I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.
|||Yes, I beleive this is the best way to do it.Resolving an attribute member from a member property
What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?
You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.
E.g.
select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget
This is not what I was asking. Consider the following key attribute and member properties
Product (attribute key)
- ProductOriginal Key (member property and/or attribute)
The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?
|||Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using
Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])
HTH,
Mosha (http://www.mosha.com/msolap)
|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:
1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]
4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')
Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.
HTH,
Mosha (http://www.mosha.com/msolap)
|||This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?
Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')
I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.
|||Yes, I beleive this is the best way to do it.Resolving a many-to-many relationship
This question isn't specific to SS2000, but since there are many here
familiar with database design I thought I'd ask anyhow.
Imagine these two tables for a company's database in which several
salespeople work together on any particular order:
Salespeople (EmployeeID, Name, Location)
Orders (OrderID, items ordered, salespeople involved)
I realize there are problems with repeating groups, but what I'm interested
in is resolving the many-to-many relationship. Each salesperson gets credit
for many sales, and each sale has many salespeople associated with it.
That's a many-to-many relationship, right? As I understand things, a
"composite table" is needed to break down the one many-to-many relationship
into 2 one-to-many relationships. Am I right so far?
I'm stumped as to what composite table I need. Any ideas?
JoshYou could create a table that would store the EmployeeID and the =OrderID. These columns would be the composite primary key and they =would be foreign keys to the respective tables.
-- Keith
"Josh Meyer" <jmeyer@.msg.ucsf.edu> wrote in message =news:uqLjbh1YDHA.3444@.tk2msftngp13.phx.gbl...
> > > This question isn't specific to SS2000, but since there are many here
> familiar with database design I thought I'd ask anyhow.
> > Imagine these two tables for a company's database in which several
> salespeople work together on any particular order:
> > Salespeople (EmployeeID, Name, Location)
> Orders (OrderID, items ordered, salespeople involved)
> > I realize there are problems with repeating groups, but what I'm =interested
> in is resolving the many-to-many relationship. Each salesperson gets =credit
> for many sales, and each sale has many salespeople associated with it.
> That's a many-to-many relationship, right? As I understand things, a
> "composite table" is needed to break down the one many-to-many =relationship
> into 2 one-to-many relationships. Am I right so far?
> > I'm stumped as to what composite table I need. Any ideas?
> > Josh
> > >
familiar with database design I thought I'd ask anyhow.
Imagine these two tables for a company's database in which several
salespeople work together on any particular order:
Salespeople (EmployeeID, Name, Location)
Orders (OrderID, items ordered, salespeople involved)
I realize there are problems with repeating groups, but what I'm interested
in is resolving the many-to-many relationship. Each salesperson gets credit
for many sales, and each sale has many salespeople associated with it.
That's a many-to-many relationship, right? As I understand things, a
"composite table" is needed to break down the one many-to-many relationship
into 2 one-to-many relationships. Am I right so far?
I'm stumped as to what composite table I need. Any ideas?
JoshYou could create a table that would store the EmployeeID and the =OrderID. These columns would be the composite primary key and they =would be foreign keys to the respective tables.
-- Keith
"Josh Meyer" <jmeyer@.msg.ucsf.edu> wrote in message =news:uqLjbh1YDHA.3444@.tk2msftngp13.phx.gbl...
> > > This question isn't specific to SS2000, but since there are many here
> familiar with database design I thought I'd ask anyhow.
> > Imagine these two tables for a company's database in which several
> salespeople work together on any particular order:
> > Salespeople (EmployeeID, Name, Location)
> Orders (OrderID, items ordered, salespeople involved)
> > I realize there are problems with repeating groups, but what I'm =interested
> in is resolving the many-to-many relationship. Each salesperson gets =credit
> for many sales, and each sale has many salespeople associated with it.
> That's a many-to-many relationship, right? As I understand things, a
> "composite table" is needed to break down the one many-to-many =relationship
> into 2 one-to-many relationships. Am I right so far?
> > I'm stumped as to what composite table I need. Any ideas?
> > Josh
> > >
Resolving
Hello!
I am having trouble connection to my SQL 2000 SP4 using TCP IP.
I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?
Carl
Norway
What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.
The following link have plenty of the content on troubleshooting connectivity issues.
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
cheers,
|||Thanks!
But that was concerning SQL2005. My server is 2000 SP4.
But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.
Carl
|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||How can I identify the Browser Service?
I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.
As of now I can not see any difference in the configuration.
sqlResolving
Hello!
I am having trouble connection to my SQL 2000 SP4 using TCP IP.
I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?
Carl
Norway
What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.
The following link have plenty of the content on troubleshooting connectivity issues.
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
cheers,
|||Thanks!
But that was concerning SQL2005. My server is 2000 SP4.
But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.
Carl
|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||How can I identify the Browser Service?
I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.
As of now I can not see any difference in the configuration.
Resolving
Hello!
I am having trouble connection to my SQL 2000 SP4 using TCP IP.
I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?
Carl
Norway
What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.
The following link have plenty of the content on troubleshooting connectivity issues.
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
cheers,
|||Thanks!
But that was concerning SQL2005. My server is 2000 SP4.
But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.
Carl
|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||How can I identify the Browser Service?
I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.
As of now I can not see any difference in the configuration.
Subscribe to:
Posts (Atom)