Visual Studio 2010 and SQLCLR: Some Good, Some Bad

Hi Adam

Do you use VS2010 Ultimate 32 bit?

Ah lets see whether triple-posting issue is still exists.

Good Feedback!

Another thing I noticed is debugging (using VS 2010) clr's using "attach to process" for complex assemblies, instead of debugging with auto-deploy and test scripts is a no-go.. sql just hangs, until you detach..

Also tried adding a config file to sqlservr.exe forcing the correct debugger, but this does not work either..

Not cool Microsoft!

Thanks so much for this post, specially the framework version issue (bad stuff) nearly saved my life!

I'm reading this about a year later -- thanks for a great series of articles, Adam!

I've discovered a very annoying aspect of deployment of CLR in VS 2010:  if you try to deploy into a "large" DB with thousands of objects, it hangs forever.

See: http://blogs.msdn.com/b/psssql/archive/2011/01/24/deploying-clr-assembly-hangs-with-visual-studio-2010.aspx

The hack work-around suggested is to deploy to a small database, then edit the deployment scripts to change the database.  This was ridiculous: it's easier and more straightforward to create my own T-SQL script to drop and create the assembly and functions directly.

As Andries said above: Not cool Microsoft!

Hi Adam, happy holidays.

I came across to the loopback connection c# script in one of your posts. I really like the concept, I used to pass ServerName, database name to each clr proc or function. I added the script to my project. It builds fine. I am using VS2010 with SP1 against a SQL 2008 SP3 database. For some reason it does not let me deploy it. I boiled it down to static variable "private static readonly string connection_string;", if I make it private only (no static) it deploys fine. I searched around quite a bit what the cause will be with no luck. I figured you may already run into the similar problem or so.

By the way I started following your blog (starting following some blogs couple months ago and got addicted now ;-)). I like efficient coding and directly applied your string splitting, whoisactive, xtended events etc, just awesome. We are here in Miami, and my company does not have the habit of flying people for classes. Anyway whenever I get a chance I'd like a week long sql education ;-)

Happy holidays

The script is below.

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

static class connection_builder

{

   private static readonly string connection_string;

   static connection_builder()

   {

       using (var local_conn = new SqlConnection("context connection = true"))

       {

           local_conn.Open();

           var comm = new SqlCommand();

           comm.Connection = local_conn;

           comm.CommandText =

               @"SELECT

                   @server_name = @@SERVERNAME,

                   @db_name = DB_NAME()";

           comm.Parameters.Add("@server_name", System.Data.SqlDbType.VarChar, 128).Direction = System.Data.ParameterDirection.Output;

           comm.Parameters.Add("@db_name", System.Data.SqlDbType.VarChar, 128).Direction = System.Data.ParameterDirection.Output;

           comm.ExecuteNonQuery();

           SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();

           sb.DataSource = (string)comm.Parameters[0].Value;

           sb.InitialCatalog = (string)comm.Parameters[1].Value;

           sb.IntegratedSecurity = true;

           sb.Enlist = false;

           connection_string = sb.ConnectionString;

       }

   }

   public static SqlConnection get_loopback_connection()

   {

       return (new SqlConnection(connection_string));

   }

}

------ Build started: Project: UtilClr, Configuration: Debug Any CPU ------

 UtilClr -> C:\Users\gvarol\Documents\Util\UtilClr\bin\Debug\UtilClr.dll

------ Build started: Project: Util, Configuration: Debug Any CPU ------

 Util -> C:\Users\gvarol\Documents\Util\Util\sql\debug\Util.dbschema

------ Deploy started: Project: Util, Configuration: Debug Any CPU ------

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets(120,5): Error MSB4018: The "SqlDeployTask" task failed unexpectedly.

System.ArgumentOutOfRangeException: Index and count must refer to a location within the buffer.

Parameter name: bytes

  at System.Text.DecoderNLS.GetCharCount(Byte[] bytes, Int32 index, Int32 count, Boolean flush)

  at System.Text.DecoderNLS.GetCharCount(Byte[] bytes, Int32 index, Int32 count)

  at Microsoft.Data.Schema.Sql.SchemaModel.ClrAssemblyReader.ClrAssemblyMetaData.GetStringFromStringStream(Int32 offset)

  at Microsoft.Data.Schema.Sql.SchemaModel.ClrAssemblyReader.ClrAssemblyMetaData..ctor(BinaryReader binaryReader)

  at Microsoft.Data.Schema.Sql.SchemaModel.ClrAssemblyReader.ClrAssemblyMetaDataCache.Add(ISql90Assembly assembly)

  at Microsoft.Data.Schema.Sql.Build.SqlDeploymentPlanGenerator.AssemblyAnalyzer.GetDifferenceOperations(ISql90Assembly sourceAssembly, ISql90Assembly targetAssembly)

  at Microsoft.Data.Schema.Sql.Build.SqlDeploymentPlanGenerator.AssemblyAnalyzer.AnalyzeAlter(IModelElement sourceElement, ModelComparisonChangeDefinition change)

  at Microsoft.Data.Schema.Sql.Build.SqlDeploymentPlanGenerator.PlanMediator.CollectChangesFromAlters(Dictionary`2 proposedChanges)

  at Microsoft.Data.Schema.Sql.Build.SqlDeploymentPlanGenerator.PlanMediator.BuildOperations()

  at Microsoft.Data.Schema.Sql.Build.SqlDeploymentPlanGenerator.OnGeneratePlan()

  at Microsoft.Data.Schema.Build.DeploymentPlanGenerator.GeneratePlan()

  at Microsoft.Data.Schema.Build.SchemaDeployment.BuildPlan()

  at Microsoft.Data.Schema.Build.SchemaDeployment.Execute(Boolean executeDeployment)

  at Microsoft.Data.Schema.Build.SchemaDeployment.Execute()

  at Microsoft.Data.Schema.Tasks.DBDeployTask.Execute()

  at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()

  at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggingContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean& taskResult)

  Done executing task "SqlDeployTask" -- FAILED.

 Done building target "DspDeploy" in project "Util.dbproj" -- FAILED.

Done executing task "CallTarget" -- FAILED.

Done building target "DBDeploy" in project "Util.dbproj" -- FAILED.

Done building project "Util.dbproj" -- FAILED.

Build FAILED.

========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

Please ignore my issue above, it was related to some other object in my database, dropping clr objects and the assembly once from the database solved it.

If you use the command line (msbuild foo.csproj /T:deploy) you get a hint indicating that it might be a .NET Runtime version issue:

SqlClrDeploy:

 Beginning deployment of assembly MySqlClrProject.dll to server . : dropme

 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.

 Deployment script generated to:

 c:\Users\jdearing\Documents\MySqlClrProject\bin\Debug\MySqlClrProject.sql

If you want to show that message in the output panel of Visual Studio you can tune the verbosity settings. In the Options dialog select the Build and Run settings below the Projects and Solutions node.

For more information see these stackoverflow questions:

http://stackoverflow.com/questions/7634794/how-do-i-deploy-a-clr-stored-procedure-via-the-msbuild-command-line/11441709#11441709

http://stackoverflow.com/questions/7557562/how-do-i-get-the-message-msbuild-task-that-shows-up-in-the-visual-studio-proje