Archive for the ‘SQL Server’ Category

Microsoft Dynamics CRM – Windows Server & SQL Server Supported Versions

This post will provide a quick snapshot of the supported versions of Windows Server and SQL Server for Microsoft Dynamics CRM 2011. This posting will also be a quick reference post for me in the future.


Windows Server


The following Microsoft Windows Server editions and versions are supported for running Microsoft Dynamics CRM 2011

  • Windows Server 2012 Datacenter
  • Windows Server 2012 Standard
  • Windows Server 2008 Standard SP2 (x64 versions) or Windows Server 2008 Standard R2
  • Windows Server 2008 Enterprise SP2 (x64 versions) or Windows Server 2008 Enterprise R2
  • Windows Server 2008 Datacenter SP2 (x64 versions) or Windows Server 2008 Datacenter R2
  • Windows Web Server 2008 SP2 (x64 versions) or Windows Web Server 2008 R2
  • Windows Small Business Server 2008 Premium x64
  • Windows Small Business Server 2008 Standard x64
  • Windows Small Business Server 2011 Standard
  • Windows Small Business Server 2011 Essentials

    Microsoft Dynamics CRM 2011 Update Rollup 13 or a later is required with Windows Server 2012. Update Rollup 13 will also require Update Rollup 6 to be installed first.
    Windows Server 2008 installed by using the Server Core installation option is not supported for installing and running Microsoft Dynamics CRM Server 2011. Windows Server 2008 for Itanium-based systems is not supported for installing and running Microsoft Dynamics CRM 2011.


SQL Server


The following Microsoft SQL Server editions and versions are supported for running Microsoft Dynamics CRM 2011

  • Microsoft SQL Server 2008, Standard Edition, x64 SP1 or R2
  • Microsoft SQL Server 2008, Enterprise Edition, x64 SP1 or R2
  • Microsoft SQL Server 2008 Datacenter x64 SP1 or R2
  • Microsoft SQL Server 2008 Developer x64 SP1 or R2 (for non-production environments only)
  • *Microsoft SQL Server 2012, Enterprise, 64-bit
  • *Microsoft SQL Server 2012, Business Intelligence, 64-bit
  • *Microsoft SQL Server 2012, Standard, 64-bit

Microsoft Dynamics CRM Update Rollup 6 or later update rollup is required to use Microsoft SQL Server 2012.

Microsoft SQL Server 2000 and Microsoft SQL Server 2005 editions and are not supported by Microsoft Dynamics CRM 2011.

Microsoft SQL Server 2008 Workgroup, Web, Compact, or Microsoft SQL Server 2008 Express Edition editions are not supported for running Microsoft Dynamics CRM Server 2011.

32-bit (x86) versions of SQL Server database engine or Reporting Services are not supported with Microsoft Dynamics CRM Server 2011.


Greg Olsen

T-SQL – Read XML from a SQL Column Example

September 12, 2012 Leave a comment

Below is a post to show you how you can read XML data which is held within a SQL table column using T-SQL. In my example below, the SQL column I had to read from was also not held in a XML data type. Therefore the column holding the XML data had to be converted to XML to take advantage of the XML functions.

Firstly, lets take a look at the column which has the XML data. Below it is shown in a column called VEHI.

Select VEHI Columns without XML

What does this XML look like?

<BasicVehicleType xmlns:xsi="" 


<VehicleSystemId xmlns="http://localhost/Schema/CdiCommonTypes">11438229


<YearOfManufacture xmlns="http://localhost/Schema/CdiCommonTypes">2002


<Make xmlns="http://localhost/Schema/CdiCommonTypes">TOYOTA</Make>



When I looked into the data type of this column I noticed that it is not XML but a varchar data type. Therefore I recommend converting this to the XML data type first to take use of the XML functions.


VEHI Data Type Info

To convert the VEHI column to XML, we simply can use the CAST keyword. The T-SQL would look something like the following:

After the conversion was done to XML I could then use the T-SQL below to output the Make data from the XML using a select statement. The T-SQL code will look similar to the following. 

T-SQL for reading the XML data

Hope this helps you when playing around with XML SQL data.  If you have better options for dealing with XML than I have outlined here, then feel free to post a comment and let me know.

Greg Olsen


Categories: Microsoft, SQL Server, T-SQL

Microsoft Dynamics CRM 2011 – Database cannot be started in this edition of SQL Server

December 7, 2011 1 comment

This article outlines what is required to repair the AuditPFN or Database cannot be started in this edition of SQL Server’ errors respectively.

If you try and restore a CRM 2011 database organisation from SQL Server 2008 R2 Enterprise Edition instance to a SQL Server 2008 R2 Standard Edition, then you will most likely hit the error shown below.


What does this mean?

When CRM 2011 is installed using the SQL Server Enterprise Edition, then a partition is created for the auditing functionality built in CRM 2011. The AuditBase table within CRM 2011 uses partitioning which is only available within SQL Server Enterprise Edition. If you then try and restore a backup of the database from SQL Server Enterprise Edition and restore it within SQL Server Standard Edition , then it will not start because SQL Server Standard Edition doesn’t support partitioning.

The fix/change Required

What you will need to do is use a T-SQL script to remove the partitioning from the backup SQL database. Follow the steps below to fix this issue.

1. Restore the ‘<yourcrmorgname>_MSCRM’ database to a server running Microsoft SQL Server Enterprise Edition.

2. Run the following script against the restored database. This script recreates all the indexes on the primary partition and then drops the partition.

Note: Don’t use your production or live database! Use a test server for this.

IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name=‘AuditPScheme’)
CASE WHEN ind.type != 1 
    ‘DROP INDEX [dbo].[AuditBase].’
+ QUOTENAME( + ‘ ‘ 
ELSE ‘ ‘ 
END + 
ind.type_desc + ‘ INDEX ‘ + QUOTENAME(  COLLATE SQL_Latin1_General_CP1_CI_AS )  + ‘ ON [dbo].’ +  QUOTENAME(OBJECT_NAME(object_id)) + ‘ (‘ +
SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ‘ DESC’ ELSE ‘ ASC’ END + ‘,’
sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
OBJECT_NAME(sc.object_id) = ‘AuditBase’ AND
sc.object_id = ind.object_id AND
sc.index_id = ind.index_id
ORDER BY index_column_id ASC
)), 2, 8000)) + ‘)’ +
CASE WHEN ind.type = 1 
    ‘ ‘ 
END  as Script 
INTO #indexesScript
FROM sys.indexes ind
JOIN sys.partition_schemes ps on ind.data_space_id=ps.data_space_id 
OBJECT_NAME(object_id) = ‘AuditBase’
AND = ‘AuditPScheme’ 
AND is_unique_constraint = 0 
SELECT * FROM #indexesScript 
DECLARE @recreateScript nvarchar(max)
SELECT Script FROM #indexesScript
OPEN indScript
FETCH NEXT FROM indScript INTO @recreateScript
Execute sp_executesql @recreateScript
IF @@ERROR > 0
declare @message varchar(max)
set @message = ‘Audit history recreate index failed. SQL: ‘ + @recreateScript 
RAISERROR (@message, 10,1)
FETCH NEXT FROM indScript INTO @recreateScript   
CLOSE indScript   
DROP TABLE #indexesScript



3. Once you have run the script (as shown above), you can backup the database.

4. Restore the database from step 3 to a server running Microsoft SQL Server Standard edition. This database should now restore with no errors (AuditPFN issue is gone) and start correctly.

Hope this helps?

Greg Olsen
Yellow Duck Guy

CRM 4.0 & 2011 – SQL Code to show where your Plug-ins are registered

October 20, 2011 1 comment

When you register your plug-ins for Microsoft Dynamics CRM 4.0 or 2011 you either choose to register to Disk, Database or the GAC. I always prefer the database option so my CRM system is altogether and easier to move with deployments if required.

When you use the Plug-in Registration Tool, the action you choose will be written to the Microsoft CRM SQL Database. Therefore we can write T-SQL to give us that information.

Below is the T-SQL you can use to tell you where your Plug-ins are registered. This works for CRM 4.0 and CRM 2011.

CASE SourceType 
WHEN 0 THEN ‘Database’ 
WHEN 1 THEN ‘Disk’ 
END AS ‘Registered Location’ 
FROM PluginAssembly 


Output shown below from CRM 2011 SQL Database


Nothing stopping you either from building a SSRS Report displaying this information for your System or Deployment Administrators.
Greg Olsen
Yellow Duck Guy
Categories: Dynamics CRM, SQL Server

How do I know which version of SQL Server I’m running?

September 15, 2010 Leave a comment

To determine which version of Microsoft SQL Server 2005 or 2008 you are running, connect to SQL Server 2005 or 2008 by using SQL Server Management Studio, and execute the following T-SQL.

I have executed 3 T-SQL options:

  • select @@version
  • select SERVERPROPERTY (‘productversion’)
  • select SERVERPROPERTY (‘productlevel’)
  • select SERVERPROPERTY (‘edition’)


Greg Olsen
Yellow Duck Guy

Categories: SQL Server

Find a Column Name within your SQL Tables

January 6, 2009 1 comment
Below is a piece of T-SQL script which can help you find a column name within your user tables in SQL Server. Tested with SQL Server 2005.


SELECT  AS  TableName,

    AS  ColumnName

FROM        dbo.sysobjects  tab

INNER JOIN  dbo.syscolumns  col

ON       =

WHERE       tab.xtype    =  ‘U’       — User Table

AND  LIKE  ‘%geek%’  — Column Name you’re looking for.



Greg Olsen
Yellow Duck Guy

Categories: SQL Server

SQL Server 2008 – RTM Now Available!

August 10, 2008 Leave a comment

SQL Server 2008 has been released last week and is now available via MSDN for download.

Below you can see the versions available for download. Enjoy!

Greg Olsen
Yellow Duck Guy

Categories: SQL Server