Solved

Problem while using inner join in a cursor

Posted on 1997-12-10
5
742 Views
Last Modified: 2012-06-27
I discovered a strange behaviour in Sybase SQL Server while using cursors with inner joins. If the the field (from the 'inner' table) on the inner join is NULL, and a FETCH is executed on the cursor, the server terminates the program and throws the user out of the server. I suspect that this is a bug in the SQL server because whenever I execute the query only in the SQL server, the query works fine. The problem occrs only when this query is used in CURSOR in a stored procedure.
Below is the description of the problem in details
Thanks.




CURSOR DEFINITION
--------------------------------
DECLARE cur_T4 CURSOR FOR
      SELECT delme21
      FROM delme1,delme2
      WHERE delme12  =* delme22


FETCH cur_T4 into @casp_desc


ERROR MESSAGE
-----------------------------
The SQL Server is terminating this process.
DBPROCESS is dead or not enabled.


DATA in table delme1
--------------------------------

1> select delme11,delme12 from delme1
2> go
 delme11     delme12
 ----------- -----------
 A           ABC
 B           ABC
 C           ABC

DATA in table delme2
--------------------------------
1> select delme21,delme22 from delme2
2> go
 delme21     delme22
 ----------- -----------
 A           NULL
 A           NULL
0
Comment
Question by:osho121097
  • 3
  • 2
5 Comments
 

Author Comment

by:osho121097
ID: 1098127
Adjusted points to 100
0
 
LVL 10

Expert Comment

by:bret
ID: 1098128
Yes, this is clearly a bug.  If you look in the SQL Server
errorlog, you will probably see an "infected with 10" or
"infected with 11" or possibly a timeslice error, followed by a stack trace.  If you send that to Sybase Tech Support, they
may be able to identify a new bug and send you a fix for it,
or at least start the process of debugging the problem.

If you add the stacktrace as a comment, I may be able to identify the bug for you and tell you if it is fixed.  BTW, what version
and ebf level is this on (select @@version will tell you)?
0
 

Author Comment

by:osho121097
ID: 1098129
Hi,
I realised it is a bug. I wanted to get a way of avoiding this without coding anything new.

select @@version gives this:
SQL Server/11.0.2.1/P/Sun_svr4/OS 5.4/EBF 6717/OPT/Tue Oct 22 03:48:49 PDT 1996.
Thanks a lot for taking the trouble of answering my question. Please update me if you hear anything more on this issue.

_Anurag.
to_anurag@hotmail.com

Below is a part of the  stack trace:

00:97/12/08 12:22:21.74 kernel  SQL causing error : ins_wphl7

00:97/12/08 12:22:21.74 kernel  curdb = 8 pstat = 0x10010000 lasterror = 0
00:97/12/08 12:22:21.75 kernel  preverror = 0 transtate = 1
00:97/12/08 12:22:21.75 kernel  curcmd = 46 program = isql                          
00:97/12/08 12:22:21.75 kernel  pc: 0x2fd1d8 pcstkwalk+0x78(0xd61a9860, 0xd61a925c, 0x270f, 0x2, 0x0)
00:97/12/08 12:22:21.75 kernel  pc: 0x2fcf48 ucstkgentrace+0x78(0x6ab2002b, 0x2, 0x270f, 0x0, 0x0)
00:97/12/08 12:22:21.75 kernel  pc: 0x2e3994 ucbacktrace+0x94(0x0, 0x1, 0x0, 0xd63cf164, 0x0)
00:97/12/08 12:22:21.75 kernel  pc: 0x48230 terminate_process+0x3d8(0x2400, 0x0, 0xffffffff, 0xffffffff, 0x2e400)
00:97/12/08 12:22:21.75 kernel  pc: 0x2f2940 kisignal+0x70(0xb, 0xd61aa020, 0xd61a9e60, 0x404010a2, 0x1)
00:97/12/08 12:22:21.75 kernel  pc: 0xdf62c08c _fini+0xdf2f40ec(0xb, 0xd61aa020, 0xd61a9e60, 0x4, 0x0)
00:97/12/08 12:22:21.76 kernel  pc: 0x6f5b8 sendhost+0x98(0xd792d000, 0x64, 0xd792d0d0, 0x0, 0x0)
00:97/12/08 12:22:21.76 kernel  pc: 0x1ea1e8 exec_eop+0xcc8(0x0, 0xd63f1574, 0xd61aa53c, 0x1, 0xd792d000)
00:97/12/08 12:22:21.76 kernel  pc: 0x1e9bbc exec_eop+0x69c(0xd792c4c8, 0xd63f1574, 0xd61aa53c, 0x1, 0xd792be38)
00:97/12/08 12:22:21.76 kernel  [Handler pc: 0x1ece40 execerr installed by the following function:-]
00:97/12/08 12:22:21.76 kernel  [Handler pc: 0x1ece40 execerr installed by the following function:-]
00:97/12/08 12:22:21.76 kernel  [Handler pc: 0x174d70 aritherr installed by the following function:-]
00:97/12/08 12:22:21.76 kernel  pc: 0x1e88c8 execute+0xb98(0xd792bde0, 0xd792c1d0, 0xc1, 0xd61aa53c, 0x0)
00:97/12/08 12:22:21.76 kernel  pc: 0x1a50e8 curs_fetch+0x190(0xd792c000, 0x1, 0x37e4, 0x0, 0xd63f1574)
00:97/12/08 12:22:21.76 kernel  pc: 0x24f9c0 s_execute+0x2790(0x2400, 0xd792e5e0, 0xd792c000, 0x0, 0xd792e588)
00:97/12/08 12:22:21.76 kernel  [Handler pc: 0x105e20 s_handle installed by the following function:-]
00:97/12/08 12:22:21.76 kernel  pc: 0x103e6c sequencer+0x13b4(0xd7926000, 0x0, 0x2660, 0xd63f1574, 0x0)
00:97/12/08 12:22:21.76 kernel  pc: 0x251c28 execproc+0x4c8(0xd7926b80, 0xd7926bd8, 0x0, 0x0, 0x0)
00:97/12/08 12:22:21.77 kernel  pc: 0x24eb0c s_execute+0x18dc(0x2400, 0xd7926bd8, 0x0, 0x0, 0xd7926b80)
00:97/12/08 12:22:21.77 kernel  [Handler pc: 0x105e20 s_handle installed by the following function:-]
00:97/12/08 12:22:21.77 kernel  pc: 0x103e6c sequencer+0x13b4(0xd7926000, 0x80000000, 0xb, 0x1, 0x0)
00:97/12/08 12:22:21.77 kernel  [Handler pc: 0x66658 hdl_backout installed by the following function:-]
00:97/12/08 12:22:21.77 kernel  [Handler pc: 0x1458d8 ut_handle installed by the following function:-]
00:97/12/08 12:22:21.77 kernel  pc: 0x190b60 conn_hdlr+0x2208(0x2, 0x2fb2a8, 0x0, 0x0, 0x0)
00:97/12/08 12:22:21.77 kernel  pc: 0x30910c _coldstart+0x4(0x2, 0x18e958, 0x0, 0x0, 0x0)
00:97/12/08 12:22:21.77 kernel  end of stack trace, spid 8, kpid 1790050347, suid 1387
00:97/12/08 12:22:32.19 kernel  current process (0x6ab30014) infected with 11
00:97/12/08 12:22:32.19 kernel  Address 0x696b0 (getnext+0x610), siginfo (code, address) = (1, 0x0)
00:97/12/08 12:22:32.19 kernel  ************************************
00:97/12/08 12:22:32.19 kernel  SQL causing error : ins_wphl7

00:97/12/08 12:22:32.20 kernel  curdb = 8 pstat = 0x10010000 lasterror = 0
00:97/12/08 12:22:32.20 kernel  preverror = 0 transtate = 1
00:97/12/08 12:22:32.20 kernel  curcmd = 46 program = isql                          
00:97/12/08 12:22:32.20 kernel  pc: 0x2fd1d8 pcstkwalk+0x78(0xd60cf060, 0xd60cea5c, 0x270f, 0x2, 0x0)
00:97/12/08 12:22:32.20 kernel  pc: 0x2fcf48 ucstkgentrace+0x78(0x6ab30014, 0x2, 0x270f, 0x0, 0x0)
00:97/12/08 12:22:32.20 kernel  pc: 0x2e3994 ucbacktrace+0x94(0x0, 0x1, 0x0, 0xd63cf164, 0x0)
00:97/12/08 12:22:32.20 kernel  pc: 0x48230 terminate_process+0x3d8(0x2400, 0x0, 0xffffffff, 0xffffffff, 0x2e400)
00:97/12/08 12:22:32.20 kernel  pc: 0x2f2940 kisignal+0x70(0xb, 0xd60cf820, 0xd60cf660, 0x404010a2, 0x1)
00:97/12/08 12:22:32.21 kernel  pc: 0xdf62c08c _fini+0xdf2f40ec(0xb, 0xd60cf820, 0xd60cf660, 0x4, 0x0)
00:97/12/08 12:22:32.21 kernel  pc: 0x6f5b8 sendhost+0x98(0xd7935000, 0x64, 0xd79350d0, 0x0, 0x0)
00:97/12/08 12:22:32.21 kernel  pc: 0x1ea1e8 exec_eop+0xcc8(0x0, 0xd63f1574, 0xd60cfd3c, 0x1, 0xd7935000)
00:97/12/08 12:22:32.21 kernel  pc: 0x1e9bbc exec_eop+0x69c(0xd79344c8, 0xd63f1574, 0xd60cfd3c, 0x1, 0xd7933e38)
00:97/12/08 12:22:32.21 kernel  [Handler pc: 0x1ece40 execerr installed by the following function:-]
00:97/12/08 12:22:32.21 kernel  [Handler pc: 0x1ece40 execerr installed by the following function:-]
00:97/12/08 12:22:32.21 kernel  [Handler pc: 0x174d70 aritherr installed by the following function:-]
00:97/12/08 12:22:32.21 kernel  pc: 0x1e88c8 execute+0xb98(0xd7933de0, 0xd79341d0, 0xc1, 0xd60cfd3c, 0x0)
00:97/12/08 12:22:32.21 kernel  pc: 0x1a50e8 curs_fetch+0x190(0xd7934000, 0x1, 0x37e4, 0x0, 0xd63f1574)
00:97/12/08 12:22:32.21 kernel  pc: 0x24f9c0 s_execute+0x2790(0x2400, 0xd79365e0, 0xd7934000, 0x0, 0xd7936588)
00:97/12/08 12:22:32.21 kernel  [Handler pc: 0x105e20 s_handle installed by the following function:-]
00:97/12/08 12:22:32.22 kernel  pc: 0x103e6c sequencer+0x13b4(0xd792e000, 0x0, 0x2660, 0xd63f1574, 0x0)
00:97/12/08 12:22:32.22 kernel  pc: 0x251c28 execproc+0x4c8(0xd792eb80, 0xd792ebd8, 0x0, 0x0, 0x0)
00:97/12/08 12:22:32.22 kernel  pc: 0x24eb0c s_execute+0x18dc(0x2400, 0xd792ebd8, 0x0, 0x0, 0xd792eb80)
00:97/12/08 12:22:32.22 kernel  [Handler pc: 0x105e20 s_handle installed by the following function:-]
00:97/12/08 12:22:32.22 kernel  pc: 0x103e6c sequencer+0x13b4(0xd792e000, 0x80000000, 0xb, 0x1, 0x0)
00:97/12/08 12:22:32.22 kernel  [Handler pc: 0x66658 hdl_backout installed by the following function:-]
00:97/12/08 12:22:32.22 kernel  [Handler pc: 0x1458d8 ut_handle installed by the following function:-]
00:97/12/08 12:22:32.22 kernel  pc: 0x190b60 conn_hdlr+0x2208(0x2, 0x2fb2a8, 0x0, 0x0, 0x0)
00:97/12/08 12:22:32.22 kernel  pc: 0x30910c _coldstart+0x4(0x2, 0x18e958, 0x0, 0x0, 0x0)
00:97/12/08 12:22:32.22 kernel  end of stack trace, spid 8, kpid 1790115860, suid 1387
00:97/12/08 12:22:42.69 kernel  current process (0x6ab40039) infected with 11
00:97/12/08 12:22:42.69 kernel  Address 0x696b0 (getnext+0x610), siginfo (code, address) = (1, 0x0)
00:97/12/08 12:22:42.69 kernel  ************************************
00:97/12/08 12:22:42.69 kernel  SQL causing error : ins_wphl7

00:97/12/08 12:22:42.69 kernel  curdb = 8 pstat = 0x10010000 lasterror = 0
00:97/12/08 12:22:42.69 kernel  preverror = 0 transtate = 1
00:97/12/08

0
 
LVL 10

Accepted Solution

by:
bret earned 100 total points
ID: 1098130
This looks like bug 122106, which is fixed in the 11.0.2.2 rollup.  You can download this rollup from the Sybase website
http://www.sybase.com (after registering), or from Sybase Customer Service.

Here is the 122106 bug description:
Certain cursors declared on a outer join can cause a stacktrace from getnext(), with signal 11, on the 2nd FETCH. This is only happening on 1102.1, and the cursor works fine on 1102.
0
 

Author Comment

by:osho121097
ID: 1098131
I will try and download the rollup meanwhile and let you know what happens.
Thanks for your help.
Anurag
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This article describes how to reset your Windows 10 password when you've forgotten it.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question