Problem with updating from TEXT field into TEXT field 
Author Message
 Problem with updating from TEXT field into TEXT field

Hi, I have this query

UPDATE wkt2686
SET extra_text = memo
FROM payment_test p
WHERE wkt2686.payment_pointer = p.payment_pointer
AND type = 'E'
AND wkt2686.payment_pointer != 0

Where columns extra_text and memo are both TEXT datatypes.

This script crashes with Database connection failure DBProcess dead.  Howver
if I amend the script as follows it works fine.  Any ideas ?

UPDATE wkt2686
SET extra_text = CONVERT (TEXT,memo)
FROM payment_test p
WHERE wkt2686.payment_pointer = p.payment_pointer
AND type = 'E'
AND wkt2686.payment_pointer != 0

The Server is Intel Pentium NT4 SP3 and SQL 6.5 SP4

Regards

Jeff Tulley
MATRIX



Mon, 09 Apr 2001 03:00:00 GMT
 Problem with updating from TEXT field into TEXT field

Convert does not support user define datatype.
Could that be the case?

I have no problem sampling at pubs..pr_info table text field.

William Zhou


Quote:

>Hi, I have this query

>UPDATE wkt2686
>SET extra_text = memo
>FROM payment_test p
>WHERE wkt2686.payment_pointer = p.payment_pointer
>AND type = 'E'
>AND wkt2686.payment_pointer != 0

>Where columns extra_text and memo are both TEXT datatypes.

>This script crashes with Database connection failure DBProcess dead.
Howver
>if I amend the script as follows it works fine.  Any ideas ?

>UPDATE wkt2686
>SET extra_text = CONVERT (TEXT,memo)
>FROM payment_test p
>WHERE wkt2686.payment_pointer = p.payment_pointer
>AND type = 'E'
>AND wkt2686.payment_pointer != 0

>The Server is Intel Pentium NT4 SP3 and SQL 6.5 SP4

>Regards

>Jeff Tulley
>MATRIX




Tue, 10 Apr 2001 03:00:00 GMT
 Problem with updating from TEXT field into TEXT field
Sorry,  I said It works ONLY if I specify the CONVERT (TEXT, column_name)
and fails without, despite the fact that I'm updating a TEXT from a TEXT.

The exact failure message is ....

This command did not return data, and it did not return any rows

DB-Library Process Dead - Connection Broken

and the SQL Server error log shows.....

98/10/23 09:27:26.23 spid15   EXCEPTION_ACCESS_VIOLATION raised, attempting
to create symptom dump
98/10/23 09:27:26.25 spid15   ***BEGIN STACK TRACE***
98/10/23 09:27:26.25 spid15   0x004EA8E7 in SQLSERVR.EXE, a_norm_qexp() +
0x0077
98/10/23 09:27:26.25 spid15   0x0046ABD3 in SQLSERVR.EXE, exec_constraint()
+ 0x0123
98/10/23 09:27:26.25 spid15   0x0040DC0A in SQLSERVR.EXE, mirrorproc() +
0x001A
98/10/23 09:27:26.25 spid15   0x0040DEEF in SQLSERVR.EXE, mirrorproc() +
0x02FF
98/10/23 09:27:26.25 spid15   0x0040886C in SQLSERVR.EXE,
CDTCTransaction::recoverTran() + 0x014C
98/10/23 09:27:26.25 spid15   0x00409829 in SQLSERVR.EXE, opencheck() +
0x0089
98/10/23 09:27:26.25 spid15   0x00427B09 in SQLSERVR.EXE, tbswritecheck() +
0x0969
98/10/23 09:27:26.25 spid15   0x00250FED in opends60.dll
98/10/23 09:27:26.25 spid15   0x0025055B in opends60.dll
98/10/23 09:27:26.25 spid15   0x002414D1 in opends60.dll
98/10/23 09:27:26.25 spid15   0x00241384 in opends60.dll
98/10/23 09:27:26.25 spid15   0x10219D84 in MSVCRT40.dll
98/10/23 09:27:26.25 spid15   0x77F04F4A in KERNEL32.dll
98/10/23 09:27:26.25 spid15   ***END STACK TRACE***
98/10/23 09:27:26.79 kernel   The current contents of process' input buffer
are 'UPDATE wkt2686

     SET extra_text = memo
     FROM payment p
     WHERE wkt2686.payment_pointer = p.payment_pointer
     AND type = 'E'
     AND wkt2686.payment_pointer != 0'.
98/10/23 09:27:26.79 spid15   Symptom dump created:
C:\MSSQL\log\SQL91889.DMP
98/10/23 09:27:26.79 kernel   The current contents of process' input buffer
are 'UPDATE wkt2686



Tue, 10 Apr 2001 03:00:00 GMT
 Problem with updating from TEXT field into TEXT field
Time to call MS support about all the AV staff.


Quote:
>Sorry,  I said It works ONLY if I specify the CONVERT (TEXT, column_name)
>and fails without, despite the fact that I'm updating a TEXT from a TEXT.

>The exact failure message is ....

>This command did not return data, and it did not return any rows

>DB-Library Process Dead - Connection Broken

>and the SQL Server error log shows.....

>98/10/23 09:27:26.23 spid15   EXCEPTION_ACCESS_VIOLATION raised, attempting
>to create symptom dump
>98/10/23 09:27:26.25 spid15   ***BEGIN STACK TRACE***
>98/10/23 09:27:26.25 spid15   0x004EA8E7 in SQLSERVR.EXE, a_norm_qexp() +
>0x0077
>98/10/23 09:27:26.25 spid15   0x0046ABD3 in SQLSERVR.EXE, exec_constraint()
>+ 0x0123
>98/10/23 09:27:26.25 spid15   0x0040DC0A in SQLSERVR.EXE, mirrorproc() +
>0x001A
>98/10/23 09:27:26.25 spid15   0x0040DEEF in SQLSERVR.EXE, mirrorproc() +
>0x02FF
>98/10/23 09:27:26.25 spid15   0x0040886C in SQLSERVR.EXE,
>CDTCTransaction::recoverTran() + 0x014C
>98/10/23 09:27:26.25 spid15   0x00409829 in SQLSERVR.EXE, opencheck() +
>0x0089
>98/10/23 09:27:26.25 spid15   0x00427B09 in SQLSERVR.EXE, tbswritecheck() +
>0x0969
>98/10/23 09:27:26.25 spid15   0x00250FED in opends60.dll
>98/10/23 09:27:26.25 spid15   0x0025055B in opends60.dll
>98/10/23 09:27:26.25 spid15   0x002414D1 in opends60.dll
>98/10/23 09:27:26.25 spid15   0x00241384 in opends60.dll
>98/10/23 09:27:26.25 spid15   0x10219D84 in MSVCRT40.dll
>98/10/23 09:27:26.25 spid15   0x77F04F4A in KERNEL32.dll
>98/10/23 09:27:26.25 spid15   ***END STACK TRACE***
>98/10/23 09:27:26.79 kernel   The current contents of process' input buffer
>are 'UPDATE wkt2686

>     SET extra_text = memo
>     FROM payment p
>     WHERE wkt2686.payment_pointer = p.payment_pointer
>     AND type = 'E'
>     AND wkt2686.payment_pointer != 0'.
>98/10/23 09:27:26.79 spid15   Symptom dump created:
>C:\MSSQL\log\SQL91889.DMP
>98/10/23 09:27:26.79 kernel   The current contents of process' input buffer
>are 'UPDATE wkt2686



Tue, 10 Apr 2001 03:00:00 GMT
 Problem with updating from TEXT field into TEXT field
AV staff ?
Quote:

>Time to call MS support about all the AV staff.





Fri, 13 Apr 2001 03:00:00 GMT
 Problem with updating from TEXT field into TEXT field
Jeff,

Q.  I am getting a message 'dbprocess dead or not enabled' from SQL
Server.  I am seeing an 'Exception Access Violation' message in the SQL
errorlog.  I am getting *.DMP files in the <sql>\log directory.  What
is going on?
(v1.2   20.10.1998)

A.  Basically SQL is probably internally gpf'ing/AV'ing (same thing) -
you should see messages to this effect in the SQL errorlog.  There are
only three reasons for this :-

1.  A database corruption - you can check for this with the dbcc
checkdb, newalloc and checkcatalog commands.

2.  A hardware problem - usually duff memory.

3.  A bug in the SQL Server code (this is the most likely cause -
database corruptions rarely cause gpf's, and hardware errors normally
show up in other ways).  This is the Microsoft C code that makes up
SQLSERVR.EXE and dll's, NOT your TSQL code.  If you have SQL code that
causes an AV it is Microsoft's bug, not yours.

Assuming it's not a database corruption, then follow the following
diagnostic process :-

1.  Check the Microsoft Kb on Technet (if you don't have Technet then
order it now!).  Also check the on-line website at
www.microsoft.com/support which is more up to date than Technet.  
Search on kbbug AND AV AND SQL to find all documented AV bugs.  Many
contain workarounds.

2.  Are you on the latest version of SQL Server and the latest service
pack?   MS fix a lot of AV errors in every service pack, so it is
definitely worth getting current.  If you're not on the latest service
pack then that is the first thing MS are going to ask you to do if you
contact them anyway.

3.  Check the SQL errorlog and save away all the messages - especially
anything telling you what SQL was being executed at the time.

4.  Check the \<sql>\LOG directory for SQLxxxx.DMP files that may have
been created.  These contain information on what SQL Server was doing
at the time, module stack traces etc.  Save these away for MS support
as necessary.  (Though there is a PRINTDMP.EXE utility supplied the
output of this is still of no use to anyone unless they have the
SQLServer C source code)

5.  Can you re-create the problem at will?  If the SQL being run is not
shown in the errorlog, then find out what the user/developer was doing
at the time.  Use SQL Trace to capture the actual SQL code being run if
you can.  If you can't recreate it, it's still worth reporting as long
as you have the errorlog(s) and dump file(s).

6.  If you can re-create the problem, then see if you can create a
reproduction script to show the problem.  This needs to be capable of
running on a brand-new install of SQL Server on a new database.  
Therefore it needs to contain all tables, user defined data types,
triggers, views etc. needed to show the problem.  If it needs data then
try and keep this to a minimum.  (If the script/data is reasonably
short then post to one of the
newsgroups and one of MVP's can report it to MS for you).

7.  Can you work around the problem by re-writing the SQL?  Even with a
reproduction script MS are unlikely to turn a fix around quickly -
unless you are a multi-million dollar customer.  And even then you
wouldn't just be applying one small fix, it would be a latest build
with lots of other fixes too - it won't have been regression tested, so
it could cause more damage than it fixed anyway.

8.  Report the problem to MS PSS.  PLEASE do this even if you can
workaround it.  Unless MS get these bug reports then they can't fix
them.  (With a repro script an MVP will do it for you).  Your call fee
WILL be re-imbursed as all calls about bugs are free.  (However, on the
"normal" support-line the person answering the phone can't know it's a
bug, so they'll need your credit card details anyway).

MS will need you to supply :-

    SQL Errorlog(s)
    NT event log(s) - if any NT errors were occuring at the time
    TSQL code running at the time
    Details of hardware, version of NT, servicepacks etc.  WINMSDP
output is good for this.

With SQL 7 there is a new utility that will garner most of this
information for you automatically.  It is called sqldiag -
    sqldiag -U<login> -P<password> -O<output filename>

 Neil Pike MVP/MCSE
 Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated
otherwise)



Fri, 13 Apr 2001 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. SQL Query problem concat text field from mulitple rows into one text field

2. Text Field -> Repeating Calculation (Text) Field

3. Webpage won't show text field but will show calc of text field

4. Update text pattern [0-9] with SQL Server text field

5. My textarea form field will not update my stored proc text field

6. Text justification problems with 4.1 on Windows (fields and text boxes)

7. PROBLEMS INSERTING MEMO FIELDS FROM TABLE FIELD TO TEXT FILE

8. Problem updating text field

9. Update problem in Text field

10. SQL 6.5 - Problem updating a text field

11. Problem w/ UPDATE with Text fields

12. yet another problem updating text fields


 
Powered by phpBB® Forum Software