3

My requirement is to create concatenate function so I am trying to create Aggregate function as shown

CREATE AGGREGATE [dbo].[Concatenate]
    (@value [NVARCHAR](MAX))
RETURNS[NVARCHAR](MAX)
EXTERNAL NAME [Test].[Test_SQL.Concatenate]
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Concatenate.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'Concatenate'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'28' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'Concatenate'
GO

But it showing error as:

Assembly 'Test' was not found in the SQL catalog of database 'Test'.

How to create Assembly.

1 Answer 1

1

You must compile your CLR code as a library (.dll) using the .NET build tools or Visual Studio to create the assembly. See the MSDN article on Deploying CLR Database Objects for details. Assuming your source file is Concatenate.vb, you can compile from a command prompt (set your PATH to include the appropriate .NET Framework directory or cd to it):

vbc /target:library /out:Test.dll C:\Concatenate.vb

After compiling the library, you must notify SQL Server of the assembly's existence using CREATE ASSEMBLY. This step will resolve the error you are seeing by adding the assembly to the database catalog. Copy the DLL to the database server and execute T-SQL similar to the following:

CREATE ASSEMBLY Test from 'C:\Test.dll' WITH PERMISSION_SET = SAFE;

After these steps are complete, you can access the functions defined in an assembly using the CREATE AGGREGATE syntax.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.