SQL COALESCE

This is how it is done....

If you want to pivot the data you could run the following command.

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

and get the following result set.

- sent by Dirkie

Check disk space in C#

Sample 1: Using System.Diagnostics.PerformanceCounter
//Get disk space info from remote server
System.Diagnostics.PerformanceCounter _pc;
float _freemegabytes;
float _freespacepercentage;
float _capacity = 0;
string result = "";
//Get free space percentage
_pc = new System.Diagnostics.PerformanceCounter("LogicalDisk", "% Free Space", serverdrive, servername);
_freespacepercentage = _pc.NextValue();
//Get free space in megabytes
_pc = new System.Diagnostics.PerformanceCounter("LogicalDisk", "Free Megabytes", serverdrive, servername);
_freemegabytes = _pc.NextValue();
//Calculate the capacity in gigabytes
_capacity = ((_freemegabytes / _freespacepercentage) * 100) / 1024;
//Calculate free space in gigabytes
_freemegabytes = _freemegabytes / 1024;
result = "" + servername + " " + serverdrive + " TotalS:" + _capacity.ToString("##.00") + "gb FreeS:" + _freemegabytes.ToString("##.00") + "gb. ";
 
Sample 2: Using System.IO.DriveInfo
//Get disk space info from local server
System.IO.DriveInfo dinfo = new DriveInfo("x:");
//Get disk size
double dsize = double.Parse(dinfo.TotalSize.ToString()) / 1073741824;
//Get free space
double dspace = double.Parse(dinfo.TotalFreeSpace.ToString()) / 1073741824;
result = "Logical Disk Size = " + dsize.ToString("##.00") + " GB";
result += " Logical Disk FreeSpace = " + dspace.ToString("##.00") + " GB";

SysPrep base Virtual Machine to create multiple images

Overview
Creating new virtual machines is bit time consuming so to create a base virtual hard drive and just make copies of it and start-up new virtual machine is ideal, but cloning/coping vhd will lead to having virtual servers with same SID and CID on your network. There are tools like NewSID to fix it but I had problem with clone/ghost virtual machine which didn't want to be joined to a domain.

What is SysPrep?
SysPrep is a tool that allows you to prepare or “prep” a machine with the operating system along with any software you wish was pre-installed and pre-configured. Once a machine is SysPrep’d, you have a new virtual hard drive that has the Windows operating system along with any additional software or features you want, such as IIS, preinstalled and pre-configured. SysPrep allows you to create your perfect system configuration packaged so that you can have a new virtual machine up and running in just minutes. And, it is available for both Windows Server 2003 32bit/64bit and Windows XP.

Where to get SysPrep?
System Preparation tool for Windows Server 2003 Service Pack 2 32bit Deployment
System Preparation tool for Windows Server 2003 Service Pack 2 64bit Deployment

How to SysPrep?
Creating the base virtual machine image/vhd

1. Create new virtual machine, I did it on Hyper-V but should work for VPC 2007/Virtual Server and more.
2. Install your OS. Windows Server 2003 R2 (latest service pack) or Windows XP.
3. Do NOT join the virtual machine to any domain.
4. Leave the administrator password blank or reset it to blank.
5. Now get all latest windows updates and all.
6. Install antivirus software and latest updates.
7. Install the virtual additions, depending which virtualization you're using.
8. Install all and latest .Net frameworks.
9. Activate the OS license. Then you don't need to re-activate the OS for each new virtual machine you create.
10. When I create a base for virtual servers (MOSS, K2, SQL or WEB servers) I install BGInfo, part of Windows sysinternals package, get it here. It creates cool desktop background with various server information as a desktop background.

Create SysPrep.inf File

Before you can SysPrep you virtual machine, you need to create a SysPrep.inf configuration file. This file contains the information about your machine. It will also prevent you from having to enter you CD Key each time you create a new virtual machine from you SysPrep’d image. Below is a sample of the SysPrep.inf file that you need to create. This file configures the SysPrep process and automates boot up process.

1. On your virtual machine, create a folder SysPrep at the root of your C: drive (C:\SysPrep).
2. Copy the following text into a text file named SysPrep.inf.
3. Enter the correct values for the following keys:
1. TimeZone – the value of 140 is Harare, Pretoria (UCT +02:00). You may want to change this to your local time zone, but it is not required to do so, Index numbers for [GuiUnattended]/TimeZone.
2. OEMDuplicatorsting – this should contain the name of the operating system you have installed on your virtual machine.
3. FullName – your name, the name you would enter if you were installing Windows.
4. OrgName – the name of your company, or blank.
5. ProductKey – Your product key (CD key) license.

Sample SysPrep.inf file:

;SetupMgrTag
[GuiUnattended]
TimeZone=140
OEMSkipRegional=1
OemSkipWelcome=1
EncryptedAdminPassword=NO
OEMDuplicatorstring="Windows Server 2003 R2 64Bit"
[Identification]
JoinWorkgroup=WORKGROUP
[Networking]
InstallDefaultComponents=Yes
[LicenseFilePrintData]
AutoMode=PerServer
AutoUsers=50
[Unattended]
OemSkipEula=Yes
InstallFilesPath=C:\sysprep\i386
[UserData]
FullName="YOUR NAME HERE"
OrgName="YOUR COMPANY NAME HERE"
ProductKey=YOUR-PRODUCT-KEY-HERE
[SetupMgr]
DistFolder=C:\sysprep\i386
DistShare=windist

Your SysPrep.inf configuration file is now ready to be used.

SysPrep-ing your Virtual Machine

SysPrep-ing your virtual machine takes just a minute or two. Most of the time is simply shutting down your virtual machine. Important: do not start this virtual machine back up or it will un-SysPrep your machine. If this does happen, you can simply go through these steps below to SysPrep you virtual machine again.

1. Run the SysPrep install tool, it will install a deploy.cab file in this location, C:\WINDOWS\system32\deploy.cab.
2. Extract all the files in the deploy.cab to C:\SysPrep.
3. Then run SysPrep.exe
4. Check the “Don't reset grace period for activation” option.
5. Make sure Shutdown mode is Shut down.
6. Click the Reseal button to shutdown and package.
7. Click OK to generate new SID's.
8. Your virtual machine will now shut down and be SysPrep’d.
9. You now have a virtual image that is SysPrep’d, but not ready to be used.
10. Before you use this image, you will need to make a backup copy of your virtual machine image. This will allow you to always have a SysPrep’d virtual machine ready and waiting.
11. Backup your SysPrep’d virtual machine image (.vhd), and rename them to something you can easily understand and that describes what your image contains. For example:
    Base2003R2x64_SysPrep.vhd
12. You now have your virtual machine SysPrep’d. You can now use this image to quickly create a new virtual machine in minutes, with a new machine name and new unique System ID (SID) each time you use it.

The SSRS 2008 Minefield

One of the big advances in Microsoft's "2008 platform", with regard to Reporting Services, was that there would be a single, consistent Report Definition Language (RDL) across all the products. This means that reports developed in Report Builder can be shared with reports developed in BIDS, and vice-versa. While one can immediately appreciate the advantages of this, it's disappointing that it seems, on this occasion, to have been at the expense of compatibility efforts.

If you've developed reports in Visual Studio 2008 and expect to be able to deploy them to SSRS 2005, then think again. You can't.....Read More...

Quick/Simple Ping in C#

private string PingServers(string host)
{
  try
  {
    System.Net.NetworkInformation.Ping ping = new System.Net.NetworkInformation.Ping();
    System.Net.NetworkInformation.PingReply pingReply = ping.Send(host);
    return "Status: " + pingReply.Status.ToString();
  }
  catch (System.Net.NetworkInformation.PingException e)
  {
    return e.Message;
  }
}

K2 SQL User Manager 101

K2 blackpearl now supports non-AD users to interact with their workflow tasks. This is performed via the integrated SQL User Manager (SQLUM) that is installed by default with K2 blackpearl. Previously, in K2.net 2003, K2 was configured to use either the default Activive Directory UM or the SQL UM. Blackpearl now installs and runs both by default.

Read More

How to implement a ASP.NET Security Provider for K2 blackpearl

The example below takes you through the steps to use the ASP.NET Authentication Provider with K2 blackpearl. A typical scenario where you might use the ASP.NET Security Provider is when your Web Application requires a user to login in order to view restricted content. This includes a K2 Worklist. This article shows how to take advantage of the out-of-box ASP.NET security components and how to extend it to enable successful integration with K2 blackpearl...

Read More

Microsoft Deployment Toolkit 2010

Get ready to deploy Windows 7 and Windows Server 2008 R2 with the final release of Microsoft Deployment Toolkit 2010.

Click here to download MDT now.

Checking the Job Status on Multiple Servers

Typically a DBA manages multiple SQL server instances. It is an important part of DBA’s job to make sure all the jobs are running as intended and scheduled on all servers. With many servers to manage this job is very tedious and time consuming if you have to manually check the jobs on each and every server. It is a management nightmare and every DBA’s Achilles-heel.

This article shows how to generate a comprehensive Job Status Report about all the jobs in a multi-server environment. You can use this report to check the status of all the jobs that are running on all servers with ease and make sure important production jobs run on schedule and have not slipped through cracks.

Step 1
First you have to create linked server connections on the master server for all your target servers that you want to monitor. For more information on how to create linked servers in SQL 2000, please check the topic “How to set up a linked server (Enterprise Manager)” in SQL Server Books Online or on the web. For help on setting up linked servers in SQL Server 2005 check the topic “Linking Servers” in books online. The following scripts will use the information from sysservers table which contains all the registered link servers in the master database. Please make sure that you can connect to all the linked servers before you proceed.

Step 2
This script creates a table called Job_Status on the master server. You have to substitute your database name for yourdb. This script is called 01_create_table_job_status.sql.

Step 3
Create the stored procedure called usp_mon_job_status_of_all_servers on the master server. You have to substitute your database name for yourdb The script is called 02_usp_mon_job_status_of_all_servers.sql.

Step 4
Create the stored procedure called usp_help_job_status on the master server. You have to substitute your database name for yourdb. The script is called 03_usp_help_job_status.sql

Step 5
Create a job that executes the procedure created in Step 3 on the master server. You can also create a schedule that runs this job daily on the master server. For more information on how to create jobs, job steps and schedules, please refer to the topic “Implementing Jobs” on SQL Server Books Online (BOL).

That’s it. You are all set. Now every day you just have to connect to the master server using Query Analyzer or SQL Server Management Studio, open a query window and run the proc usp_help_job_status as shown below. Replace your database name for yourdb.

Use yourdb
Go
Exec usp_help_job_status

And it will show you a report similar to the following:

- The Source of this article can be found @ sqlservercentral.com.

SQL TCP/IP Connections Dropping

A while ago we were plagued at a client by connections that were being forcibly closed by SQL on our Live Production DB. At first I presumed this a network error at the client because in the event log on the server, the error, "Windows Domain Controller Could Not be Found" occurred often, so at first I thought that the network connection was then being dropped by the server losing connection to the network for some or other reason, this meant I escalated the issue to networks and the end result is we sat one night and redid the box, but the error came back and caused the same problems as before.

So after more investigation, I found that there were cases on the net where people had the same isses as us...

• TCP/IP Connection was forcibly closed by the software in your machine
• Operation cannot be completed because the Socket is not a Socket.
• Software in your Remote Host has forcibly closed your connection.

The fix I applied which has led to 3 weeks of no further issues being logged is as follows...

• Open SQL Server Configuration Manager on your DB Server
• Expand the node SQL Native Client Configuration and click on Protocols
• Ensure that the Shared Memory option is disabled and that TCP/IP is ordered 1 and Named Pipes is 2

This fixed the problems we were having, again I only suggest this fix if you encounter the problems described above, obviously trying to understand what this fix does, which the guys on net coudn't explain was that Shared Memory was enabled and ordered 1, thus when the server ever took alot of strain, or had many connections, even though the connection limit was set to unlimited, it seems SQL would then go and close random/dormant/sleeping TCP Connections cause it gave priority to Shared Memory even though we never used Shared Memory.

Take note, though, becareful when disabling Shared Memory if you have applications that run on the SQL Box, so for example if we have web apps and the DB on the same server, this could end up breaking your application.

- sent by Robert aka Robbie aka Rabobi

Windows Mobile 6.5 Launching In October

More here

Recursive Queries using Common Table Expressions (CTE) in SQL Server

If you had the need to create a table with index data quickly you can use this script:

WITH tmpData AS
(
SELECT 1 as iIndex
UNION ALL
SELECT iIndex + 1 FROM tmpData WHERE iIndex <=100 ) SELECT * FROM tmpData;

This works for Dates as well:

WITH tmpData AS
(
SELECT CONVERT('2009-01-01',DATETIME) As DateValue
UNION ALL
SELECT DATEADD(dd,1,DateValue) FROM tmpData WHERE DateValue < '2010-01-01' ) SELECT * FROM tmpData;

The Source of this article can be found here;

Convert byte array to hex string

If you are like me and you regularly need to use byte arrays or hex string, you know that it is very handy to convert a byte array to a hex string.
I was always confused as to why the framework would not have a native method to do this, so I used to write my own little method to do it that looked something like this.

StringBuilder sb = new StringBuilder();
foreach (byte b in myByteArray)
{
sb.Append(b.ToString("X2"));
}
string hexString = sb.ToString();

But low and behold, the framework does have a native method that does this, I was just looking in the wrong place.

So in the BitConverter class there is a ToString method that takes a byte array as a parameter and spits out a nice hex string for you.

string hex = BitConverter.ToString(myByteArray);

Jumpbox - Free Virtualization Platforms

Jumpbox is packaged VM's that you can download with pre-loaded open source software. Just download the jumpbox and there you have a virtual server up and running with CRM, Content Management or Discussion forums ect. Most popular:

• Drupal 5.x Content Management System
• SugarCRM 4.5.x CRM System

More here

Best of Open Source Software Awards 2009

Not all gold comes from Redmond.
Read more

18 cool things Windows 7 does that Vista doesn't

My favorites is:

• Jumplist.
• Try Win + P, for multi-displays.
• XP Mode, download needed.

More here from techradar.com

Tech Ed 2009, Africa [Speaker Presentations]

• http://www.teched.co.za/presentations.aspx

Windows 7: Boot from VHD

One very cool feature of Win 7 is booting into your VHD.

• blogs.technet.com/aviraj
• sevenforums.com

Visual Studio Projects Best Practices

This is a link to some best practices with regards to how you should structure your solution and related projects depending on the size of your application and other factors. There are quite a few important factors to consider when planning a new solution, Click Here.

- sent by Matei Mihai

How to repetitively shrink a DB file

USE [YouDatabaseName]

WHILE
 (SELECT((size /128.0) - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0))
 FROM sys.database_files WHERE NAME = 'YourDataFileName') <> 0

BEGIN
 DBCC SHRINKFILE (YourDataFileName, 1)
END

This will keep on shrinking the file until the free space is 0. Remember to rebuild your indexes after this as it does create some fragmentation, also remember that most databases require a bit of free space during normal operations, so I recommend doing this only if you are planning to archive the DB.

The reason for repetitive shrinking is so that when you backup the DB (which makes it nice and small) and you at some later point restore it again, you don’t restore a huge file with lots of free space that you don’t need.

So before I archive a DB, I’ll shrink it down to its smallest size (also set the recovery model to SIMPLE and get rid of your log file), and then only backup the DB.

The goal here is to save space, if you have oodles of HDD space, then don’t worry about it.

- sent by Pieter Swart

Wayback Machine

Archived history of any website so you can see what the web used to look like.
http://www.archive.org