Tuesday, April 30, 2013

How to find the total number of rows in your Database and the rate at which new rows are being added

I was working on a data migration project and needed a way to quantify the progress. Since 1 particular issue we were facing was that the whole process slowed down gradually as it went, I wanted a way to collect data points to know the current insertion rate. Kind of how you'd want to monitor the download speed when downloading a movie or something :).

In this thread, I found the following script that would return the total number of rows contained within a database using clustered indices (i.e. the primary keys), and the heap. So the only reason this would not work is if the database has design flaws.

SELECT SUM(row_count)
    FROM sys.dm_db_partition_stats
    WHERE index_id IN (0,1)
    AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0;

I added the NOLOCK hint because I don't want this to add to the problem by locking the table. Besides it is just a monitoring tool and I can compromise on the accuracy.

So the logic is,
1. collect 2 data points with an interval in between. Higher the interval, the more accurate the rate is. But you don't want to wait for too long either. So I've found that a 10 second interval works best for me.
2. get the delta which tells us the number of rows added within that period
3. divide it by the time taken for the whole process to get the number of rows per second.

DECLARE @startcount INT, @endcount INT
DECLARE @startdate DATETIME, @enddate DATETIME

SELECT @startcount = SUM(row_count),
@startdate = GETDATE()
FROM sys.dm_db_partition_stats WITH(NOLOCK)
WHERE index_id IN (0,1)
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0

WAITFOR DELAY '00:00:10'

SELECT @endcount = SUM(row_count),
@enddate = GETDATE()
FROM sys.dm_db_partition_stats WITH(NOLOCK)
WHERE index_id IN (0,1)
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0

SELECT @startcount StartCount,
@endcount EndCount,
@startdate StartTime,
@enddate EndTime,
(@endcount - @startcount)/DATEDIFF(second, @startdate, @enddate) [Rec/s]

Please note that you need to run this query with a user that has access to sys objects.

Wednesday, April 17, 2013

Adding a custom dataflow control to SSIS 2008 and 2012 (Visual Studio BIDS 2008 and 2010)

So recently i wanted to create a custom SSIS data source that could use an Azure Table storage as the data source. i found an excellent (possibly the only) implementation of this here:


The biggest problem I ran in to, being a SSIS NOOB, was getting this custom source to BIDS (Business Intelligence Development Studio)

I was using both SSIS 2008 and 2012. which meands BIDS 2008 and 2010 respectively.

Here's a copy of the thread on this here for convenience (thread already covers all the keywords which i don't want to type :))


I've created a custom data source using the instructions given in the following link:http://msdn.microsoft.com/en-us/library/ms136088.aspx
However once i build the project and try to add the new datasource to the toolbox, i get the following error:
There are no components in 'C:\Somepath\bin\Debug\CustomDataSource.dll' that can be placed on the toolbox
following are the tools i've used:
To create and build the custom datasource; Visual Studio 2010 & build in Framework 3.5
To create the SSIS package SSIS - SQL Server 2008 R2 & SQL Server Business Intelligence Development Studio (VS2008)


i found the answer to my problem; here's the solution and hope it helps someone.
  1. copy the dll to the following folder(s). the exact folder depends on whether your on x64 or x86 (doesn't hurt to have it on both):
    C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents
    C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents
  2. Install the dll to your GAC. you will need to have it signed to do this.
  3. In BI Dev Studio, go to Tools > Choose Toolbox Items. you will find your data source in the "SSIS Dataflow Items" tab
For steps 1 & 2 you might find it helpful to have a post build event like follows:
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\gacutil.exe" -u $(TargetName) 
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\gacutil.exe" -iF $(TargetFileName) 
copy $(TargetPath) "C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents" 
copy $(TargetPath) "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents"

If you're using SSIS 2012 with BIDS 2010, the toolbox items get added automatically (i.e. u don't have to go to Tools > Choose Toolbox Items) you just need to copy it to the dll right places (ref - http://fendy-huang.blogspot.com/2011/09/how-to-add-ssis-custom-component-to.html):
C:\Program Files\Microsoft SQL Server\110\DTS\PipelineComponents   
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents   
C:\Program Files\Microsoft SQL Server\110\DTS\Tasks   
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks

Thursday, May 31, 2012

REST explained

I came across this great article for beginners which explains what REST is. Dr. Elkstein also explains the difference between ROA vs SOA as well as REST vs SOAP. The comments section by itself is also very insightful.

For reference, both mine and yours these are the links.




Monday, August 15, 2011

asp RangeValidator cannot convert MaximumValue in foreign Cultures

Recently I added a range validator to 1 of our controls which is supposed to ensure that a value is between 0.1 - 50. In our application's default culture (which is en-GB) the validator works fine.

However during our localization exercise we realized that the validator throws an exception for certain cultures (in this case, when we ran the application in de-DE)

The declaration of my control is as follows:

< asp:RangeValidator ID="RangeValidator1" runat="server" ControlToValidate="SomeTextBox1" MinimumValue="0.1" MaximumValue="50" Type="Double" />

The runtime exception that was thrown was:

The value '0.1' of the MinimumValue property of 'RangeValidator1' cannot be converted to type 'Double'.
at System.Web.UI.WebControls.RangeValidator.ValidateValues()
at System.Web.UI.WebControls.RangeValidator.ControlPropertiesValid()
at System.Web.UI.WebControls.BaseValidator.get_PropertiesValid()
at System.Web.UI.WebControls.BaseValidator.OnPreRender(EventArgs e)


Well its simple. German's equivalent of 0.1 is 0,1 (with a comma instead of a dot). And when the thread is running in the german culture, it doesn't know how to convert 0.1. IMO, this is a bug in the framework. The range validator (or any other control for that matter) must not consider the culture for values that are set during design time.


What i did was;

MinimumValue='<%# (0.1).ToString(System.Globalization.CultureInfo.CurrentCulture) %>'

This way, I'm not hardcoding anything in design time. during run time the value will be set to the control in the current thread's culture. You can also do this in code behind. But I didn't have the option of recompiling the library :)

Wednesday, May 18, 2011

How to start numbering from 3rd page in MS Word 2007

Are you trying to submit your assignment before deadline? Has your report exceeded the maximum page limit? Do you want to save a few pages by not numbering your content page? Are you frustrated because the deadline is tomorrow and the page numbering is not coming right? If your answer is YES to any of these questions, I know how you feel.

Here's how to do it!

  1. Place the cursor behind the 1st letter of the page you want to start your numbering from.
  2. Go to Page Layout tab and click on Breaks
  3. From the menu, select Next Page which is under Section Breaks

This will break the content and the report in to 2 separate sections. Now you can number these sections independently of each other. To start putting page numbers, follow these steps:

  1. Place the cursor anywhere on the section you want to number. (say this is your 3rd page where you start off with your introduction)
  2. Go to Insert tab
  3. Click on Page Number menu and select a position for your page numbering

Once you do this, numbering will start from the 1st page itself. now you need to tell word that you want to number only the current section;

  1. Place the cursor anywhere on the section you want to number. (say this is your 3rd page where you start off with your introduction)
  2. Go to Insert tab
  3. Click on Page Number and select Format Page Numbers...
  4. In the Page Number Fortmat dialog box, set the Page Numbering to Start at: 1

Now you will see that there are no page numbers in your content pages, and numbers are starting from the body of the report.

Similarly if you have different sections in your report such as the preface, appendices, etc. all you need to do is make sure they are broken in to separate sections, before you put page numbering.

Saturday, April 9, 2011

Telerik RadGrid doesn't fire NeedDataSource event on Rebind()

So you're using telerik, and you want your NeedDataSource method to get called when you rebind. But it doesnt?

This is a problem I face every now and then. Each time I struggle to remember the resolution from last time but keep forgetting. Just got the same error so this time, decided to put it down here.

An event handler for the event NeedDataSource has been properly subscribed.



<telerik:RadGrid ... OnNeedDataSource="Gird_NeedDataSource">

Code behind:

protected void Grid_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
// Logic to load data

private void SomeMethod()


make the datasource of the grid null before calling rebind.
Grid.DataSource = null;

Most likely you are not adhering to the standard way the RadGrid is supposed to be implemented (which is OK in my opinion).

If you place a break point in your existing NeedDataSource event handler, you will notice that it gets called before you explicitly call Rebind.

This means you most probably set the Grid.DataSource property within the NeedDataSource event. Therefore when the DataSource property is not null, that should mean that there is 'NO need for a data source'.

Therefore quite naturally the NeedDataSource event will not get fired when you rebind the grid.
I believe this is the expected behavior of the RadGrid. If you ever have a need to explicitly set the DataSource to null before calling rebind, that means something is not following the expected standard in your code. Oh well, you have to live with it right?

If you know of any other reasons as to why the NeedDataSource wouldn't get called on rebinding the grid, feel free to post a comment on it :)

Thursday, April 7, 2011

Script control 'ControlName' is not a registered script control

So you're here because you got the following error.

Script control 'ControlName' is not a registered script control. Script controls must be registered using RegisterScriptControl() before calling RegisterScriptDescriptors().

Parameter name: scriptControl

I myself have done a fair bit of googling on this, and there are many solutions given all over the net. unfortunately these are all very scenario specific solutions. Some people get their answers, and the others, like me, don't.


You are changing the visibility of a control at the wrong stage of the page life cycle.


If you are changing the visibility of a control, you should always do it during, or before the PreRender event. If you do it after (i.e. in a End handler of an async task or during PreRenderComplete) you might run in to this issue.

This is not an issue for simple controls such as buttons or text boxes. But it will have adverse effects on controls such as grids.

When I say changing the visibility it could be any one of the following situations

  • Having visible=false of a control during the early stages of the life cycle and being changed to visible=true during an end handler or PreRenderComplete
  • Changing the selected view of a MultiView during an end handler or PreRenderComplete
  • Any other situation where the control may not be visible during the earlier stages of the page life cycle which are set to be visible during the latter stage
Purely from my understanding, ASP.NET does not render the scripts or the HTML related to a control if it is not being shown to the user. The registering of script controls which is mentioned in the exception seem to happen during an early stage of the life cycle. if the control is not visible at this stage, this registration is skipped for that control. If it's made to be visible at a latter point, you get yourself a control without some of the relevant scripts.

Anyway this is what I have understood. I may be wrong. But if you come across this issue, it will definitely help you to check for controls which are changing visibility at different points of the life cycle. You will be able to identify your specific problem by doing this and then come up with a solution on your own.

Hope this information helps some one.