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'.

Copyright

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.

Bev

do i have to run the create schema script in the database and then import the database back into the SSDT project? – Jasmin

@user1490835 IF your actual question was about Schema's then I would say that you don't need to create it in the DB first. But since you are actually asking about something related to SQLCLR, I will say that your issue has absolutely nothing to do with Schemas. I will address this on your posted question. – Bev

Answer 2

You will have to set the Build Action to Build as well. Otherwise the error will keep showing.

In the following screenshot, Selective.sql is the Schema object I added to my project. Hit F4 for the properties window.

Shan