SQL Server 6.5 与西班牙语字母 ú 发生死锁和ü

发布于 2024-11-02 06:17:25 字数 996 浏览 2 评论 0原文

我们通过 ADO 运行 SQL 6.5，并且遇到了最奇怪的问题。

这句话就会开始产生死锁，

insert clinical_notes ( NOTE_ID, CLIENT, MBR_ID, EPISODE, NOTE_DATE_TIME,  
NOTE_TEXT, DEI, CARE_MGR, RELATED_EVT_ID, SERIES, EAP_CASE, TRIAGE, CATEGORY,  
APPOINTMENT, PROVIDER_ID, PROVIDER_NAME )  
VALUES ( 'NTPR3178042', 'HUMANA/PR', '999999999_001', 'EPPR915347',  
'03-28-2011 11:25', 'We use á, é, í, ó, ú and ü (this is the least one we   
use, but there''s a few words with it, like the city: Mayagüez).', 'APK', 'APK', 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )

触发器是字符ú和ü。如果它们位于 NOTE_TEXT 列中。 NOTE_TEXT 是文本列。上有索引

UNC_not_id
NT_CT_MBR_NDX
NT_REL_EVT_NDX
NT_SERIES_NDX
idx_clinical_notes_date_time
nt_ep_idx

NOTE_ID是主键。

发生的情况是，在我们发出此语句后，如果我们发出相同的语句，但使用新的 NOTE_ID 值，则会出现死锁。

如前所述，只有当 ú 或 ü 位于 NOTE_TEXT 中时才会发生这种情况。

这是一台测试服务器，发生错误时通常只有一个会话访问该表。

我确信它与角色设置等有关，但对于我的生活我无法解决。

原文

We're running SQL 6.5 though ADO and we have the oddest problem.

This sentence will start generating deadlocks

insert clinical_notes ( NOTE_ID, CLIENT, MBR_ID, EPISODE, NOTE_DATE_TIME,  
NOTE_TEXT, DEI, CARE_MGR, RELATED_EVT_ID, SERIES, EAP_CASE, TRIAGE, CATEGORY,  
APPOINTMENT, PROVIDER_ID, PROVIDER_NAME )  
VALUES ( 'NTPR3178042', 'HUMANA/PR', '999999999_001', 'EPPR915347',  
'03-28-2011 11:25', 'We use á, é, í, ó, ú and ü (this is the least one we   
use, but there''s a few words with it, like the city: Mayagüez).', 'APK', 'APK', 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )

The trigger are the characters ú and ü. If they are in the NOTE_TEXT column.
NOTE_TEXT is a text column.
There are indexes on

UNC_not_id
NT_CT_MBR_NDX
NT_REL_EVT_NDX
NT_SERIES_NDX
idx_clinical_notes_date_time
nt_ep_idx

NOTE_ID is the primary key.

What happens is after we issue this statement, if we issue an identical one, but with a new NOTE_ID value, we receive the deadlock.

As mentioned, this only happens when ú or ü is in NOTE_TEXT.

This is a test server and there is generally only one session accessing this table when the error occurs.

I'm sure it has something to so with character sets and such, but for the life of me I can't work it out.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风蛊 2024-11-09 06:17:25

列是基于 (var)char 的还是基于 n(var)char 的？使用 unicode 的值是高于 255 还是 ascii 255 或更低（250 和 252）？
尝试将列更改为二进制排序规则，只是为了看看这是否有帮助（它可能会阐明问题）。我不知道这在 SQL 2000 中是否有效（尽管我可以在星期一检查），但是您可以尝试此操作来查找您的服务器上可用的排序规则：
```
SELECT * FROM ::fn_helpcollations()
```
拉丁文通用 BIN 应该在某处。
假设您找到了一个排序规则来尝试，您可以像这样更改排序规则：
```
ALTER TABLE 表名 ALTER COLUMN 列名 varchar(8000) NOT NULL COLLATE Collation_Name_Here
```
编写表格脚本以了解它现在使用的排序规则，以便在不起作用或导致问题时可以将其设置回来。或者使用备份。 :)

另外需要注意的是，如果您使用 unicode，则在文字字符串之前确实需要一个 N，例如：

SELECT N'String'

Is the column (var)char-based or n(var)char-based? Are the values using unicode above 255 or are they ascii 255 or below (250 and 252)?
Try changing the column to a binary collation, just to see if that helps (it may shed light on the issue). I do NOT know if this works in SQL 2000 (though I can check on Monday), but you can try this to find what collations are available on your server:
```
SELECT * FROM ::fn_helpcollations()
```
Latin General BIN should be in there somewhere.
Assuming you find a collation to try, you change the collation like so:
```
ALTER TABLE TableName ALTER COLUMN ColumnName varchar(8000) NOT NULL COLLATE Collation_Name_Here
```
Script out your table to learn the collation it's using now so you can set it back if that doesn't work or causes problems. Or use a backup. :)

One additional note is that if you're using unicode you do need an N before literal strings, for example: