Converting seconds to HH:MM:SS

Since a lot of people are asking for a good way to convert seconds to HH:MM:SS format, I created a small function to do this. After posting my answer to the tek-tips forum, I received an even better solution. So here it is:

CREATE FUNCTION SecTimeDay (@sec integer)
RETURNS VARCHAR(19)
AS
BEGIN
   DECLARE @DayTime VARCHAR(19)
   /* Calculate # of days and display if necessary */
   SELECT @DayTime =
      CASE WHEN @Sec >= 86400
         THEN CONVERT(VARCHAR(5), @Sec/86400) + ' days '
         ELSE ''
      END
      /* Add HH:MM:SS to number of days (or ') for output */
      + CONVERT(VARCHAR(8), DATEADD(Second, @Sec, 0), 108)
   RETURN @DayTime
END

Combining SQL Server Profiler and Windows Performance Logs and Alerts to monitor performance issues

SQL Server Profiler can give you a great hand when troubleshooting performance in a SQL Server database. If you combine Profiler with the Performance Logs and Alerts snap in, you can put your sql statements next to the performance counter to make it easy to find the weak points.

In the following example, I'll show you how you can find a deadlock by combining both tools:

Set up of the performance counter:

• Open the Performance Logs and Alerts snap-in (Administrative Tools)

• Expand Performance Logs and Alerts, right-click Counter Logs and click New Log Settings
  

• Choose a name for the log (I've used DeadlockLogging)

• In the General tab click Add Counters

• Set the properties as below:




• Performance object: SQLServer:Locks
• Select counters from list: Number of Deadlocks/sec
• Select instances from list: _Total

• Click Close

• In the General tab, change the Interval to 1 second
  

• In the Log Files tab, select Text File (Comma delimited) for the Log File Type
  

• In the Schedule tab, select both Start and Stop methods to Manually
  
• Click OK (if a warning message occurs to create the directory, click Yes)
  

• Select the counter you just created and start it
  

Set up of the SQL Server Profiler:

• Open SQL Server Profiler (All Programs\Microsoft SQL Server 2005\Performance Tools\SQL Server Profiler)
  

• Create a new trace (File\New Trace ...) and connect to the server
  

• In the Use the template box, select TSQL_Replay
  

• Check the Save to file and specify a location for the file
  

• Click Run
  

Create a deadlock situation:

• Open SQL Server Management Studio
  

• Run the following query:
  USE tempdb;
  GO
  
  CREATE TABLE Product (
  ProdID INT,
  ProdName VARCHAR(50)
  )
  GO
  
  INSERT INTO Product VALUES (1, 'Book')
  GO
  
  CREATE TABLE ProdOrder (
  ProdOrderID INT,
  ProdID INT,
  OrderCode VARCHAR(10)
  )
  GO
  
  INSERT INTO ProdOrder VALUES (1, 1, 'BKO1')
  GO
  

• Enter the following query but do not run it yet:
  USE tempdb;
  GO
  
  BEGIN TRAN
  UPDATE Product
  SET ProdName = 'DVD'
  WHERE ProdID = 1
  WAITFOR DELAY '00:00:10'
  
  UPDATE ProdOrder
  SET OrderCode = 'DVD01'
  WHERE ProdOrderID = 1
  

• Open a new query window and type the following statement:
  USE tempdb;
  GO
  
  BEGIN TRAN
  UPDATE ProdOrder
  SET OrderCode = 'BK01'
  WHERE ProdOrderID = 1
  WAITFOR DELAY '00:00:10'
  
  UPDATE Product
  SET ProdName = 'BOOK'
  WHERE ProdID = 1
  

• Start both queries
  

• You will see that after a few seconds, the following error will be shown: "Msg 1205, Level 13, State 45, Line 8 Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
  

Look up the deadlock in Profiler:

• Open the Performance Log and Alerts and stop the DeadlockLogging counter

• Switch to SQL Server Profiler and stop the trace
  

• Close SQL Server Profiler and open the trace file you just created
  

• From the File menu, select Import Performance Data and select the log file created by Performance Log and Alerts

• In the Performance Counter Limit Dialog window make sure that everything is selected and click OK

• Now you can loop throught the timebar and see which select statement performed the deadlock
  

FREE Book: 7 Development Projects for MS Office Sharepoint Server 2007 and Windows Sharepoint Services Version 3.0

While browsing the net I found that Microsoft is offering a free eBook about Sharepoint 2007: 7 Development Projects for MS Office Sharepoint Server 2007 and Windows Sharepoint Services Version 3.0

Download it here: link

Get rid of "Click to activate and use this control"

The latest browsers have changed the way to handle the rendering of ActiveX components. When an ActiveX component needs to be rendered, a message with "Click to activate and use this control" will be shown to the user. This is good for security but can be frustating for the users of your site.

It is possible to avoid this by setting the ActiveX component via an external script. So in case of having your object tag in your html page, you need to put it in a .js file OUTSIDE the HTML page (it does not work when generated inside the html page) and reference it from your html/aspx page.

Here is an example:
Content of the html page which has a loads the content of a javascript file:
<html>
<body>
...
<script type="text/javascript" language="javascript" src="script.js"></script>
...
</body>
</html>

Content of the script.js file:
document.write("<object id='myApplet' classid='MyActiveX.dll#Namespace.ClassName'></object>");

In case of using a JavaScript file, it is also possible to do the same via an innerHTML or via the DOM as long as you add the object from outside the HTML page.

Intellisense in SQL Server Management Studio

SQL Prompt is a handy tool that adds intellisense functionality to your SQL Server Management Studio. After changing some settings, it becomes a real time saver.

Unfortunately, it is no longer for free.
Info: http://www.red-gate.com/products/SQL_Prompt/index.htm

Error when referencing a javascript via the <LINK> tag in combination with .NET Applet (ActiveX)

When embedding a JavaScript file with the below code in combination with .NET Applets, strange things might happen.

<link href="../script/myJavaScript.js" type="text/javascript">

If you run your page, you will receive the following message:


Details:


When looking in eventviewer you can see the following:

Faulting application iexplore.exe, version 7.0.5730.11, faulting module msvcr80.dll, version 8.0.50727.762, fault address 0x00014904.


To solve this problem, you need to include your JavaScript with a SCRIPT tag like this:
<script src="../script/myJavaScript.js" type="text/javascript"></script>

NOTE: Close the script tag explicitly since otherwise the page stops rendering at the end of the script line.

What's so bad about string concatenation?

A lot of developers use the + operator to concatenate multiple strings. This works fine so what is wrong with this approach?

First of all, strings are immutable objects. An immutable object is an object that can't be modified after it has been created. So when using the + operator, in case of adding the second string at the end of the first string, .NET creates a new string with the content of both strings. This can cause an object-allocation overhead and put pressure on the garbage collector.

There are 2 possible solutions to avoid this:

• use the string.Format() method
• use the StringBuilder class

I'll give an example of both methods. Since the String.Format method is normally used for formatting strings, as the name suggests, I will focus myself on the StringBuilder.

I. String.Format() method

When using this method to concatenate strings, you need to create one string with a number of "replacers" ({0}, {1}, {2}, ..., {n}). Each replacer will be replaced by the referenced argument ({0} will reference to the first argument, {1} will reference to the second argument). The number of replacers must be same as the number of arguments.

Here is an example:

string value1 = "Geert Verhoeven";
string value2 = "Belgium";

string result = string.Format("My name is {0} and I live in {1}.", value1, value2);

The string in result will be: My name is Geert Verhoeven and I live in Belgium.

Extra info: http://msdn2.microsoft.com/en-us/library/fht0f5be.aspx

II. StringBuilder method

The StringBuilder class can be found in the System.Text namespace so you will need to add a using statement.

First you need to create a StringBuilder object. The StringBuilder class has a method "Append" to append strings. When you added the last string to the StringBuilder, you can retrieve the entire string by using the ToString() method of the StringBuilder class.

StringBuilder sb = new StringBuilder();
sb.Append("This is the first part of the string");
sb.Append("This is the second part of the string");
sb.Append("This is the third part of the string");
sb.Append("This is the fourth part of the string");

string entireString = sb.ToString();

How it works:
A StringBuilder object internally contains a character buffer. This buffer expands as you append characters. When creating a StringBuilder it has enough space for 16 characters. If you go beyond 16 characters, the class creates a new buffer which is double the size as the previous buffer.

Too much expanding can affect the performance of the StringBuilder class. To avoid this, you can specify the initial size of the buffer when creating a new StringBuilder instance.

StringBuilder sb = new StringBuilder(1024);

Extra info: http://msdn2.microsoft.com/en-us/library/system.text.stringbuilder.aspx

Use CHAR instead of VARCHAR when the size of the content is known in advance

When browsing the MSDN forums, I see a lot of people using the datatype VARCHAR for fixed length columns. The VARCHAR datatype is good if you don't know the size of the content in advance but if you do know the size in advance it is better to use the CHAR datatype.

Here is a comparison between both datatypes concerning the storage:

Data Type	Range	Storage
CHAR(n)	1 to 8000 characters	(n) bytes
NCHAR(n)	1 to 4000 unicode characters	(n * 2) bytes
VARCHAR(n)	1 to 8000 characters	Variable. Storage is the actual length plus 2 bytes.
NVARCHAR(n)	1 to 4000 unicode characters	Variable. Storage is the actual length plus 2 bytes.

The difference is that the VARCHAR and the NVARCHAR data types, need an additional 2 bytes opposed to theire fixed versions. You wont see the difference with small tables. For bigger tables, this can give a waste of disk space and be less performant since the table requires more pages.

Visual Studio .NET Code Editing Tips & Tricks

While surfing the net, I bumped up an interesting document that contains tips & tricks to increase your productivity in Visual Studio .NET 2005.

http://www.agiledeveloper.com/articles/VSDOTNETCodeEditingTipsAndTricks.pdf

Most of the tips in the article are handy shortcuts.

Get the quarter of a given date in datetime format

In SQL Server you can get the quarter of a year by using the datepart function. This will return 1/1/year, 4/1/year, 7/1/year or 10/1/year depending on the date you entered.

Sometimes, it is necessary to get the quarter of a given date in datetime format. Here is a simple scalar function how to do it:

CREATE FUNCTION GetQuarter (@date DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(DATETIME, CONVERT(CHAR(8),
DATEPART(YEAR, @date) * 10000 +
( DATEPART(QUARTER, @date) * 3 - 2) * 100 +
1),
112)
END

Use the function like this:

 
SELECT dbo.GetQuarter(dateColumn) AS Quarter FROM myTable

Map IP address to specific pcname

Sometimes there is a need to map the name of a pc to a different ip address. In Windows this is possible by editing the hosts file.

For example when a part of an application connects to a productionserver PROD01 which is down, it can be handy to forward the requests to a developer server. Of course, this is only usefull if you don't have access to the connectionstring in the application.

The following steps explain how to do this:

• go to C:\Windows\system32\drivers\etc


• open the hosts file


• at the end of the file add a new entry as seen in the screenshot
  
  

This will forward all requests to PROD01 to 10.10.10.1 which can be the IP address of a different pc (for example the development server).

Using the BackgroundWorker

The backgroundworker component is a new component in .NET 2.0 that allows you to easily run costly processes in the background. This way the user interface is not blocked.

In this sample I'm using the BackgroundWorker to read a 12MB large xml file in a dataset. After the xml file is read, I'll show its contents in a datagrid on a WinForms application. When refreshing the grid you will see that the UI is not blocked.

Here we go:

• Create a new WinForms application (the example code is written in C#)
  
  
• Add a Button and a DataGridView control to the form
  
  
• Add a BackgroundWorker component to the form
  
  
• Double click on the DoWork and RunWorkerCompleted events in the propertygrid to create the associated methods
  
  
• The code in the DoWork event handler is run on a separate and dedicated thread.
  Here we will add the code to read the xml file in a dataset and return it to the calling method.
  
  Copy the following code in the DoWork eventhandler (explanation will folow):
  

  private void backgroundWorker1_DoWork(object sender,
                                        DoWorkEventArgs e)
  {
     // Use the parameter that has been passed
     string fileName = (string)e.Argument;
  
     // Load the file in the dataset
     DataSet ds = new DataSet();
     ds.ReadXml(fileName);
  
     // Set the dataset to the result property to make it
     // available to the calling thread
     e.Result = ds;
  }
  
  
  To pass a parameter to the background thread we can use the DoWorkEventArgs.Argument property.
  To pass the result back to the calling thread, we use the DoWorkEventArgs.Result property.

• When the background process is finished, we need to do something with the result. This is done in the RunWorkerCompleted eventhandler. The following code retrieves the DataSet out of the Result property from the RunWorkerCompletedEventArgs argument.
  
  

  private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
  {
     // Get the dataset out of the result property
     DataSet xmlContent = (DataSet)e.Result;
  
     // Bind the dataset to the gridview
     dgvXmlContent.DataSource = xmlContent.Tables[0];
  }

• Now the only thing that rests us is tell the BackgroundWorker to start.
  
  

  private void btnLoadXML_Click(object sender, EventArgs e)
  {
     string fileName = @"C:\Sample.xml";
     backgroundWorker1.RunWorkerAsync(fileName);
  }

  
  

That's all there is to it. When the data is loaded and you click again on the Load button, you will see that you can continue working with the grid. At a certain moment, it will flash indicating that a new databinding has occured.