As part of our effort to upgrade from Visual Studio 2010 to 2013, I am considering several SQL Server CLR stored procedures that require EXTERNAL access, and are isolated in a separate assembly for this purpose. I have almost everything that can be built with little difficulty, but it gives me some problems.
Since this assembly uses web links, which, apparently, the new type of .sqlproj project does not like at .sqlproj , and to continue, we decided to simply build this assembly separately using VS2010 SP1 and reference the compiled DLL. The assembly itself was built just fine, and VS2013 has no complaints about the link itself.
Here's what the SQL CLR SP looks like (please don't ask me what's on Earth, what the semicolon does there at the end):
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.IO; using System.Text; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static int SQLCLR1( SqlString in1, SqlString in2, SqlString in3, SqlString in4, SqlString in5 ) {
And the SQL side:
CREATE PROCEDURE [dbo].[SQLCLR1] @in1 [nvarchar](4000), @in2 [nvarchar](4000), @in3 [nvarchar](4000), @in4 [nvarchar](4000), @in5 [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [External].[StoredProcedures].[SQLCLR1] GO
AaronLS response gives the AS EXTERNAL NAME specifier format as:
AS EXTERNAL NAME [AssemblyName].[ClassName].[FunctionName]
The database project refers to the assembly as External.dll , and in the properties of the VS2010 project in the application "Assembly Name" - "External". On the VS2013 side, Solution Explorer displays the link as just “External”, and it is mirrored in the “Properties” window under “SqlServer → Assembly Name”.
However, when I try to create a database project from the side of VS2013, it spits out errors similar (one for each CLR SP):
12>D:\Source\...\SQLCLR1.proc.sql(9,16): Error: SQL71501: Procedure: [dbo].[SQLCLR1] has an unresolved reference to Assembly [External].
What is missing for a successful build?