Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS” in the equal to operation

Working in a Microsoft System Center Service Manager 2010 database I had added a couple of new tables for doing Business Hour Calculations ( see SQL Calculate Business Hours post). I added the tables calendar and holiday for the business hour calculations and when I attempted to execute my SQL function for doing the business hour calculation between date ranges I received the error.

After searching and searching I found a simple solution. First a useful query to determine what the collation is for a columns in a table. In my case I performed the query on the calendar table and day_name and day_number were both set incorrectly.


SELECT
    col.name, col.collation_name
FROM 
    sys.columns col
WHERE
    object_id = OBJECT_ID('YourTableName')




This query will return the collation for each column specified, for me it instantly helped my identify the collation conflict and I was able to execute the following query to correct the issue.


ALTER TABLE YourTableName
  ALTER COLUMN OffendingColumn
    VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL


After I ran the alter statement, I tried the business hour function again and sure enough it worked perfectly.

SQL Server 2008 - No Administrator Access

In the past I've experienced issues with SQL Server where the domain account or group that I designated for administrator access loses the ability to perform any administrative actions. Strange behavior because it will let the accounts log in to the server but anytime a domain user attempts an action that requires elevated permissions an error is thrown.

One quick solution is to logon to the machine as the local administrator rather than any domain account and use this account to re-add any domain user or group permissions. By default this account is given administrative access on the SQL server.

SQL - Calculate Business Hours Minus Holidays

Calculating Business Hours 

I was recently tasked with generating SSRS reports against a Microsoft System Center Service Manager data warehouse. The reports require that only business hours be used to extract things like how long a service management ticket was worked etc. In my case the service desk is open M-F from 7:30 to 4:30 and no weekends or Federal Holidays. I expected to find a number of good examples with a few searches but nothing came up that fit my needs exactly.

Here's a quick rundown of how I was able to satisfy the reporting requirements.

First - create a new table to store the work week hours including Saturday and Sunday, this table will be used whenever a query is generated to determine how many hours fall within the "open" hours of the service desk.

I started the process with some examples I gathered here - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74645 I then extended the examples to include Holidays and fix the queries when the start date and end date were on the same day.

Open SQL Management Studio and attach to the database you wish to add this new table to, paste the following SQL code into the query and execute.

CREATE TABLE [dbo].[ttr_calendar] (

[day_number] [varchar] (50) NOT NULL ,

[day_name] [varchar] (50) NULL ,

[begin_time] [datetime] NULL ,

[end_time] [datetime] NULL ,

[duration] [real] NULL 

) ON [PRIMARY]

Next I'll populate the table with the hours of the service desk. Execute the following statement

insert into ttr_calendar

select 1,             'Monday',      '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 2,             'Tuesday',     '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 3,             'Wednesday',   '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 4,             'Thursday',    '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 5,             'Friday',      '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 6,             'Saturday',    '7:30:00 AM',    '4:30:00 PM',   0 union all

select 7,             'Sunday',      '7:30:00 AM',    '4:30:00 PM',   0

For brevity I'll just link the F_Table_Date function that I used in conjunction with the other tables, this code will need to be copied and executed on our SQL instance, this will generate a function that is used in the query.

Using F_Table_Date - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

Function F_TABLE_DATE is a multistatement table-valued function that returns a table containing a variety of attributes of all dates from @FIRST_DATE through @LAST_DATE. In short, it’s a calendar table function.

Next I need to create a table to store the Holiday information - Note: This table will have to be manually populated with your holiday information, simply add a title i.e. Christmas, New Years, etc. and the date in the following format 12/25/2012.

USE [DWDataMart] /* <--Your Database Name */

GO

/****** Object:  Table [dbo].[ttr_Holiday]    Script Date: 05/22/2012 14:37:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ttr_Holiday](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NOT NULL,

[Date] [date] NOT NULL,

 CONSTRAINT [PK_ttr_Holiday] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Now that all the pieces are in place I can generate the actual query that will return my business hours minus Holidays.

declare @start_date datetime,

@end_date datetime,

@temp int

select @start_date = '24 May 2012 07:30:00 AM',

@end_date = '29 May 2012 04:30:00 PM'

Select @temp = COUNT(ttr_holiday.date)*9 from ttr_holiday where ttr_holiday.date Between @start_date and @end_date 

select @temp, total_hours = sum(case

when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then

CASE

WHEN CONVERT(VARCHAR(8),@start_date,108) < '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) > '16:30:00'

THEN  

datediff(second, [DATE] + begin_time, [DATE] + end_time)

WHEN CONVERT(VARCHAR(8),@start_date,108) < '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) < '16:30:00'

THEN  

datediff(second, [DATE] + begin_time, @end_date)

WHEN CONVERT(VARCHAR(8),@start_date,108) > '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) < '16:30:00'

THEN  

datediff(second, @start_date, @end_date)

WHEN CONVERT(VARCHAR(8),@start_date,108) > '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) > '16:30:00'

THEN  

datediff(second, @start_date, [DATE] + end_time)

END

when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then

case

when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time)

else duration

end

when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then

case

when @end_date <  [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date)

else duration

end

else duration

end

 ) 

 

/ 60.0 / 60.0 - @temp

from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c

on d.WEEKDAY_NAME_LONG = c.day_name

This query will also take into account the start date and end date being on the same day, in several examples that I looked at this part would not return the correct results.

I hope this post helps with calculating business hours.

Programmatically Change Sharepoint Web Application IP Address

Recently I was asked about changing a Sharepoint Web Application IP Addresses programatically, is this possible and how will it affect the Sharepoint sites?

I did some testing and it turns out that it is indeed possible to change the IIS Website IP Address without impacting the Sharepoint Web Application(s). For the most part Sharepoint does not care about the IP Address that are assigned to it's Web Application(s), what it cares about is how the IIS sites are mapped to it's Site Collections. The mapping is done via host header in IIS and Alternate Access Mappings in Sharepoint.

Hopefully there are no Sharepoint sites that are accessed using IP Addresses, if so when the IP Address changes, things will break.

Using  Host Headers and Alternate Access Mappings allow access via friendly names, these names are only bound by DNS so as

To create a friendly name like 'Portal' first I'll create a host header in IIS to my Sharepoint site, host headers allow me to have several IIS sites and only require one IP address.

In IIS select the Sharepoint site and click Bindings from the Actions menu, enter a friendly name, in my case 'Portal'

 Next Ill create a new A record on my DNS server so that the name Portal resolves to my Sharepoint Web Applications IP Address.

Next I'll create a Sharepoint Alternate Access Mapping so that Sharepoint knows what to do when it receives a request for this friendly name 'Portal'

Open Sharepoint Central Administration, select Application Management and under Web Applications select configure alternate access mappings.

Select the alternate access mapping collection for your site and select edit public URLs

Next add the url for your friendly name in one of the zone text boxes

After this configuration is complete Sharepoint can be accessed by typing http://portal. Next I want to programmatically change the IP address of my Sharepoint Web Application.

I found a Powershell script to perform this exact task.


$oldIp = "172.16.3.214"
$newIp = "172.16.3.215"


# Get all objects at IIS://Localhost/W3SVC
$iisObjects = new-object `
    System.DirectoryServices.DirectoryEntry("IIS://Localhost/W3SVC")


foreach($site in $iisObjects.psbase.Children)
{
    # Is object a website?
    if($site.psbase.SchemaClassName -eq "IIsWebServer")
    {
    $siteID = $site.psbase.Name


    # Grab bindings and cast to array
    $bindings = [array]$site.psbase.Properties["ServerBindings"].Value


    $hasChanged = $false
    $c = 0


    foreach($binding in $bindings)
    {
    # Only change if IP address is one we're interested in
    if($binding.IndexOf($oldIp) -gt -1)
    {
    $newBinding = $binding.Replace($oldIp, $newIp)
    Write-Output "$siteID: $binding -> $newBinding"


    $bindings[$c] = $newBinding
    $hasChanged = $true
    }
    $c++
    }


    if($hasChanged)
    {
    # Only update if something changed
    $site.psbase.Properties["ServerBindings"].Value = $bindings


    # Comment out this line to simulate updates.
    $site.psbase.CommitChanges()


    Write-Output "Committed change for $siteID"
    Write-Output "========================="
    }
    }
}

Note: Remember DNS will have to be changed first to accommodate the IP Address change.

Provided there are multiple IP Addresses available on the Sharepoint server, this script will look for an old IP Address and update the IIS website to the new IP Address. Again Sharepoint does not care about the IP Address but it only cares about the request coming to it via IIS and matching the name with a Site Collection.

Hopefully this is helpful, it can be useful in fail over scenarios where re-iping at the fail over site is required.

Passed VCP 510 (VMWare Certified Professional)

I recently passed the VCP 510 exam renewing my VCP again before the deadline of February 28th 2012. If I wouldnt have taken the exam before this date I would have had to enroll in the week long training class prior to taking the exam.

The exam was very much like what I have been used to as far as the previous VMWare VCP exams have been, it did seem like there were more select multiple answer type questions than usual.

Here is a great study site that currently has 927 test questions posted, I can say that these questions are right inline with what you can expect to see on the test. If you can get comfortable with these test questions you will surely pass the test with no issues.

Check out the test questions here

SharePoint 2010 101 Code Samples

Microsoft has released 101 code samples for Sharepoint 2010. The samples include HTML 5, AJAX, JQuery, WCF, REST and a lot more.

Check them out Here

Some of the projects include...

• SharePoint 2010: Performing Cross-List Queries
• SharePoint 2010: Retrieving Single List Items in REST Requests
• SharePoint 2010: Developing Connected Web Parts
• SharePoint 2010: Creating Custom SharePoint Service Applications
• SharePoint 2010: Calling WCF Services from Timer Jobs
• SharePoint 2010: Leveraging HTML5 Objects in SharePoint
• SharePoint 2010: Developing Custom Navigation Providers
• SharePoint 2010: Using JavaScript to Show Dialog Boxes
• SharePoint 2010: Using LINQ in REST Requests
• SharePoint Online: Creating and Deploying Sandboxed Web Parts
• SharePoint 2010: Calling WCF Services Hosted in SharePoint
• SharePoint 2010: Developing Ribbon Drop Down Controls
• SharePoint 2010: Calling WCF Services from Web Parts
• SharePoint 2010: Developing Workflow Activities
• SharePoint 2010: Creating Custom Timer Jobs
• SharePoint 2010: Developing Ribbon Actions

Each code sample is part of the SharePoint 2010 101 code samples project. These samples are provided so that you can incorporate them directly in your code. Each code sample consists of a standalone project created in Microsoft Visual Studio 2010

SoftArtisans OfficeWriter - Sharepoint 2010 - Word 2010

I recently had the opportunity to check out SoftArtisans OfficeWriter product. The OfficeWriter product exposes an API that allows information from custom ASP.NET applications to be consumed and used to dynamically and programmatically build Microsoft Word documents and Microsoft Excel spreadsheets.

The OfficeWriter API is a .NET library that allows you to read, manipulate and generate Microsoft Word and Microsoft Excel documents from your own applications. The OfficeWriter product can integrate with Sharepoint 2010 allowing you to export Sharepoint list data into Microsoft Word and Excel documents.

SoftArtisans provides easy to understand sample code, videos and pre-built Sharepoint solutions that make getting started with the product very trivial.

For this tutorial I'll demonstrate deploying, configuring and testing Word Export Plus in a Sharepoint 2010 environment. Word Export Plus is a SharePoint solution that demonstrates the usage of the OfficeWriter API in SharePoint 2010. This solution adds a new context menu (custom action) button to list items, allowing you to export the list data to a pre-formatted Word template that can be designed yourself in Word, or automatically generated by Word Export Plus.

Requirements for this tutorial

1. SoftArtisans OfficeWriter -  Installed and properly licensed, download the trial from Here
2. SoftArtisans Word Export Plus - Download the Sharepoint solution installer from Here
3. Sharepoint 2010  - A site in place that will be used to test the Sharepoint integration features
4. Microsoft Word 2010 - Installed on the Sharepoint 2010 server

Installation


Launch the WordExportPlus.msi executable, this will install the Sharepoint solution into the Sharepoint Farm.

After the Sharepoint solution has been installed it can be deployed

Open Sharepoint Central Administration
Navigate to System Settings |  Farm Management  | Manage Farm Solutions
Select Word Export Plus.wsp
Select Deploy Solution
Specify a deployment time
Select OK

After the solution has been deployed it can be enabled through the site features on the site where the tool is going to be used.

After Word Export Plus has been activated I'll configure it for use.

First I'll create a contacts list that will be the data source for my exports, select Site Actions | View All Site Content | Create and select Contacts

I'll name the list People and select OK.

After the list is created I'll create a couple of test contacts in the list.

Next I'll configure Word Export Plus by selecting Site Actions | Site Settings | OfficeWriter Solution: Word Export Plus

First I'll choose the list that I just created (People) in the Sharepoint List dialog, in the Word Template dialog select Create a new template file. In the Template File Location select the Shared Documents library of the current site. Enter a name for the Template File (Word Template). Enter a name for the Action (Word)

This completes the configuration, 

select Create New Action

To test the new custom action browse to the People list created previously, from one of the contacts select the dropdown next to the last name column and select the action name created previously (Word)

A dialog will open asking if I would like to open or save the WordWriter.Doc file, select Save and after the file is downloaded select Open

This is how the auto-generated template will render the Sharepoint list data, I could have created my own Word template and selected that during the configuration steps. This provides a flexible solution to allow users to get the list data out quickly and formatted nicely into Microsoft Word.