具有C#抛出错误的CLR存储过程
我正在使用C#制作CLR存储过程,为此我将通过示例进行学习.
Hi I am working on making a CLR stored procedure using C#, for which I am learning through examples.
下面是我现在正在尝试的
Below is what I am trying now
public static void GetProductsByPrice(int price)
{
SqlConnection connection = new SqlConnection("context connection=true");
connection.Open();
string commandText = "SELECT * FROM Products WHERE PRICE < " + price.ToString();
SqlCommand command = new SqlCommand(commandText, connection);
SqlDataReader reader = command.ExecuteReader();
// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("col1", SqlDbType.NVarChar, 100),
new SqlMetaData("col2", SqlDbType.NVarChar, 100));
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
// Send 10 rows back to the client.
while (reader.Read())
{
// Set values for each column in the row.
record.SetString(0, reader[1].ToString()); //productName
record.SetString(1, reader[2].ToString()); // productDescription
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
}
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
}
但是当我尝试运行它时,出现以下错误
But when I try to run it I get the below errors
消息6549,级别16,状态1,过程GetProductsByPrice,第0行
在执行用户定义的例程或聚合'GetProductsByPrice'期间发生.NET Framework错误:
System.Data.SqlClient.SqlException:SQL Server不支持区域设置标识符(LCID)16393.
System.Data.SqlClient.SqlException:
在Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(布尔值ignoreNonFatalMessages)
在Microsoft.SqlServer.Server.SqlPipe.SendResultsStart(SqlDataRecord记录)
在StoredProcedures.GetProductsByPrice(Int32价格)
用户交易(如果有的话)将被回滚.
我指的是这篇 msdn文章获取代码.
I am referring to this msdn article for the code.
请帮助我.
异常指出: SQL不支持语言环境标识符(LCID)16393
SqlMetaData.LocaleId 属性包含区域设置ID的列或参数,其中默认值是当前线程的当前语言环境.
The SqlMetaData.LocaleId property contains the locale ID of the column or parameter where the default value is the current locale of the current thread.
在这种情况下,默认值为 16393
,它是英语-印度
语言环境(
The default value in this your case 16393
which is the English - India
locale (see table) but it seems your SQL server was installed with a different locale English - United States
因此,您有三个选择:
- 配置/重新安装SQL Server以使用语言环境
英语-印度
- 将当前线程的语言环境更改为SQL Server支持的本地语言
-
在创建
SqlMetaData
时手动指定语言环境:
- Configure/ReInstall your SQL server to use the locale
English - India
- Change the locale of your current thread to a local which is supported by SQL server
Specify the locale manually when creating the
SqlMetaData
:
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("col1", SqlDbType.NVarChar, 1033, SqlCompareOptions.None),
new SqlMetaData("col2", SqlDbType.NVarChar, 1033, SqlCompareOptions.None));
其中1033是英语-美国