SQL CLR issue: CREATE PROCEDURE failed because parameter counts do not match

Question

I did check this but the problem doesn't appears to be with mismatch of type.

I'm trying to use CLR to convert back a file from blob data. Below is the c# code converted to dll and stored using assembly.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;


namespace CLRProcedures
{
   public class WriteFile
    {
        [SqlFunction]
        public static SqlInt32 WriteToFile(SqlBytes binary, SqlString path, SqlBoolean append)
        {
            try
            {
                if (!binary.IsNull && !path.IsNull && !append.IsNull)
                {
                    var dir = Path.GetDirectoryName(path.Value);
                    if (!Directory.Exists(dir))
                        Directory.CreateDirectory(dir);
                    using (var fs = new FileStream(path.Value, append ? FileMode.Append : FileMode.OpenOrCreate))
                    {
                        byte[] byteArr = binary.Value;
                        for (int i = 0; i < byteArr.Length; i++)
                        {
                            fs.WriteByte(byteArr[i]);
                        };
                    }
                    return 1;
                }
                else
                   return 0;
            }
            catch (Exception ex)
            {
                return -2;
            }
        }
    }
}

SQL queries are as below :

CREATE ASSEMBLY FileOutput from 'c:\dlls\CLRProcedures.dll' WITH PERMISSION_SET = SAFE 

CREATE PROCEDURE FileOutput
@file varbinary(max),
@filepath nvarchar(4000),
@append bit,
@message int OUTPUT    
AS    
EXTERNAL NAME FileOutput.[CLRProcedures.WriteFile].WriteToFile

This throws error : 'CREATE PROCEDURE failed because parameter counts do not match.'

I've re-verified from here if there is type mismatch but there isnt one. Where could I be going wrong ? I tried to change the return type of c# code but same issue.

Thank you

Does it work if you remove the @message int OUTPUT parameter? — mjwills
– mjwills, Commented Jul 8, 2020 at 14:17
output is not the same as return value; output is kinda like ref in .NET — Marc Gravell
– Marc Gravell, Commented Jul 8, 2020 at 14:18
learn.microsoft.com/en-us/sql/database-engine/dev-guide/… shows how to use an OUTPUT parameter if you really want to do it that way. — mjwills
– mjwills, Commented Jul 8, 2020 at 14:19
What datatype should a parameter named "message" be? Doesn't sound like numeric to me. — SMor
– SMor, Commented Jul 8, 2020 at 14:22

Solomon Rutzky · Accepted Answer · 2020-07-08 14:51:51Z

1

You are ignoring what the error message is saying:

CREATE PROCEDURE failed because parameter counts do not match (emphasis added)

The parameter "count", not any particular parameter "type", is the issue. You have 3 input parameters in the method signature but are specifying 4 parameters in the CREATE PROCEDURE statement.

Your options are:

Do as Naji is suggesting and add a 4th parameter, declared as out in the C# code, and change the return to setting that message variable, which would match the 4 parameters you have defined.
Or, you can keep the C# code as-is and get that return value the same way that you would a regular T-SQL stored proc return value:
```
DECLARE @ReturnValue INT;

EXEC @ReturnValue = dbo.FileOutput 0x1234, N'path', 1;

SELECT @ReturnValue;
```
Or, you can even switch to having this be a function by simply changing the T-SQL CREATE object statement to be:
```
CREATE FUNCTION dbo.FileOutput
(
  @File VARBINARY(MAX),
  @FilePath NVARCHAR(4000),
  @Append BIT
)
RETURNS INT
AS EXTERNAL NAME ...
```
And use as follows:
```
DECLARE @ReturnValue INT;

SET @ReturnValue = dbo.FileOutput(0x1234, N'path', 1);

SELECT @ReturnValue;
```
(Technically, as a scalar function that returns an INT / Int32, you can even execute it using the same stored procedure syntax as shown in option 2 above)

ALSO:

If you are creating a procedure, you should decorate the C# WriteToFile method with SqlProcedure instead of SqlFunction.
Rather than iterating over the entire binary value as a byte[], the SqlBytes type offers a Stream property that is an actual stream that is a more efficient way of reading that value. Just copy binary.Stream to the file stream and be done :-)
Always specify a schema name (for schema-bound objects), whether in CREATE statements, SELECT, EXEC, etc.

For more info on working with SQLCLR in general, please visit: SQLCLR Info

edited Jul 8, 2020 at 14:51

answered Jul 8, 2020 at 14:34

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

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

4 Comments

Shalini Raj Over a year ago

thank you fo explaining what was actually going on. i've still not been able to export file due to Path.GetDirectoryName throws error 'Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed'. Google search suggests to set permission mode to UNSAFE while creating assembly. When I try to do so that throws error that 'Assembly 'CLRProcedures' cannot be loaded because this edition of SQL Server only supports SAFE assemblies.' which means its linux hosted sql server ??

Shalini Raj Over a year ago

previously I was trying to create the file back from blob data via 'OLE automation procedures' but enabling it is not possible as it not being suggested by higher authorities.[even when i tried to enable, it said it cannot be done on this version of sql server]. Now i'm told to try via bcp but have no idea how to approach

Solomon Rutzky Over a year ago

@ShaliniRaj Please update the main question with that additional info: a) what you are actually trying to accomplish (the end goal, not just this intermediate step of exporting a file), and b) what version/platform of SQL Server you are using. Thanks. That will help readers in general get a better understanding of what is going on, but it sounds like the actual question has been resolved/answered and you are now moving onto another question.

Solomon Rutzky Over a year ago

@ShaliniRaj I can say that if you are running on Linux then that won't be able to do file system stuff. Either way, UNSAFE is bad advice as it's unnecessary. EXTERNAL_ACCESS would handle that. Please see: Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) and SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1.

NajiMakhoul · Accepted Answer · 2020-07-08 14:29:16Z

0

You need to add the OUTPUT parameter to the WriteToFile function to return value

[SqlFunction]
public static void WriteToFile(SqlBytes binary, SqlString path, SqlBoolean append,out SqlInt32 message)
    {
        try
        {
            if (!binary.IsNull && !path.IsNull && !append.IsNull)
            {
                var dir = Path.GetDirectoryName(path.Value);
                if (!Directory.Exists(dir))
                    Directory.CreateDirectory(dir);
                using (var fs = new FileStream(path.Value, append ? FileMode.Append : FileMode.OpenOrCreate))
                {
                    byte[] byteArr = binary.Value;
                    for (int i = 0; i < byteArr.Length; i++)
                    {
                        fs.WriteByte(byteArr[i]);
                    };
                }
                message = 1;
            }
            else
                message = 0;
        }
        catch (Exception ex)
        {
            message = -2;
        }
    }

for more details : CLR Stored Procedures

edited Jul 8, 2020 at 14:29

answered Jul 8, 2020 at 14:18

NajiMakhoul

1,7362 gold badges18 silver badges32 bronze badges

7 Comments

Marc Gravell Over a year ago

I suspect this is going in the wrong direction - and the real "fix" is to remove the parameter from the SP declaration (using the return value instead)

NajiMakhoul Over a year ago

@MarcGravell why ? he can add the missing out parameter to the method and return the message value

allmhuran Over a year ago

@NajiMakhoul He could, but that parameter is being used to indicate the success/failure state, which is an idiomatic use of the return value of a stored procedure

Shalini Raj Over a year ago

thank you fo explaining what was actually going on. i've still not been able to export file due to Path.GetDirectoryName throws error 'Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed'. Google search suggests to set permission mode to UNSAFE while creating assembly. When I try to do so that throws error that 'Assembly 'CLRProcedures' cannot be loaded because this edition of SQL Server only supports SAFE assemblies.' which means its linux hosted sql server ??

Shalini Raj Over a year ago

previously I was trying to create the file back from blob data via 'OLE automation procedures' but enabling it is not possible as it not being suggested by higher authorities.[even when i tried to enable, it said it cannot be done on this version of sql server]. Now i'm told to try via bcp but have no idea how to approach –

|

Collectives™ on Stack Overflow

SQL CLR issue: CREATE PROCEDURE failed because parameter counts do not match

2 Answers 2

4 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related