Solved

Problem while using inner join in a cursor

Posted on 1997-12-10
5
707 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
Comment Utility
Adjusted points to 100
0
 
LVL 10

Expert Comment

by:bret
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I will try and download the rollup meanwhile and let you know what happens.
Thanks for your help.
Anurag
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
A procedure for exporting installed hotfix details of remote computers using powershell
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now