I've generated a report that will need to display different information
based upon where it is being run. For instance, if the report is executing
on our DVLP server, i need a title to say "Developement". Is there is simple
way to determine what environment the report is being run in? Check the URL?
Server variables?
Thanks,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com=System.Environment.MachineName
Warning: You'll need to give expression host FullTrust in order to use this
expression on report server, which may be a security risk.
If your SQL Server is on the same machine, creating a dataset against it and
using SELECT @.@.servername would be a better approach.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:uZDVm2lbEHA.3988@.tk2msftngp13.phx.gbl...
> I've generated a report that will need to display different information
> based upon where it is being run. For instance, if the report is executing
> on our DVLP server, i need a title to say "Developement". Is there is
simple
> way to determine what environment the report is being run in? Check the
URL?
> Server variables?
> Thanks,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>sql
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Determine Duration between Two Dates
All
I have a table of members and I would like to determine how long each member
has been a member based upon the current date. I would like the result
retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
assistance would be appreciated.
CREATE TABLE member
(
MemberID INT,
DateJoined SMALLDATETIME
)
INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
Desired Result:
1 1999-01-11 2005-10-13 6y 9m 2d
2 2004-12-26 2005-10-13 0y 10m 18d
3 2005-01-01 2005-10-13 0y 10m 12d
ThanksHi David,
There must be a simpler way than this, but what the heck...
SELECT id, dt, y, m,
diff_d - CASE WHEN dt_ym + diff_d > today THEN 1 ELSE 0 END AS d
FROM
(
SELECT *, DATEDIFF(day, dt_ym, today) AS diff_d
FROM
(
SELECT id, dt, today, y, m,
DATEADD(month, m, dt_y) AS dt_ym
FROM
(
SELECT id, dt, today, y, dt_y,
m_diff - CASE WHEN DATEADD(month, m_diff, dt_y) > today
THEN 1 ELSE 0 END AS m
FROM
(
SELECT *, DATEDIFF(month, dt_y, today) AS m_diff
FROM
(
SELECT *, DATEADD(year, y, dt) AS dt_y
FROM
(
SELECT id, dt, today,
y_diff - CASE WHEN DATEADD(year, y_diff, dt) > today
THEN 1 ELSE 0 END AS y
FROM
(
SELECT *, DATEDIFF(year, dt, today) AS y_diff
FROM
(
SELECT MemberID AS id, DateJoined AS dt,
CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME) AS today
FROM member
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
) AS D7
) AS D8;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"David" <David@.discussions.microsoft.com> wrote in message
news:44CF768B-1213-453F-8A62-024857AF5089@.microsoft.com...
> All
> I have a table of members and I would like to determine how long each
> member
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks|||Hi David
Probably you can check the link.
http://chanduas.blogspot.com/2005/0...lating-age.html
This is not the exact solution but can give u an idea
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David" wrote:
> All
> I have a table of members and I would like to determine how long each memb
er
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks
I have a table of members and I would like to determine how long each member
has been a member based upon the current date. I would like the result
retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
assistance would be appreciated.
CREATE TABLE member
(
MemberID INT,
DateJoined SMALLDATETIME
)
INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
Desired Result:
1 1999-01-11 2005-10-13 6y 9m 2d
2 2004-12-26 2005-10-13 0y 10m 18d
3 2005-01-01 2005-10-13 0y 10m 12d
ThanksHi David,
There must be a simpler way than this, but what the heck...
SELECT id, dt, y, m,
diff_d - CASE WHEN dt_ym + diff_d > today THEN 1 ELSE 0 END AS d
FROM
(
SELECT *, DATEDIFF(day, dt_ym, today) AS diff_d
FROM
(
SELECT id, dt, today, y, m,
DATEADD(month, m, dt_y) AS dt_ym
FROM
(
SELECT id, dt, today, y, dt_y,
m_diff - CASE WHEN DATEADD(month, m_diff, dt_y) > today
THEN 1 ELSE 0 END AS m
FROM
(
SELECT *, DATEDIFF(month, dt_y, today) AS m_diff
FROM
(
SELECT *, DATEADD(year, y, dt) AS dt_y
FROM
(
SELECT id, dt, today,
y_diff - CASE WHEN DATEADD(year, y_diff, dt) > today
THEN 1 ELSE 0 END AS y
FROM
(
SELECT *, DATEDIFF(year, dt, today) AS y_diff
FROM
(
SELECT MemberID AS id, DateJoined AS dt,
CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME) AS today
FROM member
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
) AS D7
) AS D8;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel![url]http://www.microsoft.com/israel/sql/sqlw
/default.mspx[/url]"David" <David@.discussions.microsoft.com> wrote in message
news:44CF768B-1213-453F-8A62-024857AF5089@.microsoft.com...
> All
> I have a table of members and I would like to determine how long each
> member
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks|||Hi David
Probably you can check the link.
http://chanduas.blogspot.com/2005/0...lating-age.html
This is not the exact solution but can give u an idea
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David" wrote:
> All
> I have a table of members and I would like to determine how long each memb
er
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks
Saturday, February 25, 2012
Designing Date and Time Dimension
I need to display reports based on an SSAS cube which requires both date and time as input parameters.
I'd like to know what is the best practice while designing dimensions in such a scenario. Do i need to design a separate dimension for date ( quarter, year, month, week, day) and time ( hour, minute, second) ?
Or will having just the date dimension with the date key pointing to the full datetime in the fact table suffice?
I guess there is no correct answer to this question, but in my opinion you should design two dimensions - one for the dates and one for the seconds (with related attributes, of course). If you put it all in one dimension, your time dimension would consist of 60 x 60 x 24 x 365 = 31,536,000 members for each year (!!!), whereas if you make two separate dimensions, you have a date dimension with 365 members for each year and a time dimension with 86,400 members (constant). This will be much more performant and simple to work with in most cases.Designing a generic database API
Hi,
My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..
But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.
In mysql, I accomplish this using
dbfield+interval '$arg' minute between date1 and date1
But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?
My frontend scripting language is php
Thanks d'advance
CeliaI would create a function in your PHP code that takes two arguments, a "date" and an "adder" to the date. That way you can "cook" them as needed for each database engine, while keeping the PHP quite generic.
-PatP
My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..
But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.
In mysql, I accomplish this using
dbfield+interval '$arg' minute between date1 and date1
But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?
My frontend scripting language is php
Thanks d'advance
CeliaI would create a function in your PHP code that takes two arguments, a "date" and an "adder" to the date. That way you can "cook" them as needed for each database engine, while keeping the PHP quite generic.
-PatP
Friday, February 24, 2012
Design to accomodate entity based schema versioning
in simple words it's about versioning at record level.
Example
TableEmployee - EmployeeId, EmployeeName,
EmployeeAddress, DepartmentId,
TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,
validity
TableDepartment - DepartmentId, Department
TableDesignation - DesignationId, designation
Via Modify-Employee-Details screen following are editable
EmoyeeName
EmployeeAddress
Department
Designation
this screen should allow user to navigate through changes history.
Example :
Version -1
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Accounts
Designation Accountant
Version -2
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Accounts
Designation Chief Accountant - changed
Version -3
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Sales - changed
Designation Marketing Manager - changed
Question :
What is the best proposed database design for maintaining history
records bound with version and retrieval technique
Best Regards
SasankaSasanka Pinidiya (pinidiya@.yahoo.com) writes:
> in simple words it's about versioning at record level.
> Example
> TableEmployee - EmployeeId, EmployeeName,
> EmployeeAddress, DepartmentId,
> TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,
> validity
> TableDepartment - DepartmentId, Department
> TableDesignation - DesignationId, designation
> Via Modify-Employee-Details screen following are editable
> EmoyeeName
> EmployeeAddress
> Department
> Designation
>...
> Question :
> What is the best proposed database design for maintaining history
> records bound with version and retrieval technique
There are several techniques, and which is the best depends on the actual
business case.
One techniques which makes it very simple to retrieve the data at a
certain date, is store one version each day. Clearly, this technique
is not appropriate here. This technique is good for things that changes
about daily as for instance currency prices.
Another technique is store key, date and then what changed. But this
makes is difficult to retrieve the complete information at a given point
in time.
So the best technique seems to be to have a table with the same columns
as the base table, and with a datetime column added to hold the time
of the change.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Design Question: Calculated Field
I'm trying to create an OLAP system using SQL Server 2000 Analysis
Services (AS). I want the AS cube to be based on a database with a
star schema.
Services (AS). I want the AS cube to be based on a database with a
star schema.
I have a field called Ratio. The initial ratio value is based upon
this formula: (CurrentTimePeriodAmount -
PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
initial ratio can be manually overridden and replaced. Then the ratio
is used to calculate future Amounts.
Should I place that calculated Ratio field in the "source" database
design or should it be part of the Analysis Services cube?What you may want to consider instead of creating a new field in the
physical table, is to use a view with the calculation there. I am a
huge advocate of using views for fact tables for the reason you are
talking about below.
The problem is if the ratio is going to be replaced/overridden you
need to ask yourself do you need to create a new record for this
ration value so you can track the changes over time. Are these changes
going to be adhoc, if so does your front-end tool allow for this type
of What If Functionality. Things to ask yourself.
HTH
-todd
imani_technology_spam@.yahoo.com wrote in message news:<8be6e8.0402030738.213518f7@.posting.google.com>...
> I'm trying to create an OLAP system using SQL Server 2000 Analysis
> Services (AS). I want the AS cube to be based on a database with a
> star schema.
> I have a field called Ratio. The initial ratio value is based upon
> this formula: (CurrentTimePeriodAmount -
> PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
> initial ratio can be manually overridden and replaced. Then the ratio
> is used to calculate future Amounts.
> Should I place that calculated Ratio field in the "source" database
> design or should it be part of the Analysis Services cube?|||I want to be able to display an initial, calculated ratio. I also
want a seperate field for the user to type in a ratio. How could that
be implemented? Also, I'm considering using SQL Server Analysis
Services with some kind of MS Excel 2000 front end.
toddack@.hotmail.com (Todd) wrote in message news:<a4f4b945.0402031412.4c3173fd@.posting.google.com>...
> What you may want to consider instead of creating a new field in the
> physical table, is to use a view with the calculation there. I am a
> huge advocate of using views for fact tables for the reason you are
> talking about below.
> The problem is if the ratio is going to be replaced/overridden you
> need to ask yourself do you need to create a new record for this
> ration value so you can track the changes over time. Are these changes
> going to be adhoc, if so does your front-end tool allow for this type
> of What If Functionality. Things to ask yourself.
> HTH
> -todd
> imani_technology_spam@.yahoo.com wrote in message news:<8be6e8.0402030738.213518f7@.posting.google.com>...
> > I'm trying to create an OLAP system using SQL Server 2000 Analysis
> > Services (AS). I want the AS cube to be based on a database with a
> > star schema.
> > I have a field called Ratio. The initial ratio value is based upon
> > this formula: (CurrentTimePeriodAmount -
> > PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
> > initial ratio can be manually overridden and replaced. Then the ratio
> > is used to calculate future Amounts.
> > Should I place that calculated Ratio field in the "source" database
> > design or should it be part of the Analysis Services cube?
Friday, February 17, 2012
Design issue please help
Hi all,
I do have four combination key for eg:1111-1111-1111-1111
the values can range from 0-0, based upon this i can create any number
of keys eg:1234-4567-4352-6534, these four values comes from different
tables, while joining the four i insert the new combination in a new
table .
In order to give security, i can give access rights to each parts for
eg: a user can have rights to 1234, but dont have the rights to 4567.
Iam using these keys in lot of my records , but i need to give a
checking based on the user , if he does not have the rights to a
particular segment , he wont be able to view , nor edit the record.
How do i implement this security on segment based,
Thanks in advance
Cheers
thomsonthomson
Look, I don't know your business requirements and obviously it depends on
bit permit me to suggest something
Please look at application role implemented by SQL Server as well as visit
on Vyas's greate web site to read below article
<http://vyaskn.tripod.com/sql_server...t_practices.htm> --se
curity best practices
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1114056973.285659.247590@.g14g2000cwa.googlegroups.com...
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Thomson,
The same problem i faced in my project and we have implemented in the
project manually[no supported my MS SQL].
For segment based manual security U need to have a separate table for this
which will let U know the which Key is accced by which User
KeyVal UserID SecurityLevel(RoleID)
1234 1 1
1234 3 2
6534 1 4
... ... ...
... ... ...
... ... ...
Hee SecurityLevel(RoleID) field has the ID/Level for Security.Security Level
may be like enum... or as u wish.
I think From this table you help you in getting the user ID with their
rights and role for a desired Keyval.
Am i clear to you ?
Please let me know.
NB: Please reply me on rakesh.ranjan@.3i-infotech.com
Thanking you
Rakesh Ranjan
rakesh.ranjan@.3i-infotech.com
Mumbai[India]
"thomson" wrote:
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Rakesh,
I think you got my point would you please explain it to me
the role of KeyVal and the Security Role id in Detail
Regards
thomson
RakeshRanjan wrote:
> Dear Thomson,
> The same problem i faced in my project and we have implemented in the
> project manually[no supported my MS SQL].
> For segment based manual security U need to have a separate table for
this
> which will let U know the which Key is accced by which User
> KeyVal UserID SecurityLevel(RoleID)
> 1234 1 1
> 1234 3 2
> 6534 1 4
> ... ... ...
> ... ... ...
> ... ... ...
> Hee SecurityLevel(RoleID) field has the ID/Level for
Security.Security Level
> may be like enum... or as u wish.
>
> I think From this table you help you in getting the user ID with
their
> rights and role for a desired Keyval.
> Am i clear to you ?
> Please let me know.
> NB: Please reply me on rakesh.ranjan@.3i-infotech.com
> Thanking you
> Rakesh Ranjan
> rakesh.ranjan@.3i-infotech.com
> Mumbai[India]
>
> "thomson" wrote:
>
number
different
new
for
4567.|||One more thing is that If you have mentioned the KeyVal value, as the
one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
between segments, or there can be any other Characters in between the
segments, So how will i tackle this
Regards
thomson|||Hi Tjhomson,
Key val is nothing but ur primary Key of all other table which make ur newly
generated Segmentkey(1234-4567-4352-6534).
You can have any charecter other than "-", It depends on you, how you want
to program it. This will be treated as a charecter for spliting your segment
key (which u have created from the four IDs).
Here the SecurityLevel(RoleID) field is rthe ID(Primary Key) of ur
security/role table which a user has, so that u can come to know the level o
f
user security.
Hope this much help u in better understanding. For further clarification
mail me on rakesh.ranjan@.3i-infotech.com
With regards,
Rakesh.
"thomson" wrote:
> Dear Rakesh,
> I think you got my point would you please explain it to me
> the role of KeyVal and the Security Role id in Detail
> Regards
> thomson
> RakeshRanjan wrote:
>
> this
> Security.Security Level
> their
> number
> different
> new
> for
> 4567.
>|||Hi rakesh,
The problem lies here that i dont want to give the security
to the whole key 1111-1111-1111-1111.
My issue is i have to give the security to each segments eg:1111
this 4 key can be generated by any one, its a one time setup, Then i
have to give security to each segments for each user.
suppose if i create a segment 1111-2222-3333-4444.
If i dont have the access to 1111, what i should not able to access the
records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
have access to the particular segment 1111.
Thanks in advance
thomson
thomson wrote:
> One more thing is that If you have mentioned the KeyVal value, as the
> one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
> between segments, or there can be any other Characters in between the
> segments, So how will i tackle this
> Regards
> thomson|||Hello Thomson,
Its little bit different than what I have Implemented. Though no issue , I
have a clue.
As u said the segment "1111-2222-3333-4444" is one time creation. Even
multiple time , I have no issue at all.
What u need to do is, after creation of the key "1111-2222-3333-4444" split
it into four parts i.e. 1111, 2222, 3333, 4444 and then Insert four records
in the said table with their rights( as i explained earlier), Now you know
which key has what access. so can can proceed further as per ur requirement.
For further please chat with me on mail "rakesh.ranjan@.3i-infotech.com".
With Regards,
Rakesh R.
"thomson" wrote:
> Hi rakesh,
> The problem lies here that i dont want to give the security
> to the whole key 1111-1111-1111-1111.
> My issue is i have to give the security to each segments eg:1111
> this 4 key can be generated by any one, its a one time setup, Then i
> have to give security to each segments for each user.
> suppose if i create a segment 1111-2222-3333-4444.
> If i dont have the access to 1111, what i should not able to access the
> records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
> have access to the particular segment 1111.
> Thanks in advance
>
> thomson
> thomson wrote:
>|||thomson,
You'll have to parse the key. If it's just 4 4-character pieces, it's
not hard.
select key from tableOfKeys
where not exists (
select deniedPermission from Permissions
where Permissions.theUser = @.theUser
and deniedPermission in (
substring(key,1,4),
substring(key,6,4),
substring(key,11,4),
substring(key,16,4)
)
)
If permissions are assigned positively, it's something like
select key from tableOfKeys
where not exists (
select keyPermission from (
select substring(key,1,4) union all
select substring(key,6,4) union all
select substring(key,11,4) union all
select substring(key,16,4)
) as K(keyPermission)
where keyPermission not in (
select permission from Permissions
where permission.theUser = @.theUser
)
)
[both untested]
Steve Kass
Drew University
thomson wrote:
>Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
>the values can range from 0-0, based upon this i can create any number
>of keys eg:1234-4567-4352-6534, these four values comes from different
>tables, while joining the four i insert the new combination in a new
>table .
>
> In order to give security, i can give access rights to each parts for
>eg: a user can have rights to 1234, but dont have the rights to 4567.
>Iam using these keys in lot of my records , but i need to give a
>checking based on the user , if he does not have the rights to a
>particular segment , he wont be able to view , nor edit the record.
>
>How do i implement this security on segment based,
>Thanks in advance
>Cheers
>thomson
>
>
I do have four combination key for eg:1111-1111-1111-1111
the values can range from 0-0, based upon this i can create any number
of keys eg:1234-4567-4352-6534, these four values comes from different
tables, while joining the four i insert the new combination in a new
table .
In order to give security, i can give access rights to each parts for
eg: a user can have rights to 1234, but dont have the rights to 4567.
Iam using these keys in lot of my records , but i need to give a
checking based on the user , if he does not have the rights to a
particular segment , he wont be able to view , nor edit the record.
How do i implement this security on segment based,
Thanks in advance
Cheers
thomsonthomson
Look, I don't know your business requirements and obviously it depends on
bit permit me to suggest something
Please look at application role implemented by SQL Server as well as visit
on Vyas's greate web site to read below article
<http://vyaskn.tripod.com/sql_server...t_practices.htm> --se
curity best practices
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1114056973.285659.247590@.g14g2000cwa.googlegroups.com...
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Thomson,
The same problem i faced in my project and we have implemented in the
project manually[no supported my MS SQL].
For segment based manual security U need to have a separate table for this
which will let U know the which Key is accced by which User
KeyVal UserID SecurityLevel(RoleID)
1234 1 1
1234 3 2
6534 1 4
... ... ...
... ... ...
... ... ...
Hee SecurityLevel(RoleID) field has the ID/Level for Security.Security Level
may be like enum... or as u wish.
I think From this table you help you in getting the user ID with their
rights and role for a desired Keyval.
Am i clear to you ?
Please let me know.
NB: Please reply me on rakesh.ranjan@.3i-infotech.com
Thanking you
Rakesh Ranjan
rakesh.ranjan@.3i-infotech.com
Mumbai[India]
"thomson" wrote:
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Rakesh,
I think you got my point would you please explain it to me
the role of KeyVal and the Security Role id in Detail
Regards
thomson
RakeshRanjan wrote:
> Dear Thomson,
> The same problem i faced in my project and we have implemented in the
> project manually[no supported my MS SQL].
> For segment based manual security U need to have a separate table for
this
> which will let U know the which Key is accced by which User
> KeyVal UserID SecurityLevel(RoleID)
> 1234 1 1
> 1234 3 2
> 6534 1 4
> ... ... ...
> ... ... ...
> ... ... ...
> Hee SecurityLevel(RoleID) field has the ID/Level for
Security.Security Level
> may be like enum... or as u wish.
>
> I think From this table you help you in getting the user ID with
their
> rights and role for a desired Keyval.
> Am i clear to you ?
> Please let me know.
> NB: Please reply me on rakesh.ranjan@.3i-infotech.com
> Thanking you
> Rakesh Ranjan
> rakesh.ranjan@.3i-infotech.com
> Mumbai[India]
>
> "thomson" wrote:
>
number
different
new
for
4567.|||One more thing is that If you have mentioned the KeyVal value, as the
one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
between segments, or there can be any other Characters in between the
segments, So how will i tackle this
Regards
thomson|||Hi Tjhomson,
Key val is nothing but ur primary Key of all other table which make ur newly
generated Segmentkey(1234-4567-4352-6534).
You can have any charecter other than "-", It depends on you, how you want
to program it. This will be treated as a charecter for spliting your segment
key (which u have created from the four IDs).
Here the SecurityLevel(RoleID) field is rthe ID(Primary Key) of ur
security/role table which a user has, so that u can come to know the level o
f
user security.
Hope this much help u in better understanding. For further clarification
mail me on rakesh.ranjan@.3i-infotech.com
With regards,
Rakesh.
"thomson" wrote:
> Dear Rakesh,
> I think you got my point would you please explain it to me
> the role of KeyVal and the Security Role id in Detail
> Regards
> thomson
> RakeshRanjan wrote:
>
> this
> Security.Security Level
> their
> number
> different
> new
> for
> 4567.
>|||Hi rakesh,
The problem lies here that i dont want to give the security
to the whole key 1111-1111-1111-1111.
My issue is i have to give the security to each segments eg:1111
this 4 key can be generated by any one, its a one time setup, Then i
have to give security to each segments for each user.
suppose if i create a segment 1111-2222-3333-4444.
If i dont have the access to 1111, what i should not able to access the
records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
have access to the particular segment 1111.
Thanks in advance
thomson
thomson wrote:
> One more thing is that If you have mentioned the KeyVal value, as the
> one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
> between segments, or there can be any other Characters in between the
> segments, So how will i tackle this
> Regards
> thomson|||Hello Thomson,
Its little bit different than what I have Implemented. Though no issue , I
have a clue.
As u said the segment "1111-2222-3333-4444" is one time creation. Even
multiple time , I have no issue at all.
What u need to do is, after creation of the key "1111-2222-3333-4444" split
it into four parts i.e. 1111, 2222, 3333, 4444 and then Insert four records
in the said table with their rights( as i explained earlier), Now you know
which key has what access. so can can proceed further as per ur requirement.
For further please chat with me on mail "rakesh.ranjan@.3i-infotech.com".
With Regards,
Rakesh R.
"thomson" wrote:
> Hi rakesh,
> The problem lies here that i dont want to give the security
> to the whole key 1111-1111-1111-1111.
> My issue is i have to give the security to each segments eg:1111
> this 4 key can be generated by any one, its a one time setup, Then i
> have to give security to each segments for each user.
> suppose if i create a segment 1111-2222-3333-4444.
> If i dont have the access to 1111, what i should not able to access the
> records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
> have access to the particular segment 1111.
> Thanks in advance
>
> thomson
> thomson wrote:
>|||thomson,
You'll have to parse the key. If it's just 4 4-character pieces, it's
not hard.
select key from tableOfKeys
where not exists (
select deniedPermission from Permissions
where Permissions.theUser = @.theUser
and deniedPermission in (
substring(key,1,4),
substring(key,6,4),
substring(key,11,4),
substring(key,16,4)
)
)
If permissions are assigned positively, it's something like
select key from tableOfKeys
where not exists (
select keyPermission from (
select substring(key,1,4) union all
select substring(key,6,4) union all
select substring(key,11,4) union all
select substring(key,16,4)
) as K(keyPermission)
where keyPermission not in (
select permission from Permissions
where permission.theUser = @.theUser
)
)
[both untested]
Steve Kass
Drew University
thomson wrote:
>Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
>the values can range from 0-0, based upon this i can create any number
>of keys eg:1234-4567-4352-6534, these four values comes from different
>tables, while joining the four i insert the new combination in a new
>table .
>
> In order to give security, i can give access rights to each parts for
>eg: a user can have rights to 1234, but dont have the rights to 4567.
>Iam using these keys in lot of my records , but i need to give a
>checking based on the user , if he does not have the rights to a
>particular segment , he wont be able to view , nor edit the record.
>
>How do i implement this security on segment based,
>Thanks in advance
>Cheers
>thomson
>
>
Tuesday, February 14, 2012
Design Advice
HI there my first post so be gentle...
I am redeveloping a web store which is an ASP based site and am looking to make life easier for myself and other staff at the company I work for as well as our customers. I have some problems with my existing data, it is becoming quite a task to manage and this boils down to receiving product information from Vendors in varying formats and in some cases partial data from two different vendors to make one catalogue.
When the site was originally developed it was designed for one "master" product table. We currently still use this and use catalogue numbers and barcodes as our keys to pulling the various supporting information out of our Vendor databases this works OK but we now have many duplicates but fixing the table is not an option as too much of the internal systems rely on this master table for looking up product information.
I have a plan which I am currently in the middle of conceiving, and hence asking for advice here, to re-code our website to directly browse and search purely on the Vendor databases, this way our customers can view the absolute best up to date catalogues and if we need to we can perform a complete reload of the data if we wish most importantly not affecting the other databases or our internal systems. To keep track of what the customers order I intend to add the important product information - such as price, supplier etc to an orders table. Our staff can then use this information to process the orders regardless of the status of my product data that is web facing. Now strictly speaking I dont think this design is correct, as I will be duplicating quite a bit of data into that already exists in my Vendor databases into my orders table and certain problems will arise that I can forsee already like having to write specific code for the browsing and searching of each Vendor database but I think the overall benefits outweigh the current setup. we use SQL server 2k and have several million rows of existing data that will need importing into the new structure aswell. What do you think of doing this i.e. keeping the product data and order data totally seperate and developing the website around this so essentially the add to shopping cart button on the website is what does the adding of data between the two.
phew long post...any advice appreciated!Are these vendor databases you are referring to - are they part of your system or are they the actual remote databases of your vendors ? And how does the "master" relate to these ?|||They make up the product range that we sell from the website and are supplied by the vendors as databases (normally dbf or csv format - but they will usually supply a schema). I will take these databases and provide facility to browse, search and order the products on the website.
As the rest of our system is designed to access a single table for the products, my code on the website takes the information from the Vendor databases and as the customers browse our site & add products to their cart some background code adds records into the master product table so that we can keep track of what they have in their cart.
This solution is not particularly suitable as it creates a lot of duplicate entries (if two different customers order the same product for example or our staff may delete a record for whatever reason and add a new record.)
what I am thinking is to eliminate the master table, and seperate my data into two totally different entities - product data and order data. I doubt this is good design but Its the easiest way I can see of simplifying the system.|||So are the customers browsing the "databases" that the vendors supply to you - you don't import this data into a central products table ?
Why not import the vendor's databases into 1 (use can use dts to help you transform the data into a standard if possible)- rather than waiting on a customer to pick - this would be easier to manage and faster ? Next, use the "true master" as a query only - use the unique key from this and use it to populate the order details table for the customer.|||this would be the ideal however the vendor databases are very different in product information, change frequently (daily/weekly change,additions & deletes) the vendor data also contains "related" product information that isn't directly related to orders that would logically not work in a single product table. My "keys" to pull ou t the related data i need would be barcodes and/or catalogue numbers but these are a) not always unique or b) do not match up (so we lose valuable product information that would generate sales)
My Vendor databases could potentially number 5 - 10 and range from single table 50mb files to 10gb relational structures. Getting it all to work off of a single product table with no dupes and allowing staff to make changes aswell as the daily/weekly updates just spins my head.|||To be honest (and I have very minimal information for this recommendation) I would re-examine the entire process and redesign my structure. The problem you will continue to have is "patching" the process to make everything work based on a design that may have worked in the beginning but is beginning to show it flaws. There are several techniques to handle disjointed data sources that you have while still having a centralized repository of data (usually keys that just point to the actual table/databases that you need to retrieve data). Basically, creating an intermediate table to abstract the complexity of the data sources beneath it, while allowing it to be manageable and maintainable.
There are times that you are much better off punting the existing design - this may have an upfront cost that seems too expensive but realize that in a very short time the maintainability will pay for itself - any customization on both the database and software development side will be minimal. Plus adding additional vendors to this design would be seamless.|||RE: To be honest (and I have very minimal information for this recommendation) I would re-examine the entire process and redesign my structure. The problem you will continue to have is "patching" the process to make everything work based on a design that may have worked in the beginning but is beginning to show it flaws. There are several techniques to handle disjointed data sources that you have while still having a centralized repository of data (usually keys that just point to the actual table/databases that you need to retrieve data). Basically, creating an intermediate table to abstract the complexity of the data sources beneath it, while allowing it to be manageable and maintainable.
There are times that you are much better off punting the existing design - this may have an upfront cost that seems too expensive but realize that in a very short time the maintainability will pay for itself - any customization on both the database and software development side will be minimal. Plus adding additional vendors to this design would be seamless.
RE: [I would re-examine the entire process and redesign my structure.]
S1 I agree, and support the sentiment.
S2 Create a sound logical design that addresses the business needs. This is probably the best advice you can take. Since your situation appears large and complex, you may want to implement a sound end result in two or more intermediate stages that may more easily be budgeted, implemented, and adjusted for 'unforseen' issues over a period of few quarters or longer (rather than use a 'Big Bang' cutover strategy).|||Thanks for the replies definitely very useful comments. What I am considering is to totally seperate my product data from my order data and use code on the website to transfer the neccessary data into my order table so the staff can process the customer orders.
vendor data --> sql server --> website --> sql server --> order tables --> order processing
whereas the current setup is along the lines of
product tables <--> sql server <--> website
^
|
order tables
my data changes so frequently that this is the only simple way I can see of approaching the problem.
If i go along the route of having keys in a centralised database pointing to the source of the information I think this would be too much effort to manage and ensure it is kept in order.
Thanks for your help its great to be able to throw some ideas around as I do not currently have a second technical person to discuss this in detail with!
I am redeveloping a web store which is an ASP based site and am looking to make life easier for myself and other staff at the company I work for as well as our customers. I have some problems with my existing data, it is becoming quite a task to manage and this boils down to receiving product information from Vendors in varying formats and in some cases partial data from two different vendors to make one catalogue.
When the site was originally developed it was designed for one "master" product table. We currently still use this and use catalogue numbers and barcodes as our keys to pulling the various supporting information out of our Vendor databases this works OK but we now have many duplicates but fixing the table is not an option as too much of the internal systems rely on this master table for looking up product information.
I have a plan which I am currently in the middle of conceiving, and hence asking for advice here, to re-code our website to directly browse and search purely on the Vendor databases, this way our customers can view the absolute best up to date catalogues and if we need to we can perform a complete reload of the data if we wish most importantly not affecting the other databases or our internal systems. To keep track of what the customers order I intend to add the important product information - such as price, supplier etc to an orders table. Our staff can then use this information to process the orders regardless of the status of my product data that is web facing. Now strictly speaking I dont think this design is correct, as I will be duplicating quite a bit of data into that already exists in my Vendor databases into my orders table and certain problems will arise that I can forsee already like having to write specific code for the browsing and searching of each Vendor database but I think the overall benefits outweigh the current setup. we use SQL server 2k and have several million rows of existing data that will need importing into the new structure aswell. What do you think of doing this i.e. keeping the product data and order data totally seperate and developing the website around this so essentially the add to shopping cart button on the website is what does the adding of data between the two.
phew long post...any advice appreciated!Are these vendor databases you are referring to - are they part of your system or are they the actual remote databases of your vendors ? And how does the "master" relate to these ?|||They make up the product range that we sell from the website and are supplied by the vendors as databases (normally dbf or csv format - but they will usually supply a schema). I will take these databases and provide facility to browse, search and order the products on the website.
As the rest of our system is designed to access a single table for the products, my code on the website takes the information from the Vendor databases and as the customers browse our site & add products to their cart some background code adds records into the master product table so that we can keep track of what they have in their cart.
This solution is not particularly suitable as it creates a lot of duplicate entries (if two different customers order the same product for example or our staff may delete a record for whatever reason and add a new record.)
what I am thinking is to eliminate the master table, and seperate my data into two totally different entities - product data and order data. I doubt this is good design but Its the easiest way I can see of simplifying the system.|||So are the customers browsing the "databases" that the vendors supply to you - you don't import this data into a central products table ?
Why not import the vendor's databases into 1 (use can use dts to help you transform the data into a standard if possible)- rather than waiting on a customer to pick - this would be easier to manage and faster ? Next, use the "true master" as a query only - use the unique key from this and use it to populate the order details table for the customer.|||this would be the ideal however the vendor databases are very different in product information, change frequently (daily/weekly change,additions & deletes) the vendor data also contains "related" product information that isn't directly related to orders that would logically not work in a single product table. My "keys" to pull ou t the related data i need would be barcodes and/or catalogue numbers but these are a) not always unique or b) do not match up (so we lose valuable product information that would generate sales)
My Vendor databases could potentially number 5 - 10 and range from single table 50mb files to 10gb relational structures. Getting it all to work off of a single product table with no dupes and allowing staff to make changes aswell as the daily/weekly updates just spins my head.|||To be honest (and I have very minimal information for this recommendation) I would re-examine the entire process and redesign my structure. The problem you will continue to have is "patching" the process to make everything work based on a design that may have worked in the beginning but is beginning to show it flaws. There are several techniques to handle disjointed data sources that you have while still having a centralized repository of data (usually keys that just point to the actual table/databases that you need to retrieve data). Basically, creating an intermediate table to abstract the complexity of the data sources beneath it, while allowing it to be manageable and maintainable.
There are times that you are much better off punting the existing design - this may have an upfront cost that seems too expensive but realize that in a very short time the maintainability will pay for itself - any customization on both the database and software development side will be minimal. Plus adding additional vendors to this design would be seamless.|||RE: To be honest (and I have very minimal information for this recommendation) I would re-examine the entire process and redesign my structure. The problem you will continue to have is "patching" the process to make everything work based on a design that may have worked in the beginning but is beginning to show it flaws. There are several techniques to handle disjointed data sources that you have while still having a centralized repository of data (usually keys that just point to the actual table/databases that you need to retrieve data). Basically, creating an intermediate table to abstract the complexity of the data sources beneath it, while allowing it to be manageable and maintainable.
There are times that you are much better off punting the existing design - this may have an upfront cost that seems too expensive but realize that in a very short time the maintainability will pay for itself - any customization on both the database and software development side will be minimal. Plus adding additional vendors to this design would be seamless.
RE: [I would re-examine the entire process and redesign my structure.]
S1 I agree, and support the sentiment.
S2 Create a sound logical design that addresses the business needs. This is probably the best advice you can take. Since your situation appears large and complex, you may want to implement a sound end result in two or more intermediate stages that may more easily be budgeted, implemented, and adjusted for 'unforseen' issues over a period of few quarters or longer (rather than use a 'Big Bang' cutover strategy).|||Thanks for the replies definitely very useful comments. What I am considering is to totally seperate my product data from my order data and use code on the website to transfer the neccessary data into my order table so the staff can process the customer orders.
vendor data --> sql server --> website --> sql server --> order tables --> order processing
whereas the current setup is along the lines of
product tables <--> sql server <--> website
^
|
order tables
my data changes so frequently that this is the only simple way I can see of approaching the problem.
If i go along the route of having keys in a centralised database pointing to the source of the information I think this would be too much effort to manage and ensure it is kept in order.
Thanks for your help its great to be able to throw some ideas around as I do not currently have a second technical person to discuss this in detail with!
Subscribe to:
Posts (Atom)