1

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.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Aaron
  • 85
  • 1
  • 4
  • 2
    You're using a 15-year-old version of SQL Server? I thought I was the last one! – Gabe Apr 18 '11 at 15:58
  • 1
    In case I'm missing something... you did say "SQL Server 6.5" which is older than some folk here? – gbn Apr 18 '11 at 16:46
  • This is for the same database as your [older question](http://stackoverflow.com/questions/4809982/high-order-ascii-chars-in-ms-sql-6-5-insert-using-ado), isn't it? – Dave DuPlantis Apr 18 '11 at 21:20
  • Yes, SQL Server 6.5. If it makes you feel any better, it was only patched to the latest version the other week. I know... It's accessed through a DOS app. That's the main reason it's still at 6.5, because the SQL interface needs some named pipes software that doesn't work with SQL 7.0, so it's sorta stuck there. Part of what we're doing is converting the app to Windows, and once it's there, the DOS app will be retired and the server upgraded, but until then, we're stuck with what we got. That's part of the reason for all the odd questions. Slim picking for such an old version out there. – Aaron Apr 19 '11 at 01:48
  • If you're using named pipes I wouldn't be surprised if some of the non-ascii doesn't contain something interpreted as flow-control. – dkretz May 22 '11 at 04:37

1 Answers1

0

  1. 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)?

  2. 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:

SELECT N'String'
ErikE
  • 48,881
  • 23
  • 151
  • 196
  • Thanks. We'll give that a go and let you know how it turns out. – Aaron May 25 '11 at 17:49
  • It turned out to be 'server issues' and that was all we were able to get from the tech guys. I suppose I'll never know what it was. – Aaron Jul 07 '11 at 01:32