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 Transformation

An updated version of the Checksum Transformation has finally made it out into the big wide world.

Checksum Transformation
(http://www.sqlis.com/default.aspx?21)Any details on how "unique" a checksum value is?

The inbuilt SQL Server CHECKSUM() function seems to have a pretty horrible collision rate due to being only a INT. It's not very useful in determining unique rows amongst anything more than a small handful of rows.|||I have no real numbers on how unique it is, only my testing, which seemed rather good. It uses the .Net Hash function (C#), and the internals of that are not documented.

Feel free to try it.

Using an Int makes it easier to combine multiple columns, where as most other methods would produce a very large value, or just not be capable of handling multiple values.

Darren

Checking is updation successfull

I am updating a table in store procedure. I want to check whether the statement has successfully updated the table or not. i know in mySql i can handle it using ROW_COUNT() function. but how can i do it in MS-SQL?You can use @.@.ROWCOUNT for getting number of rows affected by the last statement.|||Hey, thanks man... you saved my time on Google :)

checking for unique constraints during a batch update

hello .
i have a grid for a table that gets updated with recordset.updatebatch
for a multi-user application.

the problem is that some of the table fields have to be unique

now imagine the next situation.

user A opens the form
user B opens the form
user A writes '1000' in the unique field
user A saves the recordset with updatebatch

user B writes '1000' in the unique field
user B saves the recordset with updatebatch

now there will be two records with the field '1000' !

how can i avoid this ?
i cannot check for unique during the update event of the grid
because it should check during the time it is saving and not
when it is just entering data without having updated the recordset

thanks !It is not a good idea to ask users input data for unique field in multi-user application. Use identity, triggers, etc.|||There is another solution, I just wanted to share with you a control featured on download.com that I have been using that has made my life easier, and it solves exactly the problem you are describing. When your user presses the update button on this control it automatically checks for conflicts and lets the user decide what to do. You can try it for free here:

http://download.com.com/3000-2404-10257755.html?tag=lst-0-1

Basically, after selecting the CursorType, CursorLocation, LockType and Disconnected status of your Recordset (there are defaults). I bind my controls and that is it All the complexities of ADO are hidden from the user which is you.

Then I can Navigate, Add New, Delete, Refresh, Search and Save Changes to my recordsets without any other code. That is really it, no other code is needed.

This works in any environment VB, Access, VC++ etc...

Let me know what you think, and if it works out for you.

Originally posted by alexandros
hello .
i have a grid for a table that gets updated with recordset.updatebatch
for a multi-user application.

the problem is that some of the table fields have to be unique

now imagine the next situation.

user A opens the form
user B opens the form
user A writes '1000' in the unique field
user A saves the recordset with updatebatch

user B writes '1000' in the unique field
user B saves the recordset with updatebatch

now there will be two records with the field '1000' !

how can i avoid this ?
i cannot check for unique during the update event of the grid
because it should check during the time it is saving and not
when it is just entering data without having updated the recordset

thanks !