CREATE PROCEDURE gets "Msg 6567, Level 16, State 2" for SQLCLR stored procedure

Question

I have an issue getting SQL Server to create the stored procedure for my CLR function.

This is the error I get:

Msg 6567, Level 16, State 2, Procedure PerfInsert, Line 12 [Batch Start Line 0]
CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, System.Nullable, void.

What am I doing wrong?

(Updated) C# code:

using Microsoft.SqlServer.Server;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;

    namespace MiddleMan
{
    public static class MiddleMan
    {
        [SqlProcedure(Name = "PerfInsert")]
        public static SqlInt32 CreateCommand(SqlChars tblString, SqlChars featureName, SqlChars connectionString, SqlChars perfionConnectionString, SqlChars logFile)
        {
            Process compiler = new Process();
            compiler.StartInfo.FileName = "C:\\SQL Server C# Functions\\PerfionLoader\\PerfionLoader\\bin\\Release\\PerfionLoader.exe";
            compiler.StartInfo.Arguments = tblString.Value + " " + featureName.Value + " " + connectionString.Value + " " + perfionConnectionString.Value + " " + logFile.Value;
            //compiler.StartInfo.UseShellExecute = false;
            //compiler.StartInfo.RedirectStandardOutput = true;
            compiler.Start();
            return SqlInt32.Zero;
        }
    }
}

(Updated) SQL code:

CREATE PROCEDURE PerfInsert
    (
        @tblString nvarchar(max)
        , @featureName nvarchar(max)
        , @connectionString nvarchar(max)
        , @perfionConnectionString nvarchar(max)
        , @logFiel nvarchar(max)
    )
    AS EXTERNAL NAME PerfInsert.[MiddleMan.MiddleMan].[CreateCommand]
GO

OK, you can't use varchar for parameters, they must be nvarchar. Reference — Dale K
– Dale K, Commented Oct 29, 2019 at 21:01
Yeah, changed the variable declarations to nvarchar and still the same error — Doug Coats
– Doug Coats, Commented Oct 29, 2019 at 21:03

Solomon Rutzky · Accepted Answer · 2019-11-27 00:30:23Z

3

You should decorate the method with:

[SqlProcedure()]
public static SqlInt32 ...
{
  ...
}

And, SQLCLR doesn't work with VARCHAR. You will need to change the T-SQL definition to use NVARCHAR(4000). If 4000 isn't enough then you can use MAX, but if input is guaranteed to be no more than 4000 bytes, then 1 - 4000 is more efficient. Might not make a difference here, but good habit to have :-).

Also, is best if you use Sql* types for the input parameters. Meaning, use SqlString and get the value from param.Value.

Related Posts:

System.Web in SQL Server CLR Function (on DBA.StackExchange)
System.Security.SecurityException with CLR Function

UPDATE

O.P. confirmed:

it turns out the issue was I needed to drop the assembly and recreate it. Didn't realize the build version was different.

Meaning, the originally stated error message of "CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, System.Nullable, void." was likely due to an issue that was already resolved in the code itself by the time the question was posted. That error is not the result of a missing SqlProcedure attribute, nor due to specifying VARCHAR as a datatype, so it must be due to something that we never saw.

edited Nov 27, 2019 at 0:30

answered Oct 29, 2019 at 21:09

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

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

6 Comments

Doug Coats Over a year ago

"How does one decorate the method"? Using the link Dale posted previously? learn.microsoft.com/en-us/dotnet/api/…

Solomon Rutzky Over a year ago

Correct. Typing on phone is frustrating. Updated answer. Thanks for pointing that out.

Doug Coats Over a year ago

@SolomonRutzky I have made the suggested changes and am sill getting the same error. Im wondering how much this is a user error issue :/

Doug Coats Over a year ago

Its worth noting that I have to run to an appointment in 15, so I may not respond again until the morning when I can actively work with this.

Solomon Rutzky Over a year ago

Can look more when I get home, but you need SqlString, not SqlChars.

|

David Browne - Microsoft · Accepted Answer · 2019-10-29 21:32:57Z

2

You don't need the SqlProcedureAttribute. That's just metadata to tell SSDT to emit the CREATE PROCEDURE. The stored procedure should use NVARCHAR(MAX) to match System.String. EG:

Compile that C# file into a .dll:

c:\test> C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc /target:library .\proc.cs

then:

create assembly PerfInsert from 'c:\test\proc.dll'
WITH PERMISSION_SET = UNSAFE;  
go
CREATE PROCEDURE PerfInsert
    (@tblString NVARCHAR(max),
     @featureName NVARCHAR(max),
     @connectionString NVARCHAR(max),
     @perfionConnectionString NVARCHAR(max),
     @logFiel NVARCHAR(max)
    )
AS EXTERNAL NAME PerfInsert.[MiddleMan.MiddleMan].[CreateCommand]
GO

answered Oct 29, 2019 at 21:32

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

1 Comment

Solomon Rutzky Over a year ago

Hi David. I finally had time to test all SQLCLR object types and yes, you are correct, the SqlProcedure() attribute is not required. In fact, neither is SqlTrigger() nor SqlFunction() for UDFs. Thanks for pointing that out (+1). In all these years I never once thought to try this ;-). However, I did find that SqlFunction() is required if you need to set the IsDeterministic and/or IsPrecise properties as those are for the Q.O. It's also needed for TVFs to link the FillRowMethod. UDAs and UDTs also require their particular attributes. Take care, Solomon...

Collectives™ on Stack Overflow

CREATE PROCEDURE gets "Msg 6567, Level 16, State 2" for SQLCLR stored procedure

2 Answers 2

UPDATE

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related