SQLCLR project wont deploy on 1 of 3 servers...

Question

Hi,

We have a SQLCLR project that we've been deploying to 2 servers for a few years with only a few minor troubles, but mostly working fine. We have just added a new server and it's failing to deploy our SQLCLR project. We're using Visual Studio 2010 as the development platform.

Platforms:
Server1 (Old): Windows XP box, 32 bit, SQL Server 2008 R2 SP2
Server2 (Old): Windows Server 2003, 32 bit, SQL Server 2008 R2 SP1
Server3 (New): Windows Server 2008 R2, 64 bit, SQL Serve r2008 R2 SP2

You may notice the new server is 64bit & Server 2008 R2 which are only the real differences. When we try to deploy to this server, VS logs these messages:

------ Build started: Project: <Our Product Name> SQL Utilities, Configuration: Release Any CPU ------
  <Our Product Name> SQL Utilities -> C:\Documents and Settings\JKidd\My Documents\Visual Studio 2010\Projects\<Our Product Name> SQL Utilities\<Our Product Name> SQL Utilities\bin\Release\<Our Product Name>Utilities.dll
------ Deploy started: Project: <Our Product Name> SQL Utilities, Configuration: Release Any CPU ------
Build started 16/08/2012 6:42:18 p.m..
SqlClrDeploy:
  Beginning deployment of assembly <Our Product Name>Utilities.dll to server NZ-NZL<Our Product Name>001\PROD : <Our Product Name>
  The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version.
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018: The "SqlClrDeployTask" task failed unexpectedly.
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018: System.ArgumentException: Illegal characters in path.
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at System.IO.Path.CheckInvalidPathChars(String path)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPathLength)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at System.IO.Path.GetFullPathInternal(String path)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at System.IO.FileInfo..ctor(String fileName)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Sql.Common.SqlClient.ConnectedHelpers.GetDefaultDatabasePath(SqlConnection conn, String commandText)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Sql.Common.SqlClient.ConnectedHelpers.GetDefaultDatabaseFilePath(SqlConnection conn)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Sql.Common.SqlClient.ConnectedHelpers.GetDefaultDatabaseFilePath(SqlConnection conn, String& filePath, String& errorMessage)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Sql.Build.SqlSchemaDeploymentOptions.RetrieveServerPaths(String& dataPath, String& logPath)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Sql.Build.SqlSchemaDeploymentOptions.OnInitialize()
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Build.SchemaDeployment.PrepareModels()
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Build.SchemaDeployment.Execute(Boolean executeDeployment)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Build.SchemaDeployment.Execute()
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Sql.SqlClr.SqlClrDeployment.Deploy(DataSchemaModel sourceModel, DataSchemaModel targetModel, String targetDBName, String connString, SqlClrDeploymentConfig config)
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Data.Schema.Tasks.Sql.SqlClrDeployTask.Execute()
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error MSB4018:    at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggingContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean& taskResult)

Build FAILED.

Time Elapsed 00:00:45.29
========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========
========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

<Our Product Name> = our actual product name which has been censored.

If we deploy to the old server and take the deployment SQL file created and run it on the New server, it deploys and runs fine; so I believe its a problem MSBUILD is running into, not a problem with the code (Note that is runs fine manually deployed). Have tried running it under accounts with administrator privileges with no success. Have tried increasing MSBUILD verbosity but the build portion is fine, only the deploy portion fails.

Has anyone run into this issue before, or have any idea of what might be going wrong?

Thanks for any suggestions in advance!
Jay :)

---

If you shake a kettle, does it boil faster?

Answer 1

Hi Jay,

I think what causes your issue is the .NET Framework that is incompatible with the target instance of SQL Server.

SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework.

More information you can refer to:

http://msdn.microsoft.com/en-us/library/dahcx0ww(VS.100).aspx

Best regards,

---

Ego [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Ego,

Thanks for the suggestion. I haven't changed the .NET Framework, using 3.5 Client Profile and it deploys fine to 2 other servers. The only thing I'm changing is the destination database (connection string) which 'tests' as fine.

I should note that the message "The followingerror might appear ifyou deploy a SQL CLR project that was built fora version ofthe .NET Framework that isincompatible withthe target instance ofSQL Server" is present for any error when deploy fails.

The first exception thrown is "System.ArgumentException: Illegal characters in path." which is a little confusing as I'm deploying to a remote server... The server name is near identical to the other server that does work (just a number increment) and the SQL server instance and database names are identical.

I wish more deploy verbosity logging was available to expose the problem...

Thanks,
Jay :)

---

If you shake a kettle, does it boil faster?

Answer 3

Problem seems to have been solved - It's now deploying without a problem.

The only actions between the last failed deploy and the first successful deploy was (a) running windows update - selecting all patches and (b) after running these patches we couldn't login to the server using remote desktop until enabling the 'phu2u' security package in the RegKey HKLM\system\currentcontrolset\control\lsa\security packages; Discussed here: http://social.technet.microsoft.com/Forums/en-US/winserverTS/thread/7698b90a-0708-4aa2-a33b-9734de14319c/

After those 2 steps (which was more likely the windows update was the fix), it now deploys fine to all 3 systems.

---

If you shake a kettle, does it boil faster?

Answer 4

Hi Jay,

I’m glad to hear that you got it working.

Thank you for sharing your solutions & experience here. It will be very beneficial for other community members who have similar questions.

Best regards,

---

Ego [MSFT]
MSDN Community Support | Feedback to us