Solved

Problem while using inner join in a cursor

Posted on 1997-12-10
5
748 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Title # Comments Views Activity
Small DB [ Application with 20GB Storage Size Minimum ] 4 520
Copy a TadoDataSet to another and apply changes to database. 11 1,290
SyBase SQL Query Syntax 11 474
sql anywhere query 5 146
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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