CODEWITHSUNDEEP
c#sql-serverstored-proceduressqlclr
Doug Coats
Doug Coats

Reputation: 7107

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

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

Upvotes: 2

Views: 1510

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

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:

  1. System.Web in SQL Server CLR Function (on DBA.StackExchange)
  2. 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.

Upvotes: 3

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89121

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

Upvotes: 2

Related Questions