VS SQLCLR: Function X has unresolved reference to schema Y

Question

I am creating a new SQL CLR using Visual Studio 2013 and in the Project Properties have set the Default Schema to 'decASM' (was 'dbo'). When I make this change and rebuild the project VS generates a sql file as follows:

--------------------------------------------------------------------------------
--     This code was generated by a tool.
--
--     Changes to this file may cause incorrect behavior and will be lost if
--     the code is regenerated.
--------------------------------------------------------------------------------

CREATE FUNCTION [decASM].[ExecFoxPro_SayHello] (@name [nvarchar](MAX))
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [dcFoxProAssy].[UserDefinedFunctions].[ExecFoxPro_SayHello];

GO

CREATE FUNCTION [decASM].[GetAllowedPaths] (@serviceUrl [nvarchar](MAX))
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [dcFoxProAssy].[UserDefinedFunctions].[GetAllowedPaths];

GO

CREATE FUNCTION [decASM].[GetTableRowCount] (@serviceUrl [nvarchar](MAX), @foxProPath [nvarchar](MAX), @tableName [nvarchar](MAX))
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [dcFoxProAssy].[UserDefinedFunctions].[GetTableRowCount];

GO

There is an error with each CREATE FUNCTION call of:

Error   1   SQL71501: Function: [decASM].[ExecFoxPro_SayHello] has an unresolved reference to Schema [decASM].  
Error   2   SQL71501: Function: [decASM].[GetAllowedPaths] has an unresolved reference to Schema [decASM].  
Error   3   SQL71501: Function: [decASM].[GetTableRowCount] has an unresolved reference to Schema [decASM].

If I change the Default Schema back to 'dbo', the project builds successfully. I have searched through the project properties and Google but cannot find any mention of how to add a reference to 'decASM'.

Answer 1

You need to create the Schema as well, as a separate SSDT object. It will not be automatically created for you just by specifying that you want to use it for your SQLCLR objects. You should be able to:

• Add New Item (Control + Shift + A) anywhere to your Project
• Select the template SQL Server > Security > Schema
• Name the Item / file: decASM
• Save and close the script

It will create a separate SQL file in your project for this, containing a single command CREATE SCHEMA [decASM], and will deploy it when you publish your SQLCLR code.

The steps noted above did work for me using Visual Studio 2013.