Clear Cache via Trigger?

I have a dimension table that gets updated nightly. The dimension table is used by a ROLAP cube.

If you wanted to Clear Cache through the use of a trigger once the dimension table is updated, how would you do it? Is there an easy way to execute the XMLA ClearCache from within T-SQL?

If you have a way to call external process from your procedure, you can use ascmd utility to send any XMLA command to Analysis Server. (http://msdn2.microsoft.com/en-us/ms365187.aspx)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

CHECKSUM , produces same hash for two different inputs. is this right?

Hi,

We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.

We are using following type of inside the trigger.

UPDATE [dbo].[Hospital]

SET

[HospitalID]= I.[HospitalID],

[Name]= I.[Name],

[HospitalNumber]= I.[HospitalNumber],

[ServerName] = I.[ServerName],

[IsAuthorized]= I.[IsAuthorized],

[IsAlertEnabled]= I.[IsAlertEnabled],

[AlertStartDate]= I.[AlertStartDate],

[AlertEndDate]= I.[AlertEndDate],

[IsTraining]= I.[IsTraining],

[TestMessageInterval]= I.[TestMessageInterval],

[DelayAlertTime]= I.[DelayAlertTime],

[IsDelayMessageAlert]= I.[IsDelayMessageAlert],

[IsTestMessageAlert]= I.[IsTestMessageAlert],

[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],

[IsWANDownAlert]= I.[IsWANDownAlert],

[IsWANUpAlert]= I.[IsWANUpAlert],

[CreateUserID]= Hospital.[CreateUserID],

[CreateWorkstationID]= Hospital.[CreateWorkstationID],

[CreateDate]= Hospital.[CreateDate] ,

/* record created date is never updated */

[ChangeUserID]= suser_name(),

[ChangeWorkstationID]= host_name(),

[ChangeDate]= getdate() ,

/* Updating the record modified field to now */

[CTSServerID]= I.[CTSServerID]

FROM inserted i

WHERE

i.[HospitalID]= Hospital.[HospitalID]

AND binary_checksum(

Hospital.[HospitalID],

Hospital.[Name],

Hospital.[HospitalNumber],

Hospital.[ServerName],

Hospital.[IsAuthorized],

Hospital.[IsAlertEnabled],

Hospital.[AlertStartDate],

Hospital.[AlertEndDate],

Hospital.[IsTraining],

Hospital.[TestMessageInterval],

Hospital.[DelayAlertTime],

Hospital.[IsDelayMessageAlert],

Hospital.[IsTestMessageAlert],

Hospital.[IsUnAuthorizedMessageAlert],

Hospital.[IsWANDownAlert],

Hospital.[IsWANUpAlert]) !=

binary_checksum(

I.[HospitalID],

I.[Name],

I.[HospitalNumber],

I.[ServerName],

I.[IsAuthorized],

I.[IsAlertEnabled],

I.[AlertStartDate],

I.[AlertEndDate],

I.[IsTraining],

I.[TestMessageInterval],

I.[DelayAlertTime],

I.[IsDelayMessageAlert],

I.[IsTestMessageAlert],

I.[IsUnAuthorizedMessageAlert],

I.[IsWANDownAlert],

I.[IsWANUpAlert]) ;

Here is the checksum example which produces same results for two different input.

DECLARE @.V1 VARCHAR(10)

DECLARE @.V2 VARCHAR(10)

SELECT @.V1 = NULL, @.V2=NULL

SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@.V1,@.V2,0,30,180,1,0,1,1,1),

binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@.V1,@.V2,0,30,180,1,1,1,1,1)

Lookat the two binary_checksum above, they are different and should not match, but they both return same value.

Can someone please provide some info on these.

Did any one looked at this? I guess this is a very very critical. The checksum is used by storage engine to verify the page integrity and the checksum is stored in every page. if it is producing the same hash for two different inputs it may not verify page correctly.

|||CHECKSUMS ARE NOT UNIQUE. You cannot use checksums in the way you are trying to use them.

Check out BOL under BINARY_CHECKSUM

BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long the row is not subsequently modified. BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.

and CHECKSUM

If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

Yes, you are correct, there is a small insignificant chance the checksum may be the same on a page even though the data has changed. In large binary data, like SQL server's 64k pages, this chance is in the range of 100 million to 1.

|||

Tom,

In these case the checksum is consistently produces same hash for two different inputs. So every time when I was updating the two columns specified above, it failed. So I thought there is a bug in the code which is not detecting the changes in the input.

Anyway, your suggestion to use HashBytes is very helpful.

Thanks,

CHECKSUM , produces same hash for two different inputs. is this right?

Hi,

We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.

We are using following type of inside the trigger.

UPDATE [dbo].[Hospital]

SET

[HospitalID]= I.[HospitalID],

[Name]= I.[Name],

[HospitalNumber]= I.[HospitalNumber],

[ServerName] = I.[ServerName],

[IsAuthorized]= I.[IsAuthorized],

[IsAlertEnabled]= I.[IsAlertEnabled],

[AlertStartDate]= I.[AlertStartDate],

[AlertEndDate]= I.[AlertEndDate],

[IsTraining]= I.[IsTraining],

[TestMessageInterval]= I.[TestMessageInterval],

[DelayAlertTime]= I.[DelayAlertTime],

[IsDelayMessageAlert]= I.[IsDelayMessageAlert],

[IsTestMessageAlert]= I.[IsTestMessageAlert],

[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],

[IsWANDownAlert]= I.[IsWANDownAlert],

[IsWANUpAlert]= I.[IsWANUpAlert],

[CreateUserID]= Hospital.[CreateUserID],

[CreateWorkstationID]= Hospital.[CreateWorkstationID],

[CreateDate]= Hospital.[CreateDate] ,

/* record created date is never updated */

[ChangeUserID]= suser_name(),

[ChangeWorkstationID]= host_name(),

[ChangeDate]= getdate() ,

/* Updating the record modified field to now */

[CTSServerID]= I.[CTSServerID]

FROM inserted i

WHERE

i.[HospitalID]= Hospital.[HospitalID]

AND binary_checksum(

Hospital.[HospitalID],

Hospital.[Name],

Hospital.[HospitalNumber],

Hospital.[ServerName],

Hospital.[IsAuthorized],

Hospital.[IsAlertEnabled],

Hospital.[AlertStartDate],

Hospital.[AlertEndDate],

Hospital.[IsTraining],

Hospital.[TestMessageInterval],

Hospital.[DelayAlertTime],

Hospital.[IsDelayMessageAlert],

Hospital.[IsTestMessageAlert],

Hospital.[IsUnAuthorizedMessageAlert],

Hospital.[IsWANDownAlert],

Hospital.[IsWANUpAlert]) !=

binary_checksum(

I.[HospitalID],

I.[Name],

I.[HospitalNumber],

I.[ServerName],

I.[IsAuthorized],

I.[IsAlertEnabled],

I.[AlertStartDate],

I.[AlertEndDate],

I.[IsTraining],

I.[TestMessageInterval],

I.[DelayAlertTime],

I.[IsDelayMessageAlert],

I.[IsTestMessageAlert],

I.[IsUnAuthorizedMessageAlert],

I.[IsWANDownAlert],

I.[IsWANUpAlert]) ;

Here is the checksum example which produces same results for two different input.

DECLARE @.V1 VARCHAR(10)

DECLARE @.V2 VARCHAR(10)

SELECT @.V1 = NULL, @.V2=NULL

SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@.V1,@.V2,0,30,180,1,0,1,1,1),

binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@.V1,@.V2,0,30,180,1,1,1,1,1)

Lookat the two binary_checksum above, they are different and should not match, but they both return same value.

Can someone please provide some info on these.

Did any one looked at this? I guess this is a very very critical. The checksum is used by storage engine to verify the page integrity and the checksum is stored in every page. if it is producing the same hash for two different inputs it may not verify page correctly.

|||CHECKSUMS ARE NOT UNIQUE. You cannot use checksums in the way you are trying to use them.

Check out BOL under BINARY_CHECKSUM

BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long the row is not subsequently modified. BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.

and CHECKSUM

If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

Yes, you are correct, there is a small insignificant chance the checksum may be the same on a page even though the data has changed. In large binary data, like SQL server's 64k pages, this chance is in the range of 100 million to 1.

|||

Tom,

In these case the checksum is consistently produces same hash for two different inputs. So every time when I was updating the two columns specified above, it failed. So I thought there is a bug in the code which is not detecting the changes in the input.

Anyway, your suggestion to use HashBytes is very helpful.

Thanks,

Checking User & Expire of Update Possibility (by Trigger), How to?

Hi,

I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:

RateID, Service, Rate, DateTime, User

Want I want is a code (trigger) in the database can do the following:

If user perform an Update request the code will check:

- if this recored inserted by the same user update command will be execute.

- if this recored inserted by other user: update command will not execute and return message.

- if more than 5 minutes passed the update command will not be execute and return message.

Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.

Change the update statement from something like:

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId

to something like

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())

|||

Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?

and what if I want to put this code in Trigger?

thnx again,,,

|||Any help?|||

Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.

The code idea below relies upon [User] being captured with the system_user system function (domain/username).

Code Snippet

CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN

DECLARE @.User varchar(50)

IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END

GO

|||

Hi,

I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:

CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN

IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)

END

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

Checking User & Expire of Update Possibility (by Trigger), How to?

Hi,

I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:

RateID, Service, Rate, DateTime, User

Want I want is a code (trigger) in the database can do the following:

If user perform an Update request the code will check:

- if this recored inserted by the same user update command will be execute.

- if this recored inserted by other user: update command will not execute and return message.

- if more than 5 minutes passed the update command will not be execute and return message.

Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.

Change the update statement from something like:

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId

to something like

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())

|||

Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?

and what if I want to put this code in Trigger?

thnx again,,,

|||Any help?|||

Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.

The code idea below relies upon [User] being captured with the system_user system function (domain/username).

Code Snippet

CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN

DECLARE @.User varchar(50)

IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END

GO

|||

Hi,

I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:

CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN

IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)

END

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de