BUG: Update of Text Column Using a JOIN in the UPDATE May Cause an Access Violation (283766)


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q283766
BUG #: 19066 (SQLBUG_6.5)

SYMPTOMS

When you try to update a Text column in an update that uses a JOIN you may encounter an exception access violation (AV). The AV seems to be reproducible with specific data or a combination of data and other factors.

WORKAROUND

The access violation does not occur when:
  • The Transact-SQL code is not executed as part of a stored procedure.
  • The EXECUTE() command is used around the UPDATE. For example:
    execute ('
 UPDATE MyTable
 SET MyTable.Col1 = v.Col1
 FROM MyTable
  INNER JOIN
 Table2 v  ON MyTable.Col2 = v.Col2
')
  • You build an index (clustered or nonclustered) on the join column.
  • You use CONVERT(varchar, ...). For example:
    UPDATE MyTable
 SET MyTable.Col1 = CONVERT(VARCHAR(255), v.Col1)
 FROM MyTable
  INNER JOIN
 Table2 v  ON MyTable.Col2 = v.Col2
    NOTE: When converting to a varchar data type, some of the data may be truncated. Therefore, this may not be appropriate for all situations.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5.

MORE INFORMATION

You can see a stack trace in the SQL error log similar to the one that follows when you run Microsoft SQL Server 6.5 with Service Pack 5a (SP5a) installed:

2001/01/02 10:49:50.03 spid11   EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump
2001/01/02 10:49:50.04 spid11   ***BEGIN STACK TRACE***
2001/01/02 10:49:50.04 spid11   0x004EE815 in SQLSERVR.EXE, updtext () + 0x01C5
2001/01/02 10:49:50.06 spid11   0x0046FAA4 in SQLSERVR.EXE, run_288604s_2934en_22e_759420er () + 0x00B4
2001/01/02 10:49:50.06 spid11   0x004118A8 in SQLSERVR.EXE, exec_eop () + 0x0388
2001/01/02 10:49:50.06 spid11   0x00411BA7 in SQLSERVR.EXE, execute () + 0x0687
2001/01/02 10:49:50.06 spid11   0x0040C163 in SQLSERVR.EXE, s_execute_1147c_73e_190420820er () + 0x0083
2001/01/02 10:49:50.06 spid11   0x0040D6E9 in SQLSERVR.EXE, sequencer () + 0x0049
2001/01/02 10:49:50.06 spid11   0x00417F13 in SQLSERVR.EXE, execproc () + 0x0233
2001/01/02 10:49:50.07 spid11   0x0040C201 in SQLSERVR.EXE, s_execute_1147c_73e_190420820er () + 0x0121
2001/01/02 10:49:50.07 spid11   0x0040D6E9 in SQLSERVR.EXE, sequencer () + 0x0049
2001/01/02 10:49:50.07 spid11   0x0042BA5A in SQLSERVR.EXE, language_exec () + 0x011A
2001/01/02 10:49:50.07 spid11   0x0024134D in opends60.dll, srv_post_handle () + 0x0000
2001/01/02 10:49:50.07 spid11   0x002408BB in opends60.dll, srv_post_handle () + 0x0000
2001/01/02 10:49:50.07 spid11   0x00231569 in opends60.dll, Ordinal65 () + 0x0000
2001/01/02 10:49:50.07 spid11   0x00231384 in opends60.dll, Ordinal65 () + 0x0000
2001/01/02 10:49:50.07 spid11   0x10219D84 in MSVCRT40.dll
2001/01/02 10:49:50.07 spid11   0x77F04EE8 in KERNEL32.dll, lstrcmpiW () + 0x0000
2001/01/02 10:49:50.07 spid11   ***END STACK TRACE***
2001/01/02 10:49:50.62 kernel   The current contents of process' input buffer are 'MyStoredProc  '.


Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB283766
©2003 Microsoft Corporation. All rights reserved.