Wednesday, May 2, 2012
Rich Text to Plain Text in SharePoint Client Side Object Model
string myString = item.FieldMultiLineText[Field_Name]
Simple enough!
Friday, January 28, 2011
T-SQL to Populate a Table of Country Codes with ISO 3166-1 Alpha-2 Codes and Names
Monday, January 3, 2011
CTP1 of SQL Server Denali (SQL Server 2011) will Blow up Your BIDS Dev Environment
'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
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
Thursday, December 30, 2010
Error Accessing SharePoint 2010 ListData.svc 'System.Data.Services.Providers.IDataServiceUpdateProvider' from assembly 'System.Data.Services, Version=3.5.0.0
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
Setting an Environment Variable to the "14 Hive"
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
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')
Tuesday, November 9, 2010
PowerShell Script to Monitor the Status of a SQL Job
[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
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
{
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"
Tuesday, October 5, 2010
How to determin if you are logged in via Kerberos or NTLM on SQL Server
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
Tuesday, September 14, 2010
BDC in SharePoint 2010 Foundation for a Department Level Database
Monday, September 6, 2010
PBM and ExecuteWQL()
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/
Sunday, September 5, 2010
SQL Server 2008 Extended Events
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 GO RECONFIGURE GO |
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...
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
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:
- 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.
| UPDATE [ARCUSTOMERS] SET City = REPLACE(City, '''', '') FROM [ARCUSTOMERS] WHERE City LIKE '%''%' |
Tuesday, July 6, 2010
Getting Back to Basics
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.



