Elemental SQL

T-SQL to Populate a Table of Country Codes with ISO 3166-1 Alpha-2 Codes and Names

2011-01-28T11:52:00.000-08:00

You can use this query to populate a table with 2 character alpha country codes and their corresponding names.

/*************************************************************

Country codes taken from WikiPedia and conform to ISO 3166-1 Alpha-2
http://en.wikipedia.org/wiki/ISO_3166-1_alpha-2#Officially_assigned_code_elements

**************************************************************/


INSERT CountryCode (CountryCD, CountryName)
SELECT 'AD', 'Andorra' UNION ALL
SELECT 'AE', 'United Arab Emirates' UNION ALL
SELECT 'AF', 'Afghanistan' UNION ALL
SELECT 'AG', 'Antigua and Barbuda' UNION ALL
SELECT 'AI', 'Anguilla' UNION ALL
SELECT 'AL', 'Albania' UNION ALL
SELECT 'AM', 'Armenia' UNION ALL
SELECT 'AO', 'Angola' UNION ALL
SELECT 'AQ', 'Antarctica' UNION ALL
SELECT 'AR', 'Argentina' UNION ALL
SELECT 'AS', 'American Samoa' UNION ALL
SELECT 'AT', 'Austria' UNION ALL
SELECT 'AU', 'Australia' UNION ALL
SELECT 'AW', 'Aruba' UNION ALL
SELECT 'AX', 'land Islands' UNION ALL
SELECT 'AZ', 'Azerbaijan' UNION ALL
SELECT 'BA', 'Bosnia and Herzegovina' UNION ALL
SELECT 'BB', 'Barbados' UNION ALL
SELECT 'BD', 'Bangladesh' UNION ALL
SELECT 'BE', 'Belgium' UNION ALL
SELECT 'BF', 'Burkina Faso' UNION ALL
SELECT 'BG', 'Bulgaria' UNION ALL
SELECT 'BH', 'Bahrain' UNION ALL
SELECT 'BI', 'Burundi' UNION ALL
SELECT 'BJ', 'Benin' UNION ALL
SELECT 'BL', 'Saint Barth‚lemy' UNION ALL
SELECT 'BM', 'Bermuda' UNION ALL
SELECT 'BN', 'Brunei Darussalam' UNION ALL
SELECT 'BO', '"Bolivia, Plurinational State of"' UNION ALL
SELECT 'BQ', '"Bonaire, Saint Eustatius and Saba"' UNION ALL
SELECT 'BR', 'Brazil' UNION ALL
SELECT 'BS', 'Bahamas' UNION ALL
SELECT 'BT', 'Bhutan' UNION ALL
SELECT 'BV', 'Bouvet Island' UNION ALL
SELECT 'BW', 'Botswana' UNION ALL
SELECT 'BY', 'Belarus' UNION ALL
SELECT 'BZ', 'Belize' UNION ALL
SELECT 'CA', 'Canada' UNION ALL
SELECT 'CC', 'Cocos (Keeling) Islands' UNION ALL
SELECT 'CD', '"Congo, the Democratic Republic of the"' UNION ALL
SELECT 'CF', 'Central African Republic' UNION ALL
SELECT 'CG', 'Congo' UNION ALL
SELECT 'CH', 'Switzerland' UNION ALL
SELECT 'CI', 'Cote d`Ivoire' UNION ALL
SELECT 'CK', 'Cook Islands' UNION ALL
SELECT 'CL', 'Chile' UNION ALL
SELECT 'CM', 'Cameroon' UNION ALL
SELECT 'CN', 'China' UNION ALL
SELECT 'CO', 'Colombia' UNION ALL
SELECT 'CR', 'Costa Rica' UNION ALL
SELECT 'CU', 'Cuba' UNION ALL
SELECT 'CV', 'Cape Verde' UNION ALL
SELECT 'CW', 'Cura‡ao' UNION ALL
SELECT 'CX', 'Christmas Island' UNION ALL
SELECT 'CY', 'Cyprus' UNION ALL
SELECT 'CZ', 'Czech Republic' UNION ALL
SELECT 'DE', 'Germany' UNION ALL
SELECT 'DJ', 'Djibouti' UNION ALL
SELECT 'DK', 'Denmark' UNION ALL
SELECT 'DM', 'Dominica' UNION ALL
SELECT 'DO', 'Dominican Republic' UNION ALL
SELECT 'DZ', 'Algeria' UNION ALL
SELECT 'EC', 'Ecuador' UNION ALL
SELECT 'EE', 'Estonia' UNION ALL
SELECT 'EG', 'Egypt' UNION ALL
SELECT 'EH', 'Western Sahara' UNION ALL
SELECT 'ER', 'Eritrea' UNION ALL
SELECT 'ES', 'Spain' UNION ALL
SELECT 'ET', 'Ethiopia' UNION ALL
SELECT 'FI', 'Finland' UNION ALL
SELECT 'FJ', 'Fiji' UNION ALL
SELECT 'FK', 'Falkland Islands (Malvinas)' UNION ALL
SELECT 'FM', '"Micronesia, Federated States of"' UNION ALL
SELECT 'FO', 'Faroe Islands' UNION ALL
SELECT 'FR', 'France' UNION ALL
SELECT 'GA', 'Gabon' UNION ALL
SELECT 'GB', 'United Kingdom' UNION ALL
SELECT 'GD', 'Grenada' UNION ALL
SELECT 'GE', 'Georgia' UNION ALL
SELECT 'GF', 'French Guiana' UNION ALL
SELECT 'GG', 'Guernsey' UNION ALL
SELECT 'GH', 'Ghana' UNION ALL
SELECT 'GI', 'Gibraltar' UNION ALL
SELECT 'GL', 'Greenland' UNION ALL
SELECT 'GM', 'Gambia' UNION ALL
SELECT 'GN', 'Guinea' UNION ALL
SELECT 'GP', 'Guadeloupe' UNION ALL
SELECT 'GQ', 'Equatorial Guinea' UNION ALL
SELECT 'GR', 'Greece' UNION ALL
SELECT 'GS', 'South Georgia and the South Sandwich Islands' UNION ALL
SELECT 'GT', 'Guatemala' UNION ALL
SELECT 'GU', 'Guam' UNION ALL
SELECT 'GW', 'Guinea-Bissau' UNION ALL
SELECT 'GY', 'Guyana' UNION ALL
SELECT 'HK', 'Hong Kong' UNION ALL
SELECT 'HM', 'Heard Island and McDonald Islands' UNION ALL
SELECT 'HN', 'Honduras' UNION ALL
SELECT 'HR', 'Croatia' UNION ALL
SELECT 'HT', 'Haiti' UNION ALL
SELECT 'HU', 'Hungary' UNION ALL
SELECT 'ID', 'Indonesia' UNION ALL
SELECT 'IE', 'Ireland' UNION ALL
SELECT 'IL', 'Israel' UNION ALL
SELECT 'IM', 'Isle of Man' UNION ALL
SELECT 'IN', 'India' UNION ALL
SELECT 'IO', 'British Indian Ocean Territory' UNION ALL
SELECT 'IQ', 'Iraq' UNION ALL
SELECT 'IR', '"Iran, Islamic Republic of"' UNION ALL
SELECT 'IS', 'Iceland' UNION ALL
SELECT 'IT', 'Italy' UNION ALL
SELECT 'JE', 'Jersey' UNION ALL
SELECT 'JM', 'Jamaica' UNION ALL
SELECT 'JO', 'Jordan' UNION ALL
SELECT 'JP', 'Japan' UNION ALL
SELECT 'KE', 'Kenya' UNION ALL
SELECT 'KG', 'Kyrgyzstan' UNION ALL
SELECT 'KH', 'Cambodia' UNION ALL
SELECT 'KI', 'Kiribati' UNION ALL
SELECT 'KM', 'Comoros' UNION ALL
SELECT 'KN', 'Saint Kitts and Nevis' UNION ALL
SELECT 'KP', '"Korea, Democratic Peoples Republic of"' UNION ALL
SELECT 'KR', '"Korea, Republic of"' UNION ALL
SELECT 'KW', 'Kuwait' UNION ALL
SELECT 'KY', 'Cayman Islands' UNION ALL
SELECT 'KZ', 'Kazakhstan' UNION ALL
SELECT 'LA', 'Lao Peoples Democratic Republic' UNION ALL
SELECT 'LB', 'Lebanon' UNION ALL
SELECT 'LC', 'Saint Lucia' UNION ALL
SELECT 'LI', 'Liechtenstein' UNION ALL
SELECT 'LK', 'Sri Lanka' UNION ALL
SELECT 'LR', 'Liberia' UNION ALL
SELECT 'LS', 'Lesotho' UNION ALL
SELECT 'LT', 'Lithuania' UNION ALL
SELECT 'LU', 'Luxembourg' UNION ALL
SELECT 'LV', 'Latvia' UNION ALL
SELECT 'LY', 'Libyan Arab Jamahiriya' UNION ALL
SELECT 'MA', 'Morocco' UNION ALL
SELECT 'MC', 'Monaco' UNION ALL
SELECT 'MD', '"Moldova, Republic of"' UNION ALL
SELECT 'ME', 'Montenegro' UNION ALL
SELECT 'MF', 'Saint Martin (French part)' UNION ALL
SELECT 'MG', 'Madagascar' UNION ALL
SELECT 'MH', 'Marshall Islands' UNION ALL
SELECT 'MK', '"Macedonia, the former Yugoslav Republic of"' UNION ALL
SELECT 'ML', 'Mali' UNION ALL
SELECT 'MM', 'Myanmar' UNION ALL
SELECT 'MN', 'Mongolia' UNION ALL
SELECT 'MO', 'Macao' UNION ALL
SELECT 'MP', 'Northern Mariana Islands' UNION ALL
SELECT 'MQ', 'Martinique' UNION ALL
SELECT 'MR', 'Mauritania' UNION ALL
SELECT 'MS', 'Montserrat' UNION ALL
SELECT 'MT', 'Malta' UNION ALL
SELECT 'MU', 'Mauritius' UNION ALL
SELECT 'MV', 'Maldives' UNION ALL
SELECT 'MW', 'Malawi' UNION ALL
SELECT 'MX', 'Mexico' UNION ALL
SELECT 'MY', 'Malaysia' UNION ALL
SELECT 'MZ', 'Mozambique' UNION ALL
SELECT 'NA', 'Namibia' UNION ALL
SELECT 'NC', 'New Caledonia' UNION ALL
SELECT 'NE', 'Niger' UNION ALL
SELECT 'NF', 'Norfolk Island' UNION ALL
SELECT 'NG', 'Nigeria' UNION ALL
SELECT 'NI', 'Nicaragua' UNION ALL
SELECT 'NL', 'Netherlands' UNION ALL
SELECT 'NO', 'Norway' UNION ALL
SELECT 'NP', 'Nepal' UNION ALL
SELECT 'NR', 'Nauru' UNION ALL
SELECT 'NU', 'Niue' UNION ALL
SELECT 'NZ', 'New Zealand' UNION ALL
SELECT 'OM', 'Oman' UNION ALL
SELECT 'PA', 'Panama' UNION ALL
SELECT 'PE', 'Peru' UNION ALL
SELECT 'PF', 'French Polynesia' UNION ALL
SELECT 'PG', 'Papua New Guinea' UNION ALL
SELECT 'PH', 'Philippines' UNION ALL
SELECT 'PK', 'Pakistan' UNION ALL
SELECT 'PL', 'Poland' UNION ALL
SELECT 'PM', 'Saint Pierre and Miquelon' UNION ALL
SELECT 'PN', 'Pitcairn' UNION ALL
SELECT 'PR', 'Puerto Rico' UNION ALL
SELECT 'PS', '"Palestinian Territory, Occupied"' UNION ALL
SELECT 'PT', 'Portugal' UNION ALL
SELECT 'PW', 'Palau' UNION ALL
SELECT 'PY', 'Paraguay' UNION ALL
SELECT 'QA', 'Qatar' UNION ALL
SELECT 'RE', 'Reunion !R‚union' UNION ALL
SELECT 'RO', 'Romania' UNION ALL
SELECT 'RS', 'Serbia' UNION ALL
SELECT 'RU', 'Russian Federation' UNION ALL
SELECT 'RW', 'Rwanda' UNION ALL
SELECT 'SA', 'Saudi Arabia' UNION ALL
SELECT 'SB', 'Solomon Islands' UNION ALL
SELECT 'SC', 'Seychelles' UNION ALL
SELECT 'SD', 'Sudan' UNION ALL
SELECT 'SE', 'Sweden' UNION ALL
SELECT 'SG', 'Singapore' UNION ALL
SELECT 'SH', '"Saint Helena, Ascension and Tristan da Cunha"' UNION ALL
SELECT 'SI', 'Slovenia' UNION ALL
SELECT 'SJ', 'Svalbard and Jan Mayen' UNION ALL
SELECT 'SK', 'Slovakia' UNION ALL
SELECT 'SL', 'Sierra Leone' UNION ALL
SELECT 'SM', 'San Marino' UNION ALL
SELECT 'SN', 'Senegal' UNION ALL
SELECT 'SO', 'Somalia' UNION ALL
SELECT 'SR', 'Suriname' UNION ALL
SELECT 'ST', 'Sao Tome and Principe' UNION ALL
SELECT 'SV', 'El Salvador' UNION ALL
SELECT 'SX', 'Sint Maarten (Dutch part)' UNION ALL
SELECT 'SY', 'Syrian Arab Republic' UNION ALL
SELECT 'SZ', 'Swaziland' UNION ALL
SELECT 'TC', 'Turks and Caicos Islands' UNION ALL
SELECT 'TD', 'Chad' UNION ALL
SELECT 'TF', 'French Southern Territories' UNION ALL
SELECT 'TG', 'Togo' UNION ALL
SELECT 'TH', 'Thailand' UNION ALL
SELECT 'TJ', 'Tajikistan' UNION ALL
SELECT 'TK', 'Tokelau' UNION ALL
SELECT 'TL', 'Timor-Leste' UNION ALL
SELECT 'TM', 'Turkmenistan' UNION ALL
SELECT 'TN', 'Tunisia' UNION ALL
SELECT 'TO', 'Tonga' UNION ALL
SELECT 'TR', 'Turkey' UNION ALL
SELECT 'TT', 'Trinidad and Tobago' UNION ALL
SELECT 'TV', 'Tuvalu' UNION ALL
SELECT 'TW', '"Taiwan, Province of China"' UNION ALL
SELECT 'TZ', '"Tanzania, United Republic of"' UNION ALL
SELECT 'UA', 'Ukraine' UNION ALL
SELECT 'UG', 'Uganda' UNION ALL
SELECT 'UM', 'United States Minor Outlying Islands' UNION ALL
SELECT 'US', 'United States' UNION ALL
SELECT 'UY', 'Uruguay' UNION ALL
SELECT 'UZ', 'Uzbekistan' UNION ALL
SELECT 'VA', 'Holy See (Vatican City State)' UNION ALL
SELECT 'VC', 'Saint Vincent and the Grenadines' UNION ALL
SELECT 'VE', '"Venezuela, Bolivarian Republic of"' UNION ALL
SELECT 'VG', '"Virgin Islands, British"' UNION ALL
SELECT 'VI', '"Virgin Islands, U.S."' UNION ALL
SELECT 'VN', 'Viet Nam' UNION ALL
SELECT 'VU', 'Vanuatu' UNION ALL
SELECT 'WF', 'Wallis and Futuna' UNION ALL
SELECT 'WS', 'Samoa' UNION ALL
SELECT 'YE', 'Yemen' UNION ALL
SELECT 'YT', 'Mayotte' UNION ALL
SELECT 'ZA', 'South Africa' UNION ALL
SELECT 'ZM', 'Zambia' UNION ALL
SELECT 'ZW', 'Zimbabwe'

CTP1 of SQL Server Denali (SQL Server 2011) will Blow up Your BIDS Dev Environment

2011-01-03T12:19:00.000-08:00

If you already have SQL Server 2008 R2/SSIS installed you should not install the current CTP of Danali as BIDS 2008 will become unusable. Anytime you try to create a new project you will receive the following error:

'C:\Users\<username>\AppData\Local\Temp\temp.dtproj' cannot be opened because its project type (.dtproj) is not supported by this version of the application. 

To open it, please use a version that supports this type of project.

I suspect a reinstall of SSIS will correct the issue. For the moment I suggest a VM for the CTPs, should have taken my won advice!

Finding the Columns in a Database with Specific Names

2011-01-03T08:53:00.000-08:00

You can use this T-SQL Query on SQL Server 2005 or greater to find all the columns that contain a word. In this example it would look for any column with the word number in it.

USE DataBase
GO

SELECT table_name
      ,column_name 'Column Name'
      ,data_type 'Data Type'
      ,character_maximum_length 'Maximum Length'

FROM information_schema.columns
WHERE column_name LIKE '%number%'
GO

Error Accessing SharePoint 2010 ListData.svc 'System.Data.Services.Providers.IDataServiceUpdateProvider' from assembly 'System.Data.Services, Version=3.5.0.0

2010-12-30T06:40:00.000-08:00

If you receive this error

when attempting towork with SharePoint 2010 WCF Services you need to install one of the following hotfixes:

ADO.NET Data Services Update for .NET Framework 3.5 SP1 for Windows 7 and Windows Server 2008 R2

ADO.NET Data Services Update for .NET Framework 3.5 SP1 for Windows 2000, Windows Server 2003, Windows XP, Windows Vista and Windows Server 2008

Don't forget to download the x64 executable! And on a side note, I did have to restart my development system. Just a reset of IIS was not enough.

Creating a Drag-and-Drop Upload Web Part for SharePoint Foundation 2010

2010-12-16T09:12:00.000-08:00

Today I was working on a web part for SharePoint that would allow the user to either select files using an OpenFileDialog or via Silverlight 4’s drag-and-drop support. I came across a pretty serious issue once I completed my XAP file and uploaded it to my library. Drag-and-drop support was not available in SharePoint! When I would drag a file to the surface of the Silverlight web part and drop it, the browser would try to open the file. There is a pretty easy fix for this but I had to search all over the Interwebs to find the complete solution. Here is what you can do as well as an example of the code.

1. Create your web part.

     a. Use an ObservableCollection to bind to your list box to display files that will be uploaded. This allows the ListBox to be automatically notified when items are added or removed.

     b. Wrap your ListBox in a ListBoxDragDrop Target from the Silverlight ToolKit. This allows for items to be removed from the ListBox (and the ObservableCollection) by a drag-and-drop action.

     c. Write functionality that detects the drop of an item on the VisualRoot. I also like to include some sort of feedback like a ripple effect to show the item was added.

     d. Write functionality to upload the items to SharePoint. You might use a WCF Service or the Client Object Model.

2. The issue with drag-and-drop functionality is occurring because the OOB SharePoint 2010 web part has the option “WindowlessMode” set to true by default. You will need to create a version of the OOB web part that has this option set to false.

     a. Configure the web part to be exportable by selecting “Edit Web Part” and expanding advanced. You will see the Export Mode option.

     b. Set Export Mode to export all data and save your change.

     c. You will now see the export option on the drop down.

     d. Export the file and save it as SilverlightWindowlessMode.webpart

     e. Open the file in Notepad and change the WindowlessMode node to False. Or you can copy and paste the code example to Notepad and save it as SilverlightWindowlessMode.webpart. I would also change the Description and Title nodes.

     f. Now you must go to the Web Part gallery which is located at: http://sharepointservername/_catalogs/wp/Forms/AllItems.aspx

     g. Click the Documents tab and select Upload Document and choose your webpart file.

<webparts>
  <webpart xmlns="http://schemas.microsoft.com/WebPart/v3">
    <metadata>
      <type name="Microsoft.SharePoint.WebPartPages.SilverlightWebPart, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
      <importerrormessage>Cannot import this Web Part.</importErrorMessage>
    </metaData>
    <data>
      <properties>
        <property name="HelpUrl" type="string" />
        <property name="AllowClose" type="bool">True</property>
        <property name="ExportMode" type="exportmode">All</property>
        <property name="Hidden" type="bool">False</property>
        <property name="AllowEdit" type="bool">True</property>
        <property name="Direction" type="direction">NotSet</property>
        <property name="TitleIconImageUrl" type="string" />
        <property name="AllowConnect" type="bool">True</property>
        <property name="HelpMode" type="helpmode">Modal</property>
        <property name="CustomProperties" type="string" null="true" />
        <property name="AllowHide" type="bool">True</property>
        <property name="Description" type="string">A web part to display a Silverlight application in Windowless mode.</property>
        <property name="CatalogIconImageUrl" type="string" />
        <property name="MinRuntimeVersion" type="string" null="true" />
        <property name="ApplicationXml" type="string" />
        <property name="AllowMinimize" type="bool">True</property>
        <property name="AllowZoneChange" type="bool">True</property>
        <property name="CustomInitParameters" type="string" null="true" />
        <property name="Height" type="unit">300px</property>
        <property name="ChromeType" type="chrometype">Default</property>
        <property name="Width" type="unit">400px</property>
        <property name="Title" type="string">Silverlight Windowless Web Part</property>
        <property name="ChromeState" type="chromestate">Normal</property>
        <property name="TitleUrl" type="string" />
        <property name="Url" type="string"></property>
        <property name="WindowlessMode" type="bool">False</property>
      </properties>
    </data>
  </webPart>
</webParts>

Setting an Environment Variable to the "14 Hive"

2010-12-16T06:29:00.000-08:00

What SharePoint pro (developer or admin) does not hate the fact that the path to the root SharePoint directory is so dang long?

The solution? Create an environment variable mapped to the location.

"C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"

On Windows 7 right click "Computer" and select properties or in the search field type "System" and under Control Panel select "System". Click Advanced System Settings and Environment Variables. Click "New" under user variables. Then enter the alias you wish to give the path in Variable name (I normaly use just 14) and copy and paste the value in the code block above into the Variable value field. Start a new CMD session and test by typing

C:\> echo %14%

or open a run box and type %14%.

Finding Size Information for the Content of your WSS 3.0 Webs/Lists

2010-11-10T13:26:00.001-08:00

Today I had to hack out a T-SQL query to determine information regarding the size of indiviual lists and libraries inside of a single site collection (one content DB). I used this query and then pulled it into an Excel workbook and used pivot tables to aggregate the data.

USE [WSS_Content]
GO

SELECT
    [dbo].[Webs].[FullUrl]
   ,[dbo].[Lists].[tp_Title] AS "ListName"
   ,[dbo].[Docs].[DirName]
      ,[dbo].[Docs].[LeafName]
      ,[dbo].[Docs].[Size]
      ,[dbo].[Docs].[MetaInfoSize]
      ,[dbo].[Docs].[Version]
      ,[dbo].[Docs].[TimeCreated]
      ,[dbo].[Docs].[TimeLastModified]
      ,[dbo].[Docs].[MetaInfoTimeLastModified]
      ,[dbo].[Docs].[CheckoutUserId]
      ,[dbo].[Docs].[CheckoutDate]
      ,[dbo].[Docs].[ExtensionForFile]
  
  FROM [WSS_Content].[dbo].[Docs]
  INNER JOIN [WSS_Content].[dbo].[Webs] ON [dbo].[Webs].[Id] = [dbo].[Docs].[WebId]
  INNER JOIN [WSS_Content].[dbo].[Lists] ON [dbo].[Lists].[tp_ID] = [dbo].[Docs].[ListId]
  
  WHERE [dbo].[Docs].[Size] > 0     
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.stp')   
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.aspx')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.xfp')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.dwp')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%template%')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.inf')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.css')

PowerShell Script to Monitor the Status of a SQL Job

2010-11-09T11:40:00.000-08:00

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$job = $server.JobServer.Jobs["Test Job"]
$now = Get-Date
do
{
 Start-Sleep -Seconds 1
 $job | select Name,CurrentRunStatus,LastRunDate
 $job.Refresh()
}
while($job.LastRunDate -lt $now)
$job | select Name,CurrentRunStatus,LastRunDate

PowerShell Scripts to Continuously Ping a Host and Give an Audible Alert

2010-10-30T14:46:00.000-07:00

#Used to continuously ping a host and get an audible alert when it is back up.

function ContinuousPing-Beep
{
    param([string]$computer)
    $ping = new-object System.Net.NetworkInformation.Ping
    $result = $ping.Send($computer);
    if($result.Status -eq "Success")
    {
        Write-Host "Reply received from $computer" -Foreground green
        Write-Host `a;
    }
    else
    {
        do{$result = $ping.Send($computer);Write-Host "Reply from $computer. Destionation host unreachable." -Foreground red}
            until($result.Status -eq "Success")
            Write-Host "Reply received from $computer" -Foreground green
            Write-Host `a;
    }
}

function ContinuousPing-Voice
{
    param([string]$computer)
    
    $voice = new-object -com SAPI.SpVoice
    $ping = new-object System.Net.NetworkInformation.Ping
    $result = $ping.Send($computer);
    if($result.Status -eq "Success")
    {
        $voice.Speak("Reply received from $computer", 1)
    }
    else
    {
        do{$result = $ping.Send($computer);Write-Host "Reply from $computer. Destionation host unreachable." -Foreground red}
          until($result.Status -eq "Success")
       
        $voice.Speak("Reply received from $computer", 1)

    }
}

ContinuousPing-Beep "192.168.1.1"

ContinuousPing-Voice "192.168.1.1"

How to determin if you are logged in via Kerberos or NTLM on SQL Server

2010-10-05T12:48:00.000-07:00

Simply run the following query.

SELECT
    c.session_id
   ,s.login_name
   ,c.auth_scheme
   ,c.net_transport
   ,s.host_name
   ,s.login_time
   
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s on s.session_id = c.session_id

BDC in SharePoint 2010 Foundation for a Department Level Database

2010-09-14T18:22:00.000-07:00

One big project we are workign on currently is using the BDC to surface our company's EDI project to end users in a familiar way to have them enter confirmation of new orders from clients quickly. Before we got too deep into this we were looking at various options and ways to experiment and learn the technology. One thing we did not have was a tracking system of our servers and their assigned IP addresses and assigned roles. Here is a diagram of the testing database that I designed and wrote.

The steps to create a fully CRUD enabled application that looks like a SharePoint liust but has all the benefits of being backed by a SQL database are as follows:

1. Design your database as you normally would.

2. Create a set of views that the users will access to see the data.

3. Use INSTEAD OF triggers to make the views updateable.

4. Create External Content types to connect to the SQL data.

5. Create External Lists to display the data in SharePoint 2010.

6. Add the correct permissions to the External Content Type in the SharePoint BDC management in Central Admin.

7. Test that you can add, update, and delete items in the lists.

8. Write a webpart to supply the users with a richer, easier to understand GUI.

In my next post I hope to detail the steps for adding a CRUD enabled Silverlight web part to work with the External Lists.

PBM and ExecuteWQL()

2010-09-06T07:21:00.000-07:00

Another item of interest in studying for the SQL Server 2008 upgrade exam. This is a really interesting feature that could be combined with another monitoring system to give you a really complete view of your SQL servers every morning.

http://msdn.microsoft.com/en-us/library/aa394606(VS.85).aspx
http://msdn.microsoft.com/en-us/library/bb895209.aspx
http://sqlserverpedia.com/blog/sql-server-bloggers/executewql-in-policy-based-management/

SQL Server 2008 Extended Events

2010-09-05T12:51:00.000-07:00

In my studies for the MP: DBA SQL Server 2008 I cam accross a topic that was not included in any of the books that I have been using. It's called Extended Events and is used in diagnosing things like dealocks and high CPU utilization. Read the MSDN article.

Creating a Basic CLR Stored Procedure Using C# in Visual Studio 2010

2010-08-29T10:14:00.001-07:00

I was recently confronted with a problem managing a database of MAC addresses. Our company purchased a block of MACs and our team was tasked with the problem of creating the database and associated services for managing the MAC range.

The problem was that there did not seem to be any way to easily convert from decimal integer types to hexadecimal in T-SQL. One possible way to solve this problem is to write a simple C# CLR stored procedure as this type of conversion is pretty trivial in languages like C#.

This tutorial assumes you will be adding the CLR stored procedure to the AdventureWorks 2008 database and that you will be using SQL Server 2008 R2.

Step 1 – Enabling CLR in SQL Server

Run sp_configure to enable CLR on your test SQL Server Instance.

EXEC sp_configure 'clr enabled', 1

RECONFIGURE

Step 2 - Creating the VS 2010 Project

First, you must change the version of the.NET Framework used to 3.5 at the top of this screen or your project will not deploy. Then change the project name to sp_GetHexValue.

Add a reference to the AdventureWorks database by clicking Add New Reference.

Fill out the form and click ok.

Select AdventureWorks and click ok. Then right click the solution in Solution Explorer and select New -> Stored Procedure

Change the name to sp_GetHexValue.

Change the code to look like this:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void sp_GetHexValue(int number)

{

string hex = number.ToString("x");

SqlContext.Pipe.Send(hex);

}

};

Step 3 – Deploy the Solution

Click Build -> Deploy Solution and you should receive the following output:

------ Build started: Project: sp_GetHexValue, Configuration: Debug Any CPU ------
sp_GetHexValue -> c:\users\rkaucher\documents\visual studio 2010\Projects\sp_GetHexValue\sp_GetHexValue\bin\Debug\sp_GetHexValue.dll
------ Deploy started: Project: sp_GetHexValue, Configuration: Debug Any CPU ------
Build started 8/29/2010 12:54:41 PM.
SqlClrDeploy:
Beginning deployment of assembly sp_GetHexValue.dll to server WIN7PC : AdventureWorks
The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version.
Deployment script generated to:
c:\users\rkaucher\documents\visual studio 2010\Projects\sp_GetHexValue\sp_GetHexValue\bin\Debug\sp_GetHexValue.sql

Creating [sp_GetHexValue]...
Adding files to assembly [sp_GetHexValue]
Creating [dbo].[sp_GetHexValue]...
Creating [sp_GetHexValue].[SqlAssemblyProjectRoot]...
Creating [dbo].[sp_GetHexValue].[SqlAssemblyFile]...
Creating [dbo].[sp_GetHexValue].[SqlAssemblyFileLine]...
The transacted portion of the database update succeeded.
Deployment completed

Build succeeded.

Time Elapsed 00:00:16.99
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Step 4 – Testing

In SQL Server Management Studio open a new query and type the code shown and you will see that Intellisense in aware of the new stored procedure.

USE AdventureWorks

EXEC sp_GetHexValue 1634289

And you will receive the output 18eff1. Four simple steps to writing and deploying a simple CLR procedure.

Refocusing on Studies and Blogging

2010-08-20T09:36:00.001-07:00

It has been a crazy couple of months. I have actually gotten my dream as a SQL Server and SharePoint admin/developer. I cannot explain how happy this has made me and I have been working hard to fill in the gaps in my knowledge. Now that I'm more settled in, I'm trying to get back on track as far as my studies go and take the dang upgrade exam for the MCITP DBA 2008. I know I am pretty much ready for the test. I think with a good review of mirroring and replication I could pass without really doing any prep. So I am hoping to schedule this exam as soon as possible.

I use certification to both focus and measure my studies on a given topic. There are a couple of other areas that I need to explore and I am going to pursuing the following certifications in the next 6 months as well:

MCITP: SharePoint 2010 Administrator (70-677 | 70-668)
MCTS: SQL Server 2008, Business Intelligence Development (70-448)

I'm really hoping to be able to accomplish these quickly and be able to share some details about my studies and some of the very interesting things that I have going on at work.

The Power of Stupid...

2010-08-17T12:56:00.000-07:00

Stupid mistakes can just make you feel like an idiot sometimes. At my new job I wasted nearly 2 hours trying to figure out why some very simple C# code was not working properly. The idea was to execute the code inside of an SSIS package so that it updates a WSS 3.0 Issue Tracker list. It was simple stuff, but it took me far too long to realize that I was logged in on the wrong domain.

It was not until I wrote the same code in PowerShell that I actually got an error that made sense: “Access Denied.” I mentioned this to my boss who asked me which domain I was logged in under. Then I typed whoami and quickly realized what a dummy I had been.

Chinook Database on CodePlex

2010-08-14T10:02:00.001-07:00

The other day I was browsing around CodePlex and found a wonderful tool for learning SQL Server or for integrating your applications with SQL Server. The Chinook database, while small, has some cool appeal to it. It has all the major components you need. You can work on reporting for invoicing, sales, inventory, just about anything. You can download it at http://chinookdatabase.codeplex.com/

How to Remove Some Thing from a Column’s Data

2010-07-08T12:16:00.001-07:00

Here was a problem I was faced with today. A friend of mine was experiencing issues with two databases not syncing properly because one had a column (City) that contained a special character (a single quote). We needed to craft a query that would remove the single quote without changing any other portion of the column's data. There were two considerations that needed to be made:

Ensuring the single quote was properly escaped so that it did not cause an error at run time.
How to properly place the REPLACE function inside the UPDATE command.

It took me about 30 minutes to figure it out but here is what I came up with.

UPDATE [ARCUSTOMERS]
SET City = REPLACE(City, '''', '')
FROM [ARCUSTOMERS]
WHERE City LIKE '%''%'

Getting Back to Basics

2010-07-06T09:14:00.000-07:00

In any art or profession one can never review the fundamentals too frequently. For this reason I am looking at doing some posts on the basics of relational theory. Not so much for the sake of my readers' (Hi, mom!) but more for my own sake. Here is what I currently have planned:

Fundamentals of Relational Theory and Database Design
Fundamentals of T-SQL Querying (2005 and 2008)
T-SQL Queries and DMVs

The third item is really one of my favorites, but I feel I have not put as much effort into it as I should.

Excel Services and PowerPivot for BI

2010-06-30T12:55:00.000-07:00

I am really impressed with the enhancements in Excel services and the addition of PowerPivot to SharePoint 2010. I'm going to have to write some sort of top 10 article on the two of them. Here is an example of a dashboard I wrote for the Shceduling Department and the Production supervisors published in Excel Services. The data is driven via T-SQL queries. The datasource can even be refreshed. I'm just amazed.

Stupid SharePoint 2010 Error:The Installation of this Package Failed

2010-06-29T07:09:00.000-07:00

If you are trying to install SharePoint 2010 and you get the error message shown in the following image, you will need to redownload the installation file and try again.

I wasted an entire afternoon trying to figure out why I was getting the error above. I had installed all of the prereq's and my Server 2008 R2 was up-to-date. The executable would not even fully extract, so there was no error log or anything to help me troubleshoot. I hope this post saves someone some time.

Using sp_executesql for a Script Based “Function”

2010-05-03T09:26:00.001-07:00

On the SQL Team forums a user was requesting a way to have a function run from inside a script, because he was unable to create a UDF as he lacked the required permissions to do so. My suggestion was to use sp_executesql. The syntax is a little hard to understand. Here is an example.

DECLARE @myVar AS INT
DECLARE @test AS nvarchar(500)
DECLARE @paramDef nvarchar(500)

SET @paramDef = N'@myParam INT'
SET @myVar = 10248

SET @test = N'USE Northwind; SELECT * from dbo.Orders where dbo.Orders.OrderID = @myParam'

EXEC sp_executesql @test, @paramDef, @myParam = @myVar;

The syntax is explained below.

EXEC sp_executesql @variable,
                   --This represents the SQL code to be executed.
                   @parameterDifinition,
                   --This describes the parameter. Is it a varchar,
                   -- int, or some other type.
                   @valuePassed
                   --This holds the value you wish to pass to the
                   -- parameter.

Virtualization and SAN Basics for the DBA

2010-04-30T13:02:00.000-07:00

Brent Ozar (twitter | blog) did a really nice presentation on the basics of server virtualization and SAN for DBAs. You should be able to find a recorded copy on the Pragmatic Works web site along with some of their past presentations. I really like the ones I have seen so far.

SQL PowerShell Extensions - SQLPSX

2010-04-09T05:31:00.001-07:00

I have been working with PowerShell for a while now and have been writing my own custom tools. I have used it to implement log shipping on a SQL 2000 Server, I use it to dump things to a WSS site, and I use it for a number of management tasks. Sometimes it can be a PITA as you find yourself writing your own functions and "modules" to do simple, repetitive tasks.

Well the guys in the SQLPSX team on CodePlex have just made my life so much easier and I wanted to make sure my readers (my wife and apparently a couple guys in India according to Google Analytics) were aware of the tools that SQLPSX provide. These are simple, easy to use PoSh modules that can take objects via the pip-line like almost any other command let in PoSh. Make sure you add it to your tool box! Chris Barth did a nice presentation on it at the local PASS chapter meeting at MAXTrain in Mason, OH and it was also written up in last month's SQL Server Mag.

SQL Server 2008 DBA Exam Resources

2010-04-08T06:46:00.001-07:00

I'm breaking this post up into three parts. The first is books that I have used to study for the DBA Upgrade exam and that I believe are perfect for preparing for any of the DBA exams for SQL Server 2008. The second section is for training material I have found on the web. The third section is for books that I believe are good to read for a career as a DBA. If you already work with SQL Server these books will help you get to the next level.

Section One – MCITP: DBA 2008 Books
SQL Server 2008 Administration in Action
SQL Server 2005 DBA Street Smarts: A Real World Guide to SQL Server 2005 Certification Skills - While this book is for SQL Server 2005, all of it is applicable to SQL Server 2008. Learn how to do all of the exercises in this book and know them cold and you will be at a very strong junior level.

If you study these two books and know how to do all of the labs in them in the GUI and via T-SQL you will be a very competent SQL Server professional.

Microsoft SQL Server 2008 Fundamentals – This will set you up to have a strong fundamental understanding of T-SQL and how it works.

These three books lay the foundation for becoming a very strong SQL Server IT Pro. But I cannot stress that you cannot just read them for this to be true. You need to make these as hands on and practical as possible. Do every single exercise you can. Retype every single line of code and run it.

Section Two – Online Resources

BoL 2008
SQLServerPedia
SQL Server 2008 Virtual Labs
SQL Server 2008 How To Videos and other Resources

Section Three – Beyond the Certification

DBA Survivor: Become a Rock Star DBA – Advice and information to help you get and keep your first job a s a full-time DBA.
SQL Server 2008 Troubleshooting and Internals
SQL and Relational Theory – Learn the fundamental concepts of Relational Database theory and how to apply it to your T-SQL.
Beginning Database Design: from Novice to Professional
SQL Server MVP Deep Dives – Advice and knowledge from the best professionals in the field.

DMVs Related to Disk I/O

2010-04-05T17:54:00.001-07:00

Objective: Designing a Monitoring Strategy

Sub-objective: Design a monitoring solution at the operating system level

This is a list of DMVs I have encountered in my studies of I/O as it pertains to SQL Server. I have included a brief description from the MSDN article as well as link to the article itself. These will be highly important, not only from a certification level, but also from a practical level. This list is in DBA Survivor by Thomas LaRock and many of them are also found in sources like SQL Server 2008 Administration in Action by Rod Colledge and the chapter entitled My Favorite DMVs and Why by Aaron Bertrand in MVP Deep Dives.

DMV Name	Function
sys.dm_db_file_space_usage	Returns space usage information for each file in the database.
sys.dm_db_index_operational_stats	Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
sys.dm_db_index_usage_stats	Returns counts of different types of index operations and the time each type of operation was last performed.
sys.dm_exec_query_stats	Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
sys.dm_exec_query_plan	Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
sys.dm_io_virtual_file_stats	Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.
sys.dm_io_pending_io_requests	Returns a row for each pending I/O request in SQL Server.
sys.dm_os_wait_stats	Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
sys.dm_os_waiting_tasks	Returns information about the wait queue of tasks that are waiting on some resource.

Sp_Configure Options

2010-03-31T09:59:00.001-07:00

EXAM Objective: Installing and Configuring SQL Server 2008

Sub Objective: sp_configure

Sp_Configure Options

In a previous post I discussed sp_configure and how to use it. In this post I will be covering some of the options and their meanings. Many of the meanings are pretty straight forward but a few of them are a bit obscure. The options I will be looking at now are listed below. These are either options I have come across in preparation for the SQL Server 2008 MCITP certification or that I felt might be of practical value to know.

cost threshold for parallelism
max degree of parallelism
max server memory (MB)
min server memory (MB)
index create memory
min memory per query
AWE enabled
ad hoc distributed queries
filestream access level
backup compression default

Max Degree of Parallelism and Cost Threshold of Parallelism
The first two are a little obscure and might cause some confusion as they are obviously related, though how they are related might not be aparent at first. To begin with we will need to define the term parallelism. Parallelism is the ability for SQL Server to use multiple processors simultaneously. The max degree of parallelism option is configurable from 0 to 64. Setting this option to 1 will turn off parallel querying, setting it to 2 will limit the parallel querying to 2 processors, and so on. Setting max degree of parallelism to 0 allows SQL Server to use any number of processors. You can use WITH MAXDOP(INT) in your queries to override the server wide configuration shown by sp_configure.
SQL Server first checks for the cheapest possible seriel execution plan before it will consider parallelism. Once the Query Optimizer has determined the best possible seriel plan it compares the cost of that plan to the configured value of cost threshold of parallelism. If the seriel plan's cost is less than this value it executes the plan. If the cost threshold of paralellism is less than the cost of the seriel plan it will produce a parallel plan based on some assumptions about available memory and processor utilization. The cheapest plan will win. The number of threads used to execute the query is determined at query plan execution and is the Degree of Parallelism or DOP[1].

Further Reading

For more information regarding how Windows executes and schedules threads please see Windows Internals 5th Edition, Chapter 5 Processes, Threads, and Jobs.

Max Server Memory and Min Server Memory
The main, i.e. largest, component to SQL Server's memory management system is known as the buffer pool. The buffer pool contains several types of objects (data bages, index pages, plans, etc) and the buffer manager is in charge of managing disk I/O for brining these objects into and out of the data cache. A buffer is a memory block that's the same size as an index page or data page and when a component of SQL Server requires memory it requests a buffer from the pool [2]. The buffer pool is shared among the various caches, such as the plan cache and the data cache. It's the job of the Memory Broker to manage the various requests for memory and ensure that SQL Server is using memory efficiently.
In a 32 bit system with 4 GB RAM Windows will reserve the highest 2 GB for its own use allowing SQL Server no more than 2 GB of available memory. This can be increased to 3 GB by adding the /3GB switch to the boot.ini. By using the Max Server Memory (MB) and Max Server Memory (MB) options you can limit the memory used by SQL Server. It should also be noted that the max value of 2,147,483,647 has nothing to do with the actual amount of memory in a system, but is simply the maximum value allowed by the int data type. Min Server Memory limits the smallest amount of memory that can be allocated to SQL Server and Max Server Memory limits the total possible amount which can be allocated.

As with any setting in sp_configure you should properly test and evaluate the effects that making any change will have before deploying on a production server.

Real World

It's important to know that SQL Server will not release memory if doing so would allow the amount of memory allocated to SQL Server to drop bellow the configured value for Min Server Memory. This could have serious implications for systems running additional applications. Remember that SQL Server was designed with the expectation that it will be the only application running on the server. This reason may be why you might want to configure both Min and Max Server Memory to ensure that SQL Server always has sufficient memory to run properly and to ensure that other applications are also able to perform properly.

AWE Enabled

Here is the definition given by Wikipedia for AWE:

Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space. The process of mapping an application's virtual address space to physical memory under AWE is known as "windowing," and is similar to the "overlay" concept of other environments. AWE is beneficial to certain data-intensive applications, such as database management systems and scientific and engineering software, that need to manipulate very large data sets.

On a 32 bit system having this option enabled means that 32 bit versions of SQL Server will be able to access however much memory the edition of SQL Server allows. For Standard, Developer, and Enterprise this is the maximum the OS allows.

It should be noted that this option only applies to 32 bit systems, although this option exists on 64 bit systems, it is ignored[3].
Index Create Memory

According to the BoL 2008 article:
In SQL Server, the index create memory
option controls the maximum amount of memory initially allocated for creating indexes. If more memory is later needed for index creation, and the memory is available, the server will use it, thus exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.

Its default value is set to 0.

Min Memory Per Query

This is basically just what it sounds like. This option ensures that each query begins its life with at least this option's configured value of memory (in KBs) and it may then request more memory from the buffer pool.
It should be noted that this option takes precedence over the Index Create Memory Option discussed above. In SQL Server 2000 these were distinct options but as of SQL Server 2005 they interact.

Further Reading

BoL 2008: If you alter both options and the index create memory is less than min memory per query, you receive a warning message, but the value is set. During query execution you receive another similar warning.

Ad Hoc Distributed Queries

The Ad Hoc Distributed Queries option allows you to use the OPENROWSET and OPENDATASET functions and is disabled by default for security reasons. These functions allow you to query other data sources that use OLE DB such as Excel and CSV files. This option is deactivated by default because when enabled any authenticated user can access the providers.

Real World

Configure any data sources that need to be queried regularly as linked servers.

Filestream Access Level

Filestream is a new feature of SQL Server 2008 that allows for Binary Large Objects (BLOBs) to be stored on the NTFS file system in a manner that allows them to be transactionally consistent with the database but in such a way that it does not reduce SQL Server performance by taking up space in the buffer pool.
There are three levels to this option.

Config Value	Description
0	Disables FILESTREAM support for this instance.
1	Enables FILESTREAM for Transact-SQL access.
2	Enables FILESTREAM for Transact-SQL and Win32 streaming access.

The Enables FILESTREAM for Transact-SQL and Win32 streaming access level allows developers to access the Filestream data with a logical UNC path within a transactionally consistent framework. Because of the requirement for data to remain transactionally consistent, Filestreamdata cannot be renamed or deleted when accessed through the file system.

Backup Compression Default

This option controls the default settings for backup compression at the instance level. If, for example, you have a business requirement that states all backups need to be compressed to conserve disk space enabling this option will ensure that even T-SQL backup statements that do not explicitly declare WITH COMPRESSION will be compressed. This can still be over-ridden if the statement includes the WITH NO_COMPRESSION statement.

Real World

Creating backups with compression is only supported in SQL Server 2008 Enterprise but any edition of SQL Server 2008 (Standard, Express, etc.) can restore compressed backups.

Creating, and presumably restoring, compressed backups is a CPU intensive operation and before you do this you should make considerations for server load and utilization as it will affect the performance of SQL Server.

[1] – Pg. 71 - Daley, Kalen Microsoft SQL Server 2008 Internals, Microsoft Press; 1 edition, March 11, 2009
[2] - Daley, Kalen Microsoft SQL Server 2008 Internals, Microsoft Press; 1 edition, March 11, 2009
[3] – Daley, Kalen Microsoft SQL Server 2008 Internals, Microsoft Press; 1 edition, March 11, 2009

Bibliography
---
Daley, Kalen Microsoft SQL Server 2008 Internals, Microsoft Press; 1 edition, March 11, 2009
Itzik Ben-Gan , Etal. Inside Microsoft SQL Server 2008: T-SQL Querying, Microsoft Press; 1 edition, March 25, 2009
Russinovich, Mark and Solomon, David. Windows Internals: Including Windows Server 2008 and Windows Vista, Fifth Edition. Microsoft Press; 5 edition, June 17, 2009

Using MAX(datetime) and Sub-Queries to Find the Most Recent Item

2010-03-11T18:36:00.001-08:00

Today I was preparing a query that pulled some information from the inventory and sales tables of our ERP. The query itself was pretty easy to craft, but the VP of Sales threw a wrench into the mix; it should only contain information regarding the most recent sales order. Let's imagine my query was providing output like this table. Of course the actual query was more complex, but this gives the important information to understand the problem.

Part_No	Rev	On_Hand	Safety_Stock	SO_No	SO_Date
12345	A	10	15	S1234	12/14/2009
12345	A	10	15	S1233	10/01/2009
12345	A	10	15	S1232	08/02/2009
12346		5	0	S1231	08/01/2009
12347	-	0	0	S1230	10/20/2009
12347	-	0	0	S1229	07/15/2009

What I actually needed the query to deliver were lines 1, 4, and 5. These are only the most recent sales orders associated with each part number.

karthik_padbanaban, a fellow member of SQL Team Forums, posted a solution to another member's question, which I was able to adapt to my problem.

DECLARE @A TABLE
(
part_no VARCHAR(5),
rev CHAR,
on_hand TINYINT,
safety_stock TINYINT,
so_no VARCHAR(5),
so_date DATETIME
)

INSERT @A
SELECT '12345', 'A', 10, 15, 'S1234', '12/14/2009' UNION ALL
SELECT '12345', 'A', 10, 15, 'S1233', '10/01/2009' UNION ALL
SELECT '12345', 'A', 10, 15, 'S1232', '08/02/2009' UNION ALL
SELECT '12346', '', 5, 0, 'S1231', '08/01/2009' UNION ALL
SELECT '12347', '-', 0, 0, 'S1230', '10/20/2009' UNION ALL
SELECT '12347', '-', 0, 0, 'S1229', '07/15/2009'

SELECT * FROM @A AS A
WHERE so_date =
(
    SELECT MAX(so_date)
    FROM @A AS B
    WHERE B.part_no = A.part_no AND B.Rev = A.Rev
)

part_no	rev	on_hand	safety_stock	so_no	so_date
12347	-	0	0	S1230	10/20/2009
12346		5	0	S1231	8/1/2009
12345	A	10	15	S1234	12/14/2009

So the trick is in understanding why that query works, when just using MAX(so_date) in the principal query doesn't. Obviously if I use MAX(so_date) on the principal query it does nothing because there is only the single virtual table output by the query. We need to wrap the virtual table upon iteself so that we can create a relationship that will give us a specific result sets based on the part number/rev relationship and not just the highest (max) value for all the dates in the sales orders table. I believe this will be easier if we consider how the Query Engine processes the query.

Order	Line of Code
6	SELECT *
1	FROM @A AS A
5	WHERE so_date = (
4	SELECT MAX(so_date)
2	FROM @A AS B
3	WHERE B.part_no = A.part_no AND B.Rev = A.Rev )

Tracking this process we see that first all the data is brought together in a virtual table (1), which we will be referenced as A. Then another, new virtal table is created. This is called B (2) where A and B have the same part number and rev (3) and then the Query Engine retrives those items that have the highest date (4) from B. Next the Query engine pulls the result sets from A that have the same value as in step 4 (5). Finally the required columns are selected from the virtual table in step 5, which gives us our result (6).

Let's consider a related idea that is easier to visualize mentally because it's done via a JOIN operation. The query below is taken from SQL Server 2008 Transact-SQL Recipes by Joseph Sack, which is an amazing book. It also uses table aliases to wrap a single table onto itself and then extract information out. While these examples are different in a practical way I think they illustrate a similar approach and it was Joseph Sack's example I was trying to use as my spring board for my own answer when I found Karthik's query.

USE AdventureWorks2008
GO

SELECT s.BusinessEntityID,
SUM(s2004.SalesQuota) Total_2004_SQ,
SUM(s2003.SalesQuota) Total_2003_SQ

FROM Sales.SalesPerson s
LEFT OUTER JOIN Sales.SalesPersonQuotaHistory s2004 ON s.BusinessEntityID = s2004.BusinessEntityID
AND YEAR(s2004.QuotaDate)= 2004

LEFT OUTER JOIN Sales.SalesPersonQuotaHistory s2003 ON
s.BusinessEntityID = s2003.BusinessEntityID
AND YEAR(s2003.QuotaDate)= 2003

GROUP BY s.BusinessEntityID

Removing Duplicate Items from an Array in PowerShell and C#

2010-03-05T10:03:00.001-08:00

As a DBA I sometimes have to do some programming or scripting. One of the things that I find very useful is knowing how to remove duplicate items from an array. Most recently I had a C# application I had built that was generating duplicate items (they weren't really duplicates the part numbers were the same, however the revision was different. But the fact that the rev was different was irrelevant to the people consuming the data. Now one of the fields I needed to select was of the text data type, so I could not include DISTINCT in the query's select clause. I had to remove the duplicates from the array holding the data set. The .NET Framework 3.5 had made this a snap. You used to have to loop through the array and perform a comparison, but now you can do it with just a single line of code!

PowerShell

$abc = @("a", "a", "b", "c", "d", "e", "d")
$abc = $abc | Get-Unique

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace UniqueArray_example
{

   class Program
   {

      static void Main(string[] args)
      {

         string[] abc = {"a", "a", "b", "c", "d", "a", "c", "e"};
         abc = abc.Distinct().ToArray(); //That's it. One line.

            foreach (string letter in abc)
            {
              Console.WriteLine(letter);
            }
     }
   }
}

Simulating Log Shipping with Windows PowerShell

2010-02-22T13:31:00.000-08:00

Over the past few months I have been writing the high availability and desaster recovery documentation and procedures. When I first took this position we had no sort of disaster recovery plan in place. We used tape backups and SQL Server backups that were only done once a day. T-SQL log backups were done only once during the day and there was nothing set up to notify the database administrator (that is me) if one of the jobs failed. I also had nothing to restore the backup to, if there was a hardware failure until the replacement equipment was received. Add to the fact that the SQL Server itself is out of any sort of warranty and we had a potentially nast situation if something did go wrong.

In addition to changing the backup plans so that we are doing t-log backups every hour and adding alerts, operators, and notifications tot he server I started kicking around teh idea of using transaction log shipping to add an extra layer of protection to the system. But the issue was the production database server is SQL Server 2000 Standard, which does not include log shipping.

I decided to write a PowerShell script that would use the backups I was alreeady creating to simulate log shipping to a warm standby server. Here is what I have so far.

#############################################
## SQL Server Related variables. ##
#############################################
$ErrorActionPreference = "SilentlyContinue"
$DebugPreference = "Continue"
#SQL Server to which the database is being restored.
$SQLServer = "sqlserver"
#Name of the database being restored.
$database = "database"
#The connection string used to access teh SQL Server. Do not change.
$ConnectionString = "Server = $SQLServer; Database = master; Integrated Security = True"
#Time in seconds the script should waite for the non-query to finish executing.
$TimeOut = 1800 #1800 = 30 minutes. This is more than enough time in my environment.
#############################################
# Email Configuration Variables ##
#############################################
$EmailSub = "PSLogShipping Status Report"
$MsgText = "Included is the list of backups/logs applied and the event log."
$EmailServer = "exchange"
$EmailTo = @(email@email.com)
$emailFrom = "email@email.com"
##############################################
## General Configuration variables. ##
##############################################
#Path to the list used to keep track of logs already done.
$ListPath = "C:\script\AppliedLogs.csv"
$header = "Name, LastWriteTime, TimeApplied"
if(!(Test-Path $ListPath)){ $header | Out-File $ListPath }
$EventLog = "C:\script\EventLog.txt"
if(!(Test-Path $EventLog)){ $null | Out-File $EventLog }
#An array used by the email finction to attach the files.
$emailLogs = @($ListPath, $EventLog)
#Path to the logs.
$path = "\\server\share\"
#Test tobe sure tha path above is valid.
if(!(Test-Path $path)){ Write-Host "The path to the backup files/logs cannot be found." }
#The hour at which your list should be cleared.
$ClearList = 15

###############################################
## Do not change anything under this section ##
## unless you know what you are doing. ##
###############################################
function Send-Email()
{
param( [string]$smtpServer,
[string]$from,
[string[]]$recipient,
[string]$subject,
[string]$body,
[string[]]$attachment,
[bool]$isHTML
)

$msg = new-object Net.Mail.MailMessage
$msg.From = $from
foreach($person in $recipient)
{
$msg.To.Add($person)
}
$msg.Subject = $subject
$msg.Body = $body
$msg.IsBodyHTML = $isHTML
foreach($item in $attachment)
{
$msg.Attachments.Add($item)
}
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($msg)

}
#Append to log.
$today = Get-Date
$today.ToString() + " Starting...." | Out-File $EventLog -Append
if($today.Hour -lt $ClearList)
{
$BackupDone = $false

##Get the primary backup.
$files = get-childitem $path | where { $_.Name -match ".bak" }
$backup = $files | sort -desc LastWriteTime

#Set the date we need to work with to determine which t-logs we will look at.
#We only look at ones after the most recent backup.
if($backup.Length -gt 1)
{
$BackupDate = $backup[0].LastWriteTime
$BackupFile = $backup[0].Name
}
else
{
$BackupDate = $backup.LastWriteTime
$BackupFile = $backup.Name
}


##Get the list of t-logs. Only logs with a date greater than that of the backup.
$logs = Get-ChildItem $path | where { ($_.Name -match ".trn") -and ($_.LastWriteTime -gt $BackupDate) }

#Read the list of log files already applied.
$list = Import-Csv $ListPath

#Remove any logs/backup that have already been applied from our active list.
foreach($item in $list)
{
#Has the backup been applied yet?
if($BackupFile -eq $item.Name)
{
$logs = @($logs | Where-Object {$_.Name -ne $item.Name})
$BackupDone = $true
}
$logs = @($logs | Where-Object {$_.Name -ne $item.Name })

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Done Getting backup and transaction logs for list." | Out-File $EventLog -Append
}


##Connect to SQL Server

$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = $ConnectionString
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLCmd.Connection = $SQLConnection
$SQLCmd.CommandTimeout = $TimeOut
Write-Debug "Attempting to open connection."

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Attempting to open connection." | Out-File $EventLog -Append
$SQLConnection.Open()
$time = Get-Date -displayhint time
$time.toString() + " Connection Open." | Out-File $EventLog -Append



if($BackupDone -eq $true)
{ #Apply the logs

foreach($log in $logs)
{
Write-Debug "Attempting to execute Non-Query."
$SQLcmd.CommandText ="RESTORE DATABASE $database FROM DISK ='$path\$log' WITH NORECOVERY"
$SQLcmd.ExecuteNonQuery()

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Non-Query executed.`n RESTORE DATABASE $database FROM DISK ='$path\$log' WITH NORECOVERY" | Out-File $EventLog -Append

#Append the log info to the list.
$CurrentDate = Get-Date
$text = "`n"+$log.Name+", "+$log.LastWriteTime+", "+$CurrentDate
$text | Out-File $ListPath -Append
$text | Out-File $EventLog -Append
}

}
else
{
#Apply the backup
Write-Debug "RESTORE DATABASE $database FROM DISK ='$path\$BackupFile' WITH NORECOVERY, REPLACE"
$SQLcmd.CommandText = "RESTORE DATABASE $database FROM DISK ='$path\$BackupFile' WITH NORECOVERY, REPLACE"
$SQLcmd.ExecuteNonQuery()

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Non-Query executed.`n RESTORE DATABASE $database FROM DISK ='$path\$BackupFile' WITH NORECOVERY, REPLACE" | Out-File $EventLog -Append
#Append the backup file to the list.
$CurrentDate = Get-Date
$text = "`n"+$BackupFile+", "+$BackupDate+", "+$CurrentDate
$text | Out-File $ListPath -Append
$text | Out-File $EventLog -Append
}
#Close connection to server
Write-Debug "Closing Connection."
$SqlConnection.Close()
#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Connection Closed." | Out-File $EventLog -Append
"------------------------------------" | Out-File $EventLog -Append

}
else
{
##Connect to SQL Server 
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Server = $SQLServer; Database = master; Integrated Security = True"
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLCmd.Connection = $SQLConnection
$SQLCmd.CommandTimeout = $TimeOut
$SQLConnection.Open()
#Recover the database from the backup process.
Write-Debug "RESTORE DATABASE $database WITH RECOVERY"
$SQLcmd.CommandText = "RESTORE DATABASE $database WITH RECOVERY"
$SQLcmd.ExecuteNonQuery()

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Non-Query executed.`n RESTORE DATABASE $database WITH RECOVERY" | Out-File $EventLog -Append
#Close connection to server
$SqlConnection.Close()

#Append to log.
$time = Get-Date -displayhint time
$time.toString() + " Connection Closed." | Out-File $EventLog -Append
"------------------------------------" | Out-File $EventLog -Append
#Send the event log and the list of applied DBs.
Send-Email -smtpServer $emailServer -from $emailFrom -recipient $emailTo -subject $EmailSub -body $MyText -attachment $emailLogs -isHTML $true

#Sleep 15 seconds so the email can be sent.
Start-Sleep -s 15

#Clear the list.
Write-Host "Purging list.......`n"
$header = "Name, LastWriteTime, TimeApplied"
$header | Out-File $ListPath 
Write-Host "Done!"
}

Creating a SQL Server 2008 Cluster in a Test Lab

2010-02-09T09:06:00.001-08:00

Part One – Installing the Systems and Setting up Clustering

This article gives the basic steps in Server 2008 R2 to create a test lab with a 2-node, SQL Server failover cluster. To perform the steps in this article you will need a virtualization platform capable of running two instances of Windows Server 2008 R2 and an iSCSI target, such as iSCSI Cake or StarWind. Please note that many popular Open Source iSCSI systems, such as OpenFiler, do not support persistent reservations, which is a requirement of creating a failover cluster in Server 2008/Server 2008 R2. You should also have a working Active Directory infrastructure already installed.

Step One – The Setup

The graphic to the left shows the basic setup we are going to use. In the image the two VMs are clustered via Windows Server 2008 R2 failover clustering. They share a set of logical disks using the iSCSI protocol. As stated in the introductory paragraph I suggest using either iSCSI Cake or StarWind to set up your iSCSI targets and LUNs. If you do not know what iSCSI is or would like to brush up on the terminology I suggest the Wikipedia article. Once you have created the VMs and the iSCSI targets you should install the operating systems on the VMs. Ideally the VMs should have three network cards: one for the domain, one for the SAN, and the third for the cluster heartbeat. For the OS, I suggest simply downloading the Server 2008 R2 evaluation ISO from Microsoft. Once you have your servers installed you should run Windows update, rename them, and then join them to your domain.

After this is complete we can begin the process of adding the iSCSI disks and installing the clustering service. You will need at least two LUNs for clustering. The first will be for the cluster quorum, and the second will be for the database files.

Step two - Setting Up iSCSI

First you will need to activate the iSCSI service. To do this, click Start and then type iSCSI in the search field. The Microsoft iSCSI Initiator should appear in your search results. Open the iSCSI Initiator. You will be prompted that the service needs to be started and added to the firewall exclusion list. Click 'yes' to allow this. Once the Initiator opens, select the Discovery tab. Click 'Discover Portal'.

Type the IP address of your iSCSI target. Your portal should appear in the Target portals list.

Go to the Targets tab and you should see your targets in the list. At this point they will be inactive. Select the target or targets and then click 'Connnect'.

After you have connected to the portal you can then open disk management, bring the disks online, create the partitions, and format them with the NTFS file system.

Once those steps have been completed on the first node, repeat them on the second node. You will then be able to install the Failover Clustering service on the two nodes.

Step Three – Installing Failover Clustering
To install Failover Clustering open Server Manager and select Features. Click 'add a feature' and follow the wizard.

Step Four – Validating your Configuration
Once the installation wizard completes on both nodes you will need to run the Validate a Configuration Wizard to validate your cluster. Do not skip this step as it will help you to prevent creating problems when you install the Clustering Service. Run all of the tests.

At the end you should see a screen like the following, which gives you the go-ahead to install the cluster.

Step Five – Creating the Cluster
Once your tests have been run successfully you can begin creating the cluster with the Create Cluster Wizard. This is a fairly simple wizard; the only important steps to pay attention to is that you have added both nodes to the cluster and that you have an unused IP address that can be used for the cluster service itself. Note that this IP address will not be the one used by the SQL Server instance we will be installing later.

After the setup is complete confirm in the Cluster Manager snap-in that the cluster is running without any errors and that all of your disks have been added.

You will notice that the icons for the disks have changed when you open Computer.

You should also notice that the disks no longer appear in Computer on the second node as well as the disks being "reserved" in disk management.

Conclusion

As you can see Microsoft has made getting a basic cluster up and running very easy. But it should be noted that clustering is not a substitute for back up, nor is it a silver bullet for high-availability. Microsoft's cluster does its job very well, but in order for it to help you when the chips are down you need to understand not only how to install and implement it, but you also need to know what it can and cannot do. Just as an example, say our SAN goes down, then what? Having a cluster in no way protects our database in that way.

In the next part of this article we will discuss how to prepare the servers for the installation of SQL Server 2008 as well as how to slip stream SP1 into the 2008 installation. I will also outline some labs and tests that can be run.

Installing and Configuring SQL Server 2008: sp_configure

2010-01-13T09:02:00.001-08:00

EXAM Objective: Installing and Configuring SQL Server 2008

Sub Objective: sp_configure

What is sp_configure?

Sp_configure is a system stored procedure used to display and modify information about the configuration of SQL Server. If you simply type sp_configure into a query window and run it you will get output that looks like the following table, which is a sample of the output of running this command on my developmental server.

name	minimum	maximum	config_value	run_value
access check cache bucket count	0	16384	0	0
access check cache quota	0	2147483647	0	0
Ad Hoc Distributed Queries	0	1	0	0
affinity I/O mask	-2147483648	2147483647	0	0
affinity mask	-2147483648	2147483647	0	0
….
locks	5000	2147483647	0	0
max degree of parallelism	0	64	0	0
max full-text crawl range	0	256	4	4
max server memory (MB)	16	2147483647	2147483647	2147483647
max text repl size (B)	-1	2147483647	65536	65536
max worker threads	128	32767	0	0
…

The full list can be found at the end of this post. The above should give you a good idea of the settings and values that can be seen and changed through sp_configure. Many DBAs who have become dependent on using GUI based tools like SSMS and Configuration Manager might be reluctant to use this tool even to view the settings. But the fact is it will always be faster to use a query than to do any of this through the GUI. While I can agree that GUI based tools add a feeling of security, we should not be afraid to challenge ourselves to grow as professionals and true mastery only comes from being able to perform tasks accurately and in the most efficient manner possible. That being said, do not attempt to make changes to production servers using this or any other tool without a complete understanding of the settings you are changing. Be sure to consult BoL 2008 for a thorough description of what the settings do.

How to Use sp_configure

Sp_configure works like any other stored procedure. You use EXEC the procedure name and then the name of the option you wish to change followed by the value. The full syntax can be found here. Here is an example.

/***
* This code will run sp_configure and change the 'Show Advanced Options' vslue to 1;
* which turns it on. The only question is, does this take effect immediately?
*
***/

EXEC sp_configure 'Show Advanced Options', 1

MINI LAB: Is running sp_configure enough to actually change the setting in real time? What is the difference between the config_value column and the run_value column? Which settings can be changed on the fly and which require a reboot?

Before you continue reading run a few examples of sp_configure on a SQL Server instance. Please be sure this is on a test server as changing these setting on a production machine could negatively impact performance and availability.

It is not enough just to run sp_configure to change the run_value of a setting. You need to use the following code to modify the actual value that the instance is running with.

EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

The RECONFIGURE command actually changes the running configuration of any property that is dynamic, meaning it can be changed without a reboot. If you want to find out if a property is dynamic or not, you need to query the instance metadata using the sys.Configurations DMV [LINK].

SELECT * FROM sys.Configurations
WHERE name LIKE '%advanced%'

This will return several columns, one of which is called is_dynamic. If this value is set to 1 then the value can modified by using RECONFIGURE and a reboot or restart of services is not required.

Column Meanings

The meaning of the column headers should be pretty clear, but I will cover them here briefly.

name: The name of the property to be changed or viewed.
minimum: The minimum value setting that is allowed
maximum: The maximum value that is allowed
config_value: The value that the instance is currently configured with.
run_value: The value the instance is currently running with.

Real World: You do not have to type all of the text of the name of the value you wish to change. You only need to specify a non-ambiguous amount. So if you would like to know the value for the max server memory (MB) just type sp_config 'max s'.

Example output of sp_configure from a developmental box on which I am running SQL Server 2008.

name	minimum	maximum	config_value	run_value
access check cache bucket count	0	16384	0	0
access check cache quota	0	2147483647	0	0
Ad Hoc Distributed Queries	0	1	0	0
affinity I/O mask	-2147483648	2147483647	0	0
affinity mask	-2147483648	2147483647	0	0
Agent XPs	0	1	1	1
allow updates	0	1	0	0
awe enabled	0	1	0	0
backup compression default	0	1	0	0
blocked process threshold (s)	0	86400	0	0
c2 audit mode	0	1	0	0
clr enabled	0	1	0	0
common criteria compliance enabled	0	1	0	0
cost threshold for parallelism	0	32767	5	5
cross db ownership chaining	0	1	0	0
cursor threshold	-1	2147483647	-1	-1
Database Mail XPs	0	1	0	0
default full-text language	0	2147483647	1033	1033
default language	0	9999	0	0
default trace enabled	0	1	1	1
disallow results from triggers	0	1	0	0
EKM provider enabled	0	1	0	0
filestream access level	0	2	2	0
fill factor (%)	0	100	0	0
ft crawl bandwidth (max)	0	32767	100	100
ft crawl bandwidth (min)	0	32767	0	0
ft notify bandwidth (max)	0	32767	100	100
ft notify bandwidth (min)	0	32767	0	0
index create memory (KB)	704	2147483647	0	0
in-doubt xact resolution	0	2	0	0
lightweight pooling	0	1	0	0
locks	5000	2147483647	0	0
max degree of parallelism	0	64	0	0
max full-text crawl range	0	256	4	4
max server memory (MB)	16	2147483647	2147483647	2147483647
max text repl size (B)	-1	2147483647	65536	65536
max worker threads	128	32767	0	0
media retention	0	365	0	0
min memory per query (KB)	512	2147483647	1024	1024
min server memory (MB)	0	2147483647	0	8
nested triggers	0	1	1	1
network packet size (B)	512	32767	4096	4096
Ole Automation Procedures	0	1	0	0
open objects	0	2147483647	0	0
optimize for ad hoc workloads	0	1	0	0
PH timeout (s)	1	3600	60	60
precompute rank	0	1	0	0
priority boost	0	1	0	0
query governor cost limit	0	2147483647	0	0
query wait (s)	-1	2147483647	-1	-1
recovery interval (min)	0	32767	0	0
remote access	0	1	1	1
remote admin connections	0	1	0	0
remote login timeout (s)	0	2147483647	20	20
remote proc trans	0	1	0	0
remote query timeout (s)	0	2147483647	600	600
Replication XPs	0	1	0	0
scan for startup procs	0	1	0	0
server trigger recursion	0	1	1	1
set working set size	0	1	0	0
show advanced options	0	1	1	1
SMO and DMO XPs	0	1	1	1
SQL Mail XPs	0	1	0	0
transform noise words	0	1	0	0
two digit year cutoff	1753	9999	2049	2049
user connections	0	32767	0	0
user options	0	32767	0	0
xp_cmdshell	0	1	1	1