syntax error in creating a CLR stored procedure

Question

I trying to call my first CLR stored procedure in SQL server 2008. I have successfully compiled the DLL and created the assembly. Now I am trying to Create the stored procedure that references the registered assembly by using the CREATE PROCEDURE statement. Following this tutorial, I am getting the syntax error...

--This line worked. Just including this code just to show what I am doing ...
--create assembly 
--LevenshteinLibrary
--from 'Drive Letter:\Path\LevenshteinLibrary.dll'

create procedure testCLR(@s1 nvarchar(1000), @s2 nvarchar(1000))
as external LevenshteinLibrary.getLevenshteinDistance  --Incorrect syntax near '.'.

What should I do to create this procedure? I don't see anything on the msdn create procedure page explaining the proper syntax for "as external."

The VB.net code looks like this:

Imports System.Math
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Server

Partial Public Class LevenshteinerAndSoundexer

<SqlProcedure()> _
Public Shared Function getLevenshteinDistance(ByVal string1 As String, ByVal String2 As String) As Integer

The documentation you linked to is for SQL 2000, which didn't have CLR procedures. The 2008 documentation shows the correct syntax (which is not what you have right now). — Pondlife
– Pondlife, Commented May 17, 2013 at 17:56

RBarryYoung · Accepted Answer · 2013-05-17 19:41:35Z

3

Does this work for you?

create procedure testCLR(@s1 nvarchar(1000), @s2 nvarchar(1000))
as external NAME LevenshteinLibrary.LevenshteinerAndSoundexer.getLevenshteinDistance

The doc for As External has a three part name, and you only had two parts.

Also, as Pondlife points out, As External needs NAME after it too.

Usually, the Class is a "Partial" class, I'm not sure how yours ended up as not one. I think that you may also need to prefix your VB method with an attribute, like this:

Partial Public Class LevenshteinerAndSoundexer

    <SqlProcedure()> _
    Public Shared Function getLevenshteinDistance(ByVal string1 As String, ByVal String2 As String) As Integer

You might want to look at this article and follow the process that they describe there: http://msdn.microsoft.com/en-us/library/5czye81z(v=vs.80).aspx

edited May 17, 2013 at 19:41

answered May 17, 2013 at 17:56

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pondlife Over a year ago

You also need to have AS EXTERNAL NAME; omitting NAME will give a syntax error.

bernie2436 Over a year ago

@RBarryYoung thanks. it returns an error Could not find Type 'LevenshteinerAndSoundexer' in assembly 'LevenshteinLibrary'.

Matt Arnold Over a year ago

@Pondlife This doesn't work in SQL Server 2019; if you have square brackets around the name, you get this error; if you don't you get an error stating "CREATE PROCEDURE must be the only statement in the batch" even though it is and another about the third . in the CLR namespace being "Incorrect syntax".

Matt Arnold Over a year ago

@Pondlife Correction to my previous comment; I was wrapping square brackets around the entire external name reference; it seems the . has a special meaning for the external name and thus it requires there to only be 2 in the form: MyDatabaseAssemblyReference.[MyNamespace.MyClass].MyMethod. I am still trying to figure this out however as I'm now being told my return types do not match between the C# method and Scalar Function I'm trying to create for it.

Collectives™ on Stack Overflow

syntax error in creating a CLR stored procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related