Archive

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

    Note:
    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

Note:
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
YellowDuckGuy

Advertisements

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="http://www.w3.org/2001/XMLSchema-instance" 

xmlns:xsd="http://www.w3.org/2001/XMLSchema">

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

</VehicleSystemId>

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

</YearOfManufacture>

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

</BasicVehicleType>

 

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:

SELECT TOP 1 CAST(VEHI AS XML) AS VEHI 
FROM LTSAQueries
WHERE VEHI IS NOT NULL
 
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

YellowDuckGuy

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.

AuditPFNError

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’)
BEGIN
 
SELECT 
 
CASE WHEN ind.type != 1 
  
THEN 
    ‘DROP INDEX [dbo].[AuditBase].’
+ QUOTENAME(ind.name) + ‘ ‘ 
  
ELSE ‘ ‘ 
 
END + 
  ‘CREATE ‘
+ CASE is_unique WHEN 1 THEN ‘UNIQUE ‘ ELSEEND  +
 
ind.type_desc + ‘ INDEX ‘ + QUOTENAME(ind.name  COLLATE SQL_Latin1_General_CP1_CI_AS )  + ‘ ON [dbo].’ +  QUOTENAME(OBJECT_NAME(object_id)) + ‘ (‘ +
   
 
REVERSE(SUBSTRING(REVERSE((
  
SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ‘ DESC’ ELSE ‘ ASC’ END + ‘,’
  
FROM
   
sys.index_columns sc
   
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
  
WHERE
   
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
  
FOR XML PATH()
       
)), 2, 8000)) + ‘)’ +
 
CASE WHEN ind.type = 1 
  
THEN 
    ‘ WITH (DROP_EXISTING = ON) ON [PRIMARY]’ 
  
ELSE 
    ‘ ‘ 
 
END  as Script 
 
INTO #indexesScript
 
FROM sys.indexes ind
 
JOIN sys.partition_schemes ps on ind.data_space_id=ps.data_space_id 
 
WHERE
 
OBJECT_NAME(object_id) = ‘AuditBase’
 
AND ps.name = ‘AuditPScheme’ 
 
AND is_unique_constraint = 0 
 
SELECT * FROM #indexesScript 
 
 
DECLARE @recreateScript nvarchar(max)
 
DECLARE indScript CURSOR FOR
 
SELECT Script FROM #indexesScript
 
OPEN indScript
 
FETCH NEXT FROM indScript INTO @recreateScript
 
 
WHILE @@FETCH_STATUS = 0   
 
BEGIN   
 
BEGIN TRANSACTION t1
 
Execute sp_executesql @recreateScript
  
 
IF @@ERROR > 0
 
BEGIN 
  
ROLLBACK TRAN t1
  
declare @message varchar(max)
  
set @message = ‘Audit history recreate index failed. SQL: ‘ + @recreateScript 
     
RAISERROR (@message, 10,1)
 
END
 
ELSE
 
BEGIN
  
COMMIT TRAN
 
END
 
FETCH NEXT FROM indScript INTO @recreateScript   
 
END   
 
DROP PARTITION SCHEME AuditPScheme
 
DROP PARTITION FUNCTION AuditPFN
  
 
CLOSE indScript   
 
DEALLOCATE indScript 
 
DROP TABLE #indexesScript
END

 

SQLServerExecuteOutput

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.

SELECT Name, 
CASE SourceType 
WHEN 0 THEN ‘Database’ 
WHEN 1 THEN ‘Disk’ 
WHEN 2 THEN ‘GAC’ 
END AS ‘Registered Location’ 
FROM PluginAssembly 

 

Output shown below from CRM 2011 SQL Database

CRM2011Output

Nothing stopping you either from building a SSRS Report displaying this information for your System or Deployment Administrators.
 
Enjoy!
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      tab.name  AS  TableName,

            col.name  AS  ColumnName

FROM        dbo.sysobjects  tab

INNER JOIN  dbo.syscolumns  col

ON          tab.id       =  col.id

WHERE       tab.xtype    =  ‘U’       — User Table

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

ORDER BY    tab.name

Enjoy!

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