Thursday, December 30, 2010

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

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.

Thursday, December 16, 2010

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

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.

Setting an Environment Variable to the "14 Hive"

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%.

Wednesday, November 10, 2010

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

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]

   ,[dbo].[Lists].[tp_Title] AS "ListName"
  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')

Tuesday, November 9, 2010

PowerShell Script to Monitor the Status of a SQL Job


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

Saturday, October 30, 2010

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

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

function ContinuousPing-Beep
    $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;
        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
    $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)
        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 ""

ContinuousPing-Voice ""

Tuesday, October 5, 2010

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

Simply run the following query.

FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s on s.session_id = c.session_id  

Tuesday, September 14, 2010

BDC in SharePoint 2010 Foundation for a Department Level Database

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.

Monday, September 6, 2010

PBM and ExecuteWQL()

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.

Sunday, September 5, 2010

SQL Server 2008 Extended Events

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.

Sunday, August 29, 2010

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

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

Friday, August 20, 2010

Refocusing on Studies and Blogging

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.

Tuesday, August 17, 2010

The Power of Stupid...

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.

Saturday, August 14, 2010

Chinook Database on CodePlex

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

Thursday, July 8, 2010

How to Remove Some Thing from a Column’s Data

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:
  1. Ensuring the single quote was properly escaped so that it did not cause an error at run time.
  2. 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.
SET City = REPLACE(City, '''', '')
WHERE City LIKE '%''%'

Tuesday, July 6, 2010

Getting Back to Basics

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.

Wednesday, June 30, 2010

Excel Services and PowerPivot for BI

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.

Tuesday, June 29, 2010

Stupid SharePoint 2010 Error:The Installation of this Package Failed

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.

Monday, May 3, 2010

Using sp_executesql for a Script Based “Function”

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 @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.

                   --This describes the parameter. Is it a varchar,

                   -- int, or some other type.
                   --This holds the value you wish to pass to the

                   -- parameter.

Friday, April 30, 2010

Virtualization and SAN Basics for the DBA

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.

Friday, April 9, 2010

SQL PowerShell Extensions - SQLPSX

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.

Thursday, April 8, 2010

SQL Server 2008 DBA Exam Resources

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
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.

Monday, April 5, 2010

DMVs Related to Disk I/O

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.

Wednesday, March 31, 2010

Sp_Configure Options

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

Thursday, March 11, 2010

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

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 10 15 S1234 12/14/2009
12345 10 15 S1233 10/01/2009 
12345 10 15 S1232 08/02/2009 
12346 S1231 08/01/2009 
12347 S1230 10/20/2009 
12347 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.

Friday, March 5, 2010

Removing Duplicate Items from an Array in PowerShell and C#

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!

Monday, February 22, 2010

Simulating Log Shipping with Windows PowerShell

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.

Tuesday, February 9, 2010

Creating a SQL Server 2008 Cluster in a Test Lab

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.

Wednesday, January 13, 2010

Installing and Configuring SQL Server 2008: sp_configure

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.
access check cache bucket count
access check cache quota
Ad Hoc Distributed Queries
affinity I/O mask
affinity mask
max degree of parallelism
max full-text crawl range
max server memory (MB)
max text repl size (B)
max worker threads

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.