THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Visual Studio 2010 and SQLCLR: Some Good, Some Bad

This past week I've been trying out Visual Studio 2010 for SQLCLR development. Verdict: A couple of nice things, a couple not so nice.

In the interest of keeping things somewhat positive around here, we'll start with the good stuff:

  • Pre-deployment and post-deployment scripts are built in. This is great, especially if you're working with features such as ordered TVFs, which Visual Studio 2008 never properly supported. In 2010 you can stick the ALTER FUNCTION in a post-deployment script and you'll be all set. This feature will also be useful if you need to sign assemblies or do other deployment-time modifications such as dropping and re-creating an assembly that was created outside the scope of your current project.
  • If you tried creating multiple SQLCLR projects with Visual Studio 2008 you no doubt learned quickly that it would, by default, name every assembly SqlClassLibrary. Thankfully, Visual Studio 2010 names the assembly whatever you named the project--as it should.

And now, the bad:
  • Visual Studio 2010 defaults the targeted .NET Framework version to 4.0. This creates an error when you try to deploy. The error? "Deploy failed". That's it--no other information is returned. If you're hitting this error, right-click on your project, click Properties, and change the "Target framework" setting to .NET 2.0 if you're developing against SQL Server 2005, and .NET 3.5 if you're developing against SQL Server 2008. That will probably fix things up, unless...
  • If you're testing your solution against tempdb, deployment will completely fail. Itzik Ben-Gan discovered this one and the reason it fails is quite interesting: Visual Studio, during the deployment process, tries to enable vardecimal storage at the database level. And that's not allowed in tempdb. As with the Framework version error, this will throw a simple "Deploy failed" and not tell you much else. Unless you turn on Profiler and watch what's happening. Not a good situation, and the only fix I know of at this point in time is to edit the deploy file, with will be named [your project name].sql and will be output in the debug or release bin folder when you try to deploy.
  • Anecdotally, deployment feels a lot slower. I haven't yet spent the time profiling to figure out where the time is being taken, but I can say that after a couple of years of development with Visual Studio 2008 I'm used to deployment taking a few seconds. It's taking up to 45 seconds on 2010, and it's definitely a major annoyance when you need to make and test a lot of small changes.

I'm not sure how the second issue will be impacted by various security settings on databases other than tempdb, but I suspect that we'll see other issues as a result of the attempt to turn on vardecimal. It's also concerning that the Visual Studio team would create a product that will silently enable a database feature, with no warning and, as far as I can tell, no reasoning. If anyone from the VS team is reading this, please reply in the comments and let us know why this decision was made.

All in all: The deployment scripts are a nice plus, but the better default assembly name is counter-balanced by the incorrect default framework version--you still need to remember to go in and tweak things. And while the tempdb limitation isn't a deal breaker, it's incredibly annoying. For now, especially due to the performance issue, I'm thinking I'll stick with Visual Studio 2008.

Hope this helps those of you who are doing SQLCLR development and are planning to upgrade to Visual Studio 2010 sooner rather than later. Please post in the comments here if you find other hurdles so that we can keep a running list of issues.

Published Sunday, May 02, 2010 6:40 PM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Adam

Do you use VS2010 Ultimate 32 bit?

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

May 3, 2010 12:47 AM
 

Andries said:

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!

November 5, 2010 3:00 PM
 

Beata said:

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

January 4, 2011 11:16 AM
 

Rich said:

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!

May 4, 2011 9:59 PM
 

Gokhan Varol said:

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 ==========

December 28, 2011 3:12 PM
 

Gokhan Varol said:

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.

December 29, 2011 5:48 AM
 

Justin Dearing said:

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

July 11, 2012 7:01 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement