Showing posts with label back. Show all posts
Showing posts with label back. Show all posts

Tuesday, March 27, 2012

Calculated Member

Hello All.
I am using AS 2000 and would like to create a calculated member that shows a
calculated value back to a particular month regardless of year.
Here is an example.
I am looking at data for the month of July, 2005 and I want my calculated
member to show a calculated value for the month ofo December, 2004.
I would like this to be not dependant on the year. So if I am looking at
data for July, 2004, it will calculate the member for December of 2003. The
month that I am looking for backwards from where I am is always going to be
December of the previous year.
I hope I am being clear about this.
Bill
if you have only year and month levels and if the december month is the last
month of the year this formula will return the last december:
time.currentmember.parent.prevmember.lastchild
you can create a more generic formula (using ancestor function) and there is
other way to do this job (likeusing parrallelperiod, closingperiod...).
"Bill Walo" <williamwalo@.nvgeneral.com> wrote in message
news:A105A356-BA58-4726-806F-FFFBE2E72752@.microsoft.com...
> Hello All.
> I am using AS 2000 and would like to create a calculated member that shows
> a
> calculated value back to a particular month regardless of year.
> Here is an example.
> I am looking at data for the month of July, 2005 and I want my calculated
> member to show a calculated value for the month ofo December, 2004.
> I would like this to be not dependant on the year. So if I am looking at
> data for July, 2004, it will calculate the member for December of 2003.
> The
> month that I am looking for backwards from where I am is always going to
> be
> December of the previous year.
> I hope I am being clear about this.
> Bill
>

Calculated Member

Hello All.
I am using AS 2000 and would like to create a calculated member that shows a
calculated value back to a particular month regardless of year.
Here is an example.
I am looking at data for the month of July, 2005 and I want my calculated
member to show a calculated value for the month ofo December, 2004.
I would like this to be not dependant on the year. So if I am looking at
data for July, 2004, it will calculate the member for December of 2003. The
month that I am looking for backwards from where I am is always going to be
December of the previous year.
I hope I am being clear about this.
Billif you have only year and month levels and if the december month is the last
month of the year this formula will return the last december:
time.currentmember.parent.prevmember.lastchild
you can create a more generic formula (using ancestor function) and there is
other way to do this job (likeusing parrallelperiod, closingperiod...).
"Bill Walo" <williamwalo@.nvgeneral.com> wrote in message
news:A105A356-BA58-4726-806F-FFFBE2E72752@.microsoft.com...
> Hello All.
> I am using AS 2000 and would like to create a calculated member that shows
> a
> calculated value back to a particular month regardless of year.
> Here is an example.
> I am looking at data for the month of July, 2005 and I want my calculated
> member to show a calculated value for the month ofo December, 2004.
> I would like this to be not dependant on the year. So if I am looking at
> data for July, 2004, it will calculate the member for December of 2003.
> The
> month that I am looking for backwards from where I am is always going to
> be
> December of the previous year.
> I hope I am being clear about this.
> Bill
>

Sunday, March 25, 2012

Calculated field question sum of a product rather then the product of sums

Here is my calculated field. I want to sum the “effective duration” * “portfolio weight” but what I get back is the sum of the “effective duration” * sum of “portfolio weight”.

How would I write that code or should I do that calculation when I am importing the data into the fact table.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration]
AS Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Effective Duration]
)
Then Null
When IsEmpty
(
[Measures].[Portfolio Weight]
)
Then Null
Else (
// The Root function returns the (All) value for the target dimension.
[Measures].[Effective Duration] * [Measures].[Portfolio Weight]
)

End,
FORMAT_STRING = "#,#.0000",
VISIBLE = 1 ;

I believe the problem is caused by the fact that calculated measure does not have an aggregation function by itself.

At each level in a user hierarchy, it will have to compute its value based on the values of underlying regular measures you supplied in the expression. Hence, since in most likelihood 'effective duration' and 'portfolio weight' are aggregated using Sum function, this explains the result you are getting.

You may want to specify how to compute the calculated measure for each level of the 'target hierarchy'.

The following example does that using recursive definition for Weighted Duration. It will look at whether currentmember is a leaf in the 'target hierarchy' or not.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration] AS

IIF ( IsLeaf([Target Dim].[Target Hierarchy].CurrentMember),

[Measures].[Effective Duration] * [Measures].[Portfolio Weight]),

Sum(([Target Dim].[Target Hierarchy].CurrentMember.Children, [MEASURES].[Weighted Duration])))

|||

I think this is the right concept but I need to go all the way down to the fact table. Either that or I do it in the FactTable itself. I feel like that's somewhat of a shortcut. If I precalculate everything in the fact table during the data import I feel like I'm cheating a little. I thought olap was made for this sort of thing. Weightings/ratios are rather basic.

The other part about it is pulling out a measure for a specific level in the heirarchy. No matter what dimensions I am using, if I am using DimPortfolio, I want the total of a given measure (market value) for the "All" level of the portfolio. I'm having trouble with that too.

|||

Precalculating is the best solution, given some limitations that calculated members/measures have.

You can submit your wishlist to SQL Server group though, I believe I saw some website dedicated to that, https://connect.microsoft.com

Calculated field question sum of a product rather then the product of sums

Here is my calculated field. I want to sum the “effective duration” * “portfolio weight” but what I get back is the sum of the “effective duration” * sum of “portfolio weight”.

How would I write that code or should I do that calculation when I am importing the data into the fact table.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration]
AS Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Effective Duration]
)
Then Null
When IsEmpty
(
[Measures].[Portfolio Weight]
)
Then Null
Else (
// The Root function returns the (All) value for the target dimension.
[Measures].[Effective Duration] * [Measures].[Portfolio Weight]
)

End,
FORMAT_STRING = "#,#.0000",
VISIBLE = 1 ;

I believe the problem is caused by the fact that calculated measure does not have an aggregation function by itself.

At each level in a user hierarchy, it will have to compute its value based on the values of underlying regular measures you supplied in the expression. Hence, since in most likelihood 'effective duration' and 'portfolio weight' are aggregated using Sum function, this explains the result you are getting.

You may want to specify how to compute the calculated measure for each level of the 'target hierarchy'.

The following example does that using recursive definition for Weighted Duration. It will look at whether currentmember is a leaf in the 'target hierarchy' or not.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration] AS

IIF ( IsLeaf([Target Dim].[Target Hierarchy].CurrentMember),

[Measures].[Effective Duration] * [Measures].[Portfolio Weight]),

Sum(([Target Dim].[Target Hierarchy].CurrentMember.Children, [MEASURES].[Weighted Duration])))

|||

I think this is the right concept but I need to go all the way down to the fact table. Either that or I do it in the FactTable itself. I feel like that's somewhat of a shortcut. If I precalculate everything in the fact table during the data import I feel like I'm cheating a little. I thought olap was made for this sort of thing. Weightings/ratios are rather basic.

The other part about it is pulling out a measure for a specific level in the heirarchy. No matter what dimensions I am using, if I am using DimPortfolio, I want the total of a given measure (market value) for the "All" level of the portfolio. I'm having trouble with that too.

|||

Precalculating is the best solution, given some limitations that calculated members/measures have.

You can submit your wishlist to SQL Server group though, I believe I saw some website dedicated to that, https://connect.microsoft.com

Wednesday, March 7, 2012

Cached data being displayed

If I run a report, then change some data in the database. Then, navigate
away from my sql report, go back back in and re-run the report, it doesn't
show the data that I just changed in the database. I have to actually click
the 'refresh' button to get the report actually "re-run". It appears to
display cached data.
How can I tell the report to not do this?I had the same issue and found the solution was to add another rs command to
the report url...
i.e. rs:ClearSession=true
e.g.
http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
"Brian Patrick" wrote:
> If I run a report, then change some data in the database. Then, navigate
> away from my sql report, go back back in and re-run the report, it doesn't
> show the data that I just changed in the database. I have to actually click
> the 'refresh' button to get the report actually "re-run". It appears to
> display cached data.
> How can I tell the report to not do this?
>
>|||I too had the same issue. I did tried the same solution as you have given here.
I found that even a true or false has the same effect ie.. clearing the
cached data for the both the values. Only we need to specify any one of them.
And in case we don't specify rs:ClearSession='' cached data is not cleared.
Did u experiance that?
Suresh
"NH" wrote:
> I had the same issue and found the solution was to add another rs command to
> the report url...
> i.e. rs:ClearSession=true
> e.g.
> http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> "Brian Patrick" wrote:
> > If I run a report, then change some data in the database. Then, navigate
> > away from my sql report, go back back in and re-run the report, it doesn't
> > show the data that I just changed in the database. I have to actually click
> > the 'refresh' button to get the report actually "re-run". It appears to
> > display cached data.
> >
> > How can I tell the report to not do this?
> >
> >
> >|||Well I have not tested this. It sounds like odd behaviour.
"Suresh" wrote:
> I too had the same issue. I did tried the same solution as you have given here.
> I found that even a true or false has the same effect ie.. clearing the
> cached data for the both the values. Only we need to specify any one of them.
> And in case we don't specify rs:ClearSession='' cached data is not cleared.
> Did u experiance that?
> Suresh
>
> "NH" wrote:
> > I had the same issue and found the solution was to add another rs command to
> > the report url...
> >
> > i.e. rs:ClearSession=true
> >
> > e.g.
> > http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> >
> > "Brian Patrick" wrote:
> >
> > > If I run a report, then change some data in the database. Then, navigate
> > > away from my sql report, go back back in and re-run the report, it doesn't
> > > show the data that I just changed in the database. I have to actually click
> > > the 'refresh' button to get the report actually "re-run". It appears to
> > > display cached data.
> > >
> > > How can I tell the report to not do this?
> > >
> > >
> > >|||We had the same issue. Impmented rs:ClearSession=true and that worked ome
time ago. Now it no longer worls on a RS sp 1 Windows Server 2003 with a
APS.NET app accessing reports with url. This is causing major issues with
client and the on-line business application not reporting correctly as data
enry occur. Surley Microsoft has some solution?
"NH" wrote:
> Well I have not tested this. It sounds like odd behaviour.
> "Suresh" wrote:
> > I too had the same issue. I did tried the same solution as you have given here.
> > I found that even a true or false has the same effect ie.. clearing the
> > cached data for the both the values. Only we need to specify any one of them.
> >
> > And in case we don't specify rs:ClearSession='' cached data is not cleared.
> > Did u experiance that?
> >
> > Suresh
> >
> >
> > "NH" wrote:
> >
> > > I had the same issue and found the solution was to add another rs command to
> > > the report url...
> > >
> > > i.e. rs:ClearSession=true
> > >
> > > e.g.
> > > http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> > >
> > > "Brian Patrick" wrote:
> > >
> > > > If I run a report, then change some data in the database. Then, navigate
> > > > away from my sql report, go back back in and re-run the report, it doesn't
> > > > show the data that I just changed in the database. I have to actually click
> > > > the 'refresh' button to get the report actually "re-run". It appears to
> > > > display cached data.
> > > >
> > > > How can I tell the report to not do this?
> > > >
> > > >
> > > >

Saturday, February 25, 2012

C2 auditing ?

Hello there
I just inherited a data farm and I noticed that the person before me had c2
auditing turned on.
I noticed that the trc files date only back to 04/05 and that the last time
a fiel has been modified is yesterday.
the rate of growing out of 200mb should make these files date of once a day
or even 2. does anybody know how I can check if c2 is still turned on ? or
any idea why that is ? I suppoe i can wait another couple of days and see
but ...
thanks
>> does anybody know how I can check if c2 is still turned on ?
You can use sp_configure look for the running value for c2 audit mode.
Anith

C2 auditing ?

Hello there
I just inherited a data farm and I noticed that the person before me had c2
auditing turned on.
I noticed that the trc files date only back to 04/05 and that the last time
a fiel has been modified is yesterday.
the rate of growing out of 200mb should make these files date of once a day
or even 2. does anybody know how I can check if c2 is still turned on ? or
any idea why that is ? I suppoe i can wait another couple of days and see
but ...
thanks>> does anybody know how I can check if c2 is still turned on ?
You can use sp_configure look for the running value for c2 audit mode.
Anith

c0000005 EXCEPTION_ACCESS_VIOLATION

We have SQL Server 2000 with SP3 installed on Windows 2000 Advance Server. Server has 8 GB RAM and 4 CPU. Few days back we have installed multiple named instance on this server .Now we have assign each CPU to individual instance and min server memory to 1024 and max server memory is kept open.

After this we are facing an issue with default instance for exception_access_violation as below ...
Memory
MemoryLoad = 83%
Total Physical = 7679 MB
Available Physical = 1252 MB
Total Page File = 15480 MB
Available Page File = 7756 MB
Total Virtual = 2047 MB
Available Virtual = 344 MB

*Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDu
mp0022.txt
* ************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 07/24/07 00:46:02 spid 16
*
* Exception Address = 008405D8 (DeadlockMonitor::ResolveDeadlock + 00000025 L
ine 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
* ntdll 77F80000 77FFCFFF 0007d000
* KERNEL32 7C570000 7C627FFF 000b8000
* ADVAPI32 7C2D0000 7C331FFF 00062000
* RPCRT4 77D30000 77D9EFFF 0006f000
* USER32 77E10000 77E74FFF 00065000
* GDI32 77F40000 77F7DFFF 0003e000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 78000000 78044FFF 00045000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 780A0000 780B1FFF 00012000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 028D0000 0291FFFF 00050000
* Secur32 02920000 0292EFFF 0000f000
* NTDSAPI 02930000 02940FFF 00011000
* DNSAPI 02950000 02973FFF 00024000
* WSOCK32 02980000 02987FFF 00008000
* WS2_32 02990000 029A3FFF 00014000
* WS2HELP 029B0000 029B7FFF 00008000
* WLDAP32 029C0000 029E9FFF 0002a000
* NETRAP 029F0000 029F5FFF 00006000
* SAMLIB 02A00000 02A0EFFF 0000f000
* ole32 02D70000 02E5EFFF 000ef000
* XOLEHLP 02E60000 02E67FFF 00008000
* MSDTCPRX 02E70000 02F2AFFF 000bb000
* MTXCLU 02F30000 02F3FFFF 00010000
* VERSION 02F40000 02F46FFF 00007000
* LZ32 02F50000 02F55FFF 00006000
* CLUSAPI 02F60000 02F6FFFF 00010000
* RESUTILS 02F70000 02F7CFFF 0000d000
* USERENV 02F80000 02FE0FFF 00061000
* rnr20 02FF0000 02FFBFFF 0000c000
* iphlpapi 03040000 03052FFF 00013000
* ICMP 03060000 03064FFF 00005000
* MPRAPI 03070000 03086FFF 00017000
* OLEAUT32 03090000 0312AFFF 0009b000
* ACTIVEDS 03130000 0315EFFF 0002f000
* ADSLDPC 03160000 03182FFF 00023000
* RTUTILS 03190000 0319DFFF 0000e000
* SETUPAPI 031A0000 0322DFFF 0008e000
* RASAPI32 03230000 03262FFF 00033000
* RASMAN 03270000 03280FFF 00011000
* TAPI32 03290000 032B1FFF 00022000
* COMCTL32 032C0000 03343FFF 00084000
* SHLWAPI 03350000 033B5FFF 00066000
* DHCPCSVC 033C0000 033D8FFF 00019000
* winrnr 03880000 03887FFF 00008000
* rasadhlp 03890000 03894FFF 00005000
* SSNETLIB 04040000 04054FFF 00015000
* SSNMPN70 04090000 04095FFF 00006000
* security 043B0000 043B3FFF 00004000
* msafd 04850000 0486DFFF 0001e000
* wshtcpip 048B0000 048B6FFF 00007000
* SSmsLPCn 04940000 04946FFF 00007000
* mswsock 049D0000 049E1FFF 00012000
* msv1_0 04A30000 04A50FFF 00021000
* CRYPT32 04A60000 04AE6FFF 00087000
* MSASN1 04B10000 04B1FFFF 00010000
* rsabase 04F40000 04F62FFF 00023000
* CLBCATQ 05280000 0530EFFF 0008f000
* SQLOLEDB 05310000 05382FFF 00073000
* MSDART 05390000 053AEFFF 0001f000
* comdlg32 053B0000 053EDFFF 0003e000
* SHELL32 053F0000 05635FFF 00246000
* MSDATL3 05640000 05654FFF 00015000
* oledb32 05CE0000 05D45FFF 00066000
* OLEDB32R 05D50000 05D5FFFF 00010000
* xpstar 05DE0000 05E26FFF 00047000
* SQLRESLD 05E30000 05E36FFF 00007000
* SQLSVC 05E40000 05E56FFF 00017000
* ODBC32 05E60000 05E91FFF 00032000
* odbcbcp 05EA0000 05EA5FFF 00006000
* W95SCM 05EB0000 05EBBFFF 0000c000
* SQLUNIRL 10000000 1002CFFF 0002d000
* WINSPOOL 05EC0000 05EDDFFF 0001e000
* MPR 05EE0000 05EEFFFF 00010000
* SHFOLDER 05EF0000 05EF7FFF 00008000
* odbcint 061C0000 061D5FFF 00016000
* NDDEAPI 061E0000 061E6FFF 00007000
* SQLSVC 061F0000 061F5FFF 00006000
* xpstar 06200000 06208FFF 00009000
* xplog70 06AA0000 06AB1FFF 00012000
* xplog70 06AC0000 06AC3FFF 00004000
* xpsqlbot 06AD0000 06AD5FFF 00006000
* SQLFTQRY 09200000 09231FFF 00032000
* impprov 094E0000 094F8FFF 00019000
* DBNETLIB 09A80000 09A8EFFF 0000f000
* SQLOLEDB 09BD0000 09BDEFFF 0000f000
* sqlvdi 09C20000 09C2BFFF 0000c000
* dbghelp 07BE0000 07C94FFF 000b5000
*

Any idea ?Can you install SP4? this KB seems to indicate this is fixed in SP4: http://support.microsoft.com/kb/821741/en-us?spid=2852&sid=1070

What's in the SQL Server log file just before the dump?|||Is there any workaround other than upgrading to SP4. as SQL Server crashing daily twise and SP4 upgrade testing will take atleast a week time.|||I don't have a workaround for you, sorry. Maybe PSS could suggest one if you call them.|||As suggested only MS PSS can give more idea why the stack dump is created. It is better to proceed that way to resolve this issue and also SP3 for SQL2000 is no more supported, you have to test and upgrade to SP4 for SQL.

Tuesday, February 14, 2012

business vs. logic

You have seen a chair in a public room and go back after a while to acquire it. Is it unexpected that the object is not there anymore?

I implement the business logic in stored procedures. Once user orders some service, the actions carry out are: a)check they have sufficient funds on their balance; and, b) a price check is made selecting the latest from pricelist. The newly created job entry refers the price in the pricelist table according to service_id and the issued pricelist date.

These are data validity checks, which must be done in a multiuser database. In the session beginning, user gets prices and funds. These data are not locked throughout the session -- another process may change the records. Therefore, the extra "business" checks are needed to preserve data correctness at the start of every transaction. One way to report the errors would be to raise an error.

Consider the primary-foreign key violations. They are raised by system with severity level of 16. Uniqueness violation is raised with severity 14. The MSSQL reference http://msdn2.microsoft.com/en-us/library/ms164086.aspx tells us that this is a sort of error, which can be corrected by user. Indeed, (s)he can. But the exceptions are unexpected situations. Exception processing consumes huge CPU processing power if some occurs. Normally, programs allocate resources and use them. If resource cannot be allocated -- app exits with exception. It just has nothing to do. However, there is nothing exceptional in shared data access by multiple concurrent processes in a multithread/multiuser/distributed application. Since, we cannot lock the user resources during user session, we should be prepared to concurrent data changes. Therefore, I find the exception raising inappropriate tool.

Consider a situation, when one user is assigned to a group. Nothing prevents from another admin to remove the group at the same time. Therefore, our user management application should be ready to expected "group does not exist" "error" result. I even disagree that this is an error, since it is perfectly valid to have such a situation. It is like you have seen a chair in a room and later return to take it. But the chair is not there anymore. Is it unexpected? The case should not be exceptional, IMO.

So we have:
1. Severity 1-10 "informational messages";
2. Severity 11-16 errors; and
3. RETURN error_code.

Which should be used? Anything else? Do my considerations make sense?

When I want the SQL Code to with certainly 'talk' back to the application code, I use RAISERROR.

For SQL Procedures to 'talk' with each other, I may also use the RETURN value.

I may use 'WITH LOG' to force the error condition into the Application Log.

And you are wise to question what is an error condition. Is it a 'system' error, a 'logic' error, or a 'business rule' exception?

|||Thank you, Arnie for sharing your experience.


> And you are wise to question what is an error condition. Is it a 'system'
> error, a 'logic' error, or a 'business rule' exception?

I believe that the "business logic" is an oxymoron likewise "market order", which is chaos and disorder in fact. Nevertheless, I do not distinguish between "rules" and "logic" here. It is logical that user balance should not go below zero, it is logical that the price must be the latest one, it is logical that user must belong to an existing group.

I understand the exceptions in a serial programing where your threads allocate (lock) resources but not in distributed programing where you cannot be sure about current condition of resources. It is ok for the local copies of the data to be incoherent in multiprocessor system. When data become incoherent, they may cause logic/rules/constraint/etc violations.

Some recommend checking file existence before opening it. But another process may remove the file between the check and open op. System returns "file does not exist" open error. It does not investigate whether the thread checked the file existence or it is making a reckless open. In other words, it does not examine the intentions and conditions of the caller. One utility would, for instance, set up admin rights for a list of users. This 'setadmin' app assumes the group 'admins' exists. It has nothing to do if there is no such group. It is exceptional situation for the app. Another application requests available groups, uses one and sends result back. Multiuser application should be prepared to situation where the used group does not exist anymore. A more realistic is a stock with multiple operators. When you have selected the list of available goods into a local copy, the goods can be removed by another operator.
You should tell the user that the product list is outdated, that it is incoherent with reality rather than complaining that the operation cannot be accomplished.

Looks like, error handling in distributed systems is more general than a DB errors issue. But any advices are welcomed.
|||

Valentin,

Note that I didn't use the term 'business logic', but instead used 'business rule exception' -and yet I agree with you that often business needs sometimes seem to defy logic; and that when describing the condition of a market, 'market order' could easily be a oxymoron, but when used as a entity, 'market order' has a definitive 'thing' quality about it.

This, and your other posts on Locking behavior demonstrate a deep concern for data integrity, and I can certainly appreciate your questions. When there are opportunities for instability, instability will occur. Stasis can only exist when ignoring temporality. Yet, in fact, Stasis cannot be separated from temporal considerations. The certainity of a state of data is increasingly uncertain as the system becomes more complex -distributed, multiprocessor, and multi-threaded. We often resort to extreme control when faced with apparant chaos. As TS Elliot would describe it, the time 'between' is the shadow, and we cannot ignore the shadow, for all things unknown exists in the shadow.

I once worked on a project where, for speed, data was cached on each web server in a very large web farm, and yet many data items were unique and ONLY one customer would be allowed to purchase that one item. There could be thousands of concurrent customers considering the same item. It was necessary to create a lot of communication between the cach objects, tenative 'sold' indicators, and definitive 'sold' indicators, 'cancellation', etc. You can imagine the chaos -but it did have a solution.

Since, as you put it so well, there are opportunities for chaos between the moment we determine IF an action can be taken AND the moment the action is actually taken, AND the 'system' doesn't help 'protect' us from that potential chaos, we are faced with having to find ways to invoke 'extreme control'. So like forcing all passengers through the security checkpoint as a single point of control, it may be useful to consider how to force all sensitive data activity through a 'single point of control'.

One useful approach is to force all data changes through Stored Procedures (No direct table access). Then in the Stored Procedure used to UPDATE a set of resouces in a TRANSACTION, also use sp_getapplock / sp_releaseapplock as a way to more tightly control access to the resources.

Here is an example: (Use Northwind database)

Code Snippet


CREATE PROCEDURE dbo.Employees_U_LastName
( @.EmployeeID int,
@.LastName varchar(20)
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION


DECLARE @.LockResult int

EXECUTE @.LockResult = sp_getapplock
@.Resource = 'ThisIsMyProcess-StayOut',
@.LockMode = 'Exclusive',
@.LockTimeout = 0

IF @.LockResult <> 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'SomeOne Else is Using this Resource'
RETURN
END


PRINT 'TRANSACTION IS Active'
SELECT
EmployeeID,
LastName,
FirstName
FROM Employees
WHERE EmployeeID = @.EmployeeID


PRINT 'Waiting...'
WAITFOR DELAY '00:00:15'

PRINT 'Second UPDATE to Active TRANSACTION'
UPDATE Employees
SET LastName = @.LastName
WHERE EmployeeID = @.EmployeeID


EXECUTE sp_releaseapplock
@.Resource = 'ThisIsMyProcess-StayOut'


COMMIT TRANSACTION


PRINT 'Waiting for Other Process to Complete'
WAITFOR DELAY '00:00:05'

PRINT 'CHECK Results of TRANSACTION'
SELECT
EmployeeID,
LastName,
FirstName
FROM Employees
WHERE EmployeeID = @.EmployeeID


END
GO

Then execute this from two different connections.

Code Snippet


EXECUTE dbo.Employees_U_LastName
@.EmployeeID = 1,
@.LastName = 'Davolio-Jones'

In the second connection, you will get the message: 'SomeOne Else is Using this Resource'. But you have to be adamant that there is only one 'egress' point to the resources -through the procedure.

Perhaps this will help you along on your quest to find a secure and reliable method to control data

|||

Sometimes maybe it doesn't matter if the 'problem' is a 'system' error, a 'logic' error, or a 'business rule' error -the 'problem' has to be handled, and in the case at hand, they may all be handled the same. It is still very useful to keep clarity about the differences between them. For at times, the effect of treating them the 'same' is confusing at best.

valentin tihomirov wrote:

I do not distinguish between "rules" and "logic" here. It is logical that user balance should not go below zero, it is logical that the price must be the latest one, it is logical that user must belong to an existing group.

Logic follows defined mathematical sylogisms.

1 = 2

2 = 3

Therefore 3 = 1

is based on logic. That balance MUST be >= 0 is a Business decision (busines rule) solely BECAUSE [ bal < 0 ] is mathematically valid -but in this situation, this business has determined to not allow that to occur. A bank 'should' not allow an account to overdraw (balance >= 0 ) at all times, in other words, as the trustee for your money, the bank 'should' not give others money claiming that it is your money if you don't have money to give out. However, the banking industry decided that there was money to be earned by allowing overdrafts, and you can't charge for an overdraft UNLESS the balance is allowed to be less than zero, so the business rule changed, and balance can be less than zero ( to some limit ).

Logic is easy to code, business rules, subject to change, require a lot more effort and consideration.

Of course, I realize that you know what I'm talking about. I'm using your posting to help others that may be reading, and still learning, and still uncertain... (So I hope you accept the 'lecture' in that guise. I often respond to a posting with the realization that I may also be responding to other readers with similar -but not exactly the same, questions.)

|||

For clarification, in the demonstration procedure I posted earlier, I used a PRINT statement when checking if @.LockResult. Normally, I would raise an error so that the calling application (or procedure) can capture the error number and react accordingly.

First, create the Error in the database, (Use whatever Error Number ( > 50000 ) and message you wish.)

Code Snippet

EXECUTE sp_addmessage
@.msgnum = 51001,
@.severity = 16,
@.msgtext = N'Resource NOT Available',
@.lang = 'us_english',
@.replace = REPLACE

Then in the procedure:

Code Snippet

IF @.LockResult <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ( 51001, 16, 1 )
RETURN
END

Now the application (or calling procedure) can handle ErrorNumber 51001 as deemed necessary. Wait a few seconds and Re-Submit perhaps...

|||

Arnie Rowland wrote:

One useful approach is to force all data changes through Stored Procedures (No direct table access).

SPs are really how I'm trying to do it. Thanks for hinting about sp_getapplock. It looks like a 'lock' object (mutex, critical section) in conventional programming language. However, I think it is too coarse-grain. All the users who wish to change their last names will lock each other. In addition, this lock shell be taken by parties who affect the last names in different way (removing the record, for instance). And you get the whole database locked/synchronized by one big lock. The granularity of automatic transactional locks are one record. However, using full db object names like db_name.table_1.row_id.field_x we can outperform the locking granularity down to field finesse. These are my first ideas about the procedure. Thanks for the hint.

Arnie Rowland wrote:

Logic follows defined mathematical sylogisms.

I was learnt it is the math, which is a science of proving, which is based on logic But I think I can understand the difference between logic and rules now. Thank you for the explanation.

Arnie Rowland wrote:

Of course, I realize that you know what I'm talking about. I'm using your posting to help others that may be reading, and still learning, and still uncertain... (So I hope you accept the 'lecture' in that guise. I often respond to a posting with the realization that I may also be responding to other readers with similar -but not exactly the same, questions.)


That is ok, I am a communist. The sharing is a means of saving resources and a condition of intelligent life survival on the planet Earth.

|||

You're right, using sp_getapplock is kind of 'course-grained'; sp_getapplock creates a 'lock' object, using the name you provide. The only thing 'locked' is the entry to the sproc code. The included (and required) TRANSACTION locks the underlying data. However, blocking others from using the sproc code in practice creates a single 'gateway' through which all controlled activity must pass, effectively forcing serialization of the controlled activity. I would want to make the process as streamlined and efficient as possible in order to cause the minimal amount of queue stacking.

In a very high performance / high utilitzation system, using sp_getapplock just may prove to be to much of a 'bottleneck'. But, if one was concerned about reading into an active TRANSACTION, and the effects on other activities as a result of being able to read into an active TRANSACTION, sp_getapplock is one way to reduce the 'paranoia'. It may be too 'heavy-handed' for most use.

...deleted...

Keep up the good questions.