无法将字符串作为参数传递给SQL 2005中的CLR存储过程 [英] Cannot pass string as argument to CLR stored procedure in SQL 2005
问题描述
您好,我有从这个C#源编译的简单程序集,它定义了2个存储过程,其中简单地将其参数写入SQL消息输出:
使用System;
使用System.Data ;使用System.Data.SqlTypes;
使用System.Data.SqlClient;
使用Microsoft.SqlServer.Server;
public class testclass
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TestProcInt(SqlInt32 param)
{
SqlContext.Pipe.Send(String .Format(" {0}",param));
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TestProcString(SqlString param)
{< br> SqlContext.Pipe.Send(param.Value);
}
当我尝试在sql server上创建存储过程时,TestProcInt是正确创建的,但是创建TestProcString失败了:
CREATE ASSEMBLY MyFirstUdp FROM'\\tsclient\c\dev\bin\test.dll'
CREATE PROCEDURE TestProcInt(@param int)作为外部名称MyFirstUdp.netupdater。 TestProcInt
...这个工作正常,也执行此程序有效
CREATE PROCEDURE TestProcString(@param text)AS EXTERNAL NAME MyFirstUdp.netupdater.TestProcString
...这会返回错误:
为此创建程序吨; TestProcString"失败,因为参数"@ param"的T-SQL和CLR类型不匹配。
为什么? SQL类型"text"和CLR类型的SqlString应该兼容,当我看到映射http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp时我也试过next和varchar SQL类型,但没有成功
非常感谢
Hello,
I have simple assembly compiled from this C# source, which defines 2 stored procedures, which simple write their argument to SQL message output:
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class testclass
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TestProcInt(SqlInt32 param)
{
SqlContext.Pipe.Send(String.Format("{0}", param));
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TestProcString(SqlString param)
{
SqlContext.Pipe.Send(param.Value);
}
}
when i try to create stored procedure on sql server, TestProcInt is created correctly, but creating TestProcString fails:
CREATE ASSEMBLY MyFirstUdp FROM '\\tsclient\c\dev\bin\test.dll'
CREATE PROCEDURE TestProcInt(@param int) AS EXTERNAL NAME MyFirstUdp.netupdater.TestProcInt
... this works OK, also executing this procedure works
CREATE PROCEDURE TestProcString(@param text) AS EXTERNAL NAME MyFirstUdp.netupdater.TestProcString
...this returns error:
CREATE PROCEDURE for "TestProcString" failed because T-SQL and CLR types for parameter "@param" do not match.
why? SQL type "text" and CLR type SqlString should be compatible, when i see mapping http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
I tried also next and varchar SQL types, but with no success
thanks very much
推荐答案
不确定是否有任何帮助,但是我在SQL Server端使用了数据类型NCHAR和SqlString,它运行正常。
Not sure if it is any help, but I have used the datatype NCHAR on the SQL Server side with SqlString and it works fine.
这篇关于无法将字符串作为参数传递给SQL 2005中的CLR存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!