Using rowversion and timestamp

If you do any kind of batch ETL processing, it can be very useful to know if any records in your database have been updated. One way to do this is to use a rowversion or timestamp column in your tables.

Books online has a good definition for timestamp:
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database.
This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value and, therefore, changes the key value.

Below are a couple of examples I came up with to demonstrate their potential. The first set uses timestamp. The second set uses rowversion. However, rowversion still uses timestamp behind the scenes so it’s not much different.


---------------------------------------
-- TIMESTAMP
---------------------------------------
-- 1. create a test table and put a few records in it
CREATE TABLE Test_TimeStamp (RowID int PRIMARY KEY, Value int, TS timestamp);
GO
INSERT INTO Test_TimeStamp (RowID, Value) VALUES (1, 0);
GO
INSERT INTO Test_TimeStamp (RowID, Value) VALUES (2, 0);
GO
INSERT INTO Test_TimeStamp (RowID, Value) VALUES (3, 0);
GO

-- 2. store the latest timestamp that is currently in the table
DECLARE @TS AS timestamp;
SET @TS = (SELECT @@DBTS AS TS)
--SELECT @TS

-- update a couple of records
UPDATE Test_TimeStamp SET Value=2 WHERE RowID=1
UPDATE Test_TimeStamp SET Value=3 WHERE RowID=2

-- show all of the records that have changed
SELECT * FROM Test_TimeStamp WHERE TS > @TS

-- show the new latest timestamp stored in the table
SELECT @@DBTS AS TS

-- 3. re-run step 2 and see the changes again and again
--DROP TABLE Test_TimeStamp
--GO

---------------------------------------
-- ROWVERSION
---------------------------------------
-- 1. create a test table and put a few records in it
CREATE TABLE Test_RowVersion (RowID int PRIMARY KEY, Value int, RV rowversion);
GO
INSERT INTO Test_RowVersion (RowID, Value) VALUES (1, 0);
GO
INSERT INTO Test_RowVersion (RowID, Value) VALUES (2, 0);
GO
INSERT INTO Test_RowVersion (RowID, Value) VALUES (3, 0);
GO

-- 2. store the latest timestamp that is currently in the table
DECLARE @TS AS timestamp;
SET @TS = (SELECT @@DBTS AS TS)
--SELECT @TS

-- update a couple of records
UPDATE Test_RowVersion SET Value=2 WHERE RowID=1
UPDATE Test_RowVersion SET Value=3 WHERE RowID=2

-- show all of the records that have changed
SELECT * FROM Test_RowVersion WHERE RV > @TS

-- show the new latest timestamp stored in the table
SELECT @@DBTS AS TS

-- 3. re-run step 2 and see the changes again and again
--DROP TABLE Test_RowVersion
--GO


SSRS Page 1 of 2?

Starting in reporting services 2008 there is a new feature called On Demand processing.
http://msdn.microsoft.com/en-us/library/bb630400(v=SQL.100).aspx#OndemandProcessing

With this feature reports may be rendered only one page at a time to speed up processing. One drawback to this can be seen on large reports. On your action bar, you may see page 1 of 2?
Here is an example:

One trick to avoid this is to add a textbox in your report header or footer. On the properties of the textbox, set Hidden=True. For the expressoin of the textbox, set it to =Globals!TotalPages.
Now when the report runs, it figures out the total number of pages to put into this textbox. And as a result, it also puts the correct number in your action bar!

Using Images as links in SSRS

In SSRS you can create drill-through links to other reports from textboxes. But if you really want to jazz things up you could instead add an Image and make it clickable. One catch is that you cannot associate an Image to a dataset to pass down values. How do we overcome this you ask?

There are two methods we can use.
1) If you used parameters in your report, you can easily add these as hyperlink parameters.
2) The trickier one is non-parameter values. To pass down a value from a dataset you need to have the
value available somewhere on your report. If your value may be different for each row, you will need to display it somewhere on your reports’ result set. Then on the Image, Action tab add another parameter and give it a name. For the expression value you would enter =ReportItems!Textbox126.value (or whatever the field name may be on your table)

Dynamic MDX

I recently wrote some dynamic MDX queries for my SSRS reports. I didn’t have much trouble until I ran into a StrToMember function that needed a double quote at the beginning and end. Hmm.. how to overcome that. Turns out you can use some VB .Net characters.

Here is a list of some common ones.
chr(39) = single quote
chr(34) = double quote
chr(10) = new line
chr(13) = carriage return

so for my example I had the following:
STRTOMEMBER("[Time Periods].[Fiscal Month Desc].&["+@FromMonth+", "+@FromYear+"]")

After I did some converting, I came up with this:
STRTOMEMBER(" & chr(39) & "[Time Periods].[Fiscal Month Desc].&[" & Parameters!FromMonth.Value & ", " & Parameters!FromYear.Value & "]" & chr(39) & ")

MDX vs T-SQL in SSAS

Using SSAS in BIDS, you need to remember that you should be using MDX instead of T-SQL when designing Calculated Members. Here is an example of how both would be used to prevent a divide by zero error.

T-SQL
-- Comment Block
CASE WHEN [Measures].[Measure2] = 0 THEN 0
ELSE [Measures].[Measure1] / [Measures].[Measure2]
END

MDX
// Comment Block
IIF([Measures].[Measure2] = 0, 0, [Measures].[Measure1] / [Measures].[Measure2])

Both will work, but the T-SQL one will give you some warnings. It’s best to remember to use MDX instead.

SSAS Formatting a Currency Calculation

In Analysis Services, when designing your cube in BIDS, under the Calculations tab you can create calculated values by using Calculated Members. If your calculation happens to be a currency type, you would be happy to find that under the Format string drop-down there is a “Currency” option. However, in my experience this doesn’t actually format the value as I would like. To show your value with dollar signs, and comma’s you can put in this value instead:
(include the double quotes)
“$#,##0.00;($#,##0.00)”

Passing parameters into SSIS from a SQL Job

Instead of hard coding values into your SSIS package, you could pass in parameter values into your SSIS variables using the following method. Here is an example of passing in a value of either a 1 or 0.
1. In your SSIS package, create a new variable with a data type of Int16. I’ll call the variable ‘YourVariable’ for this example.
2. If you want, you can use the variable on Precedence Constraints to control the flow of your package based on your variable value. So your expression might look like @YourVariable == 1.
3. Now in SSMS you can create your scheduled job and point it to your SSIS package.
4. On the ‘Set Values’ tab, under ‘Property Path’ you can put in your variable name of ‘\Package.Variables[YourVariable].Value’ and set the ‘Value’ field to what you want (ie. 0 or 1, etc).

SSRS Divide by Zero

In SSRS you often have calculated fields that do division. To avoid having divide by zero errors, you may need to use IIF statements in the expression:
=IIF(Fields!column2.Value = 0, 0, Fields!column1.Value/Fields!column2.Value)
which means if the denominator is 0, show 0, otherwise show the calculated value.

Now for reasons unknown this only works if you are using integers. If you are using decimal values, then you will need to do some more work. The best approach I’ve seen is here, where Robert Bruckner provides a nice solution.

You would go to Report -> Report Properties -> Code and insert the following:

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function


Now you can do the following in your expression:
=IIF(Fields!column2.Value = 0, 0, Code.Divide(Fields!column1.Value, Fields!column2.Value))

SSRS Formatting

SSRS has some standard formatting options predefined, yet they seemed to have left out a couple of commonly used ones.
1. Formatting currency with no cents. To do this, in the column expression field put in =FormatCurrency(Fields!column1.Value, 0). The 0 means we want no decimal places to be shown.
2. Show a number with commas, no decimal places, and a zero if the value is zero. One way to do this is to set the formatting properties to #,### but if the numbers value is zero it will show nothing. So we would want to use #,##0 to show a zero if neccesary.

Property Owner is not available for Database

If a database becomes orphaned and has no database owner, you will get the following error message when you try to view the database properties in SSMS:

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘[database]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)

You can use the following code to see orphaned databases.
SELECT databases.NAME AS DB_Name, server_Principals.NAME AS User_Name
FROM sys.[databases]
LEFT OUTER JOIN sys.[server_principals]
ON [databases].owner_sid = [server_principals].sid

To assign a new owner:
EXEC sp_changedbowner 'newuser'

If you get the following error message:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.

Open up the user’s Login Properties window under Security\Logins and uncheck the Map checkbox for the database and click OK. Basically the code won’t assign them as the new owner because it thinks they are already associated with that database. Now run the sp_changedbowner command again and it will work.