{Programming} & Life

You may receive an error:

Running transformation: Microsoft.SqlServer.Management.Smo.FailedOperationException: SetParent failed for Database ‘XXXX’.  —>
Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server XXXX. —>
Microsoft.SqlServer.Management.Common.ConnectionFailureException: This SQL Server version (10.0) is not supported.

When attempting to connect to a SQL Server 2008 database when working with T4 templates or simply accessing an SQL Server directly using the SQL Server SDK via SMO.

What I found was, my code was attempting to connecting using SQL Server 2005 reference assemblies for connection, and you must ensure that your referencing the correct SQL Server (10.0) 2008 Version assemblies. If this doesn’t sound like your problem, another one could be you are attempting to restore a SQL Server 2008 database onto an SQL Server 2005 instance, this also does not work as it’s not backwards compatible. Try scripting your schema + data instead.

But back on track, for the T4 problem, where you have your assembly import declarations at the top of your template, ensure they look like the following:

<#@ assembly name="Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ assembly name="Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>

Hope I can help. This should now compile correctly with the specified version calls.