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