Possible to use external SQLCLR dll assembly?

[jasson281]

Hello,

I am currently working on migrating our sqlproj to a csproj format using this SDK.

However I have some functions that call an external SQLCLR dll. I am wondering if it is possible to reference these somehow and have the project build?

CREATE FUNCTION get_timezones()
RETURNS TABLE
(
    [Name] NVARCHAR(128) NULL
  , [Id] NVARCHAR(128)  NULL
  , [Offset] NVARCHAR(255) NULL
)
AS EXTERNAL NAME [MyAssembly].[TimeZone].[GetTimezones]
GO

I tried adding them as normal assembly references but I receive errors saying the project still has missing references error SQL71501: SqlMultiStatementTableValuedFunction: [get_timezones] has an unresolved reference to SqlAssembly [MyAssembly].

I happen to have the source code for it as well and tried adding them as Project reference but I get the following errors:

Unhandled exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
2> ---> System.ArgumentException: Invalid filetype .dll, was expecting .dacpac (Parameter 'referenceFile')
2>   at MSBuild.Sdk.SqlProj.DacpacTool.PackageBuilder.ValidateReference(String referenceFile) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/DacpacTool/PackageBuilder.cs:line 60
2>   at MSBuild.Sdk.SqlProj.DacpacTool.PackageBuilder.AddExternalReference(String referenceFile, String externalParts) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/DacpacTool/PackageBuilder.cs:line 42
2>   at MSBuild.Sdk.SqlProj.DacpacTool.Program.BuildDacpac(BuildOptions options) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/DacpacTool/Program.cs:line 109

In a normal .sqlproj I was adding these as follows

<ItemGroup>
    <ProjectReference Include="..\..\Core\MyAssembly\MyAssembly.csproj">
      <Name>MyAssembly</Name>
      <Project>{c177b1fb-ed03-4762-be08-5872888916be}</Project>
      <Private>True</Private>
      <IsModelAware>True</IsModelAware>
      <GenerateSqlClrDdl>False</GenerateSqlClrDdl>
      <SqlAssemblyName>MyAssembly</SqlAssemblyName>
      <SqlOwnerName>dbo</SqlOwnerName>
      <SqlPermissionSet>UNSAFE</SqlPermissionSet>
    </ProjectReference>
  </ItemGroup>

Below is my .csproj

<Project Sdk="MSBuild.Sdk.SqlProj/1.17.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>Sql130</SqlServerVersion>
        <!-- For additional properties that can be set here, please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'"><!-- Refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#publishing-support for supported publishing options -->
    
      <OutputPath>bin\</OutputPath>
    </PropertyGroup>

    <ItemGroup>
    <!--Files inside the Post-Deployment and Pre-Deployment folder don't get built-->
      <PostDeploy Include="Post-Deployment\Script.PostDeployment.sql" />
      <PreDeploy Include="Pre-Deployment\Script.PreDeployment.sql" />
    </ItemGroup>

    <ItemGroup>
      <PackageReference Include="master" Version="130.0.0" />
      <PackageReference Include="msdb" Version="130.0.0" />
   </ItemGroup>

    <ItemGroup>
      <Reference Include="MyAssembly">
        <HintPath>..\..\..\Bin\Database\my_db\Release\MyAssembly.dll</HintPath>
    </ItemGroup>

</Project>

Note: Changed actual paths and assembly name to MyAssemly to avoid sharing sensitive data

[jmezach]

@jasson281 Unfortunately we currently have no support for SQLCLR at all. It's not that it can't be done, it just hasn't become a priority for us yet since we aren't using it. To be honest I don't think SQLCLR is a good idea anymore, especially for new projects, but that's just my personal opinion. If someone is willing to contribute support for SQLCLR that would be great, but I don't think we will get to it anytime soon.

[ErikEJ]

SQLCLR is legacy .NET Framework API, and we have no plans to support it.