Find mutli-byte character in text column

[Former Member]

We have been getting stack trace in ASE log when a report using view containing text column is executed. Text column was excluded from the select statement and no stack trace has been generated since then.

Is there anyway to search for non-ascii/non-printable character text column.

[Former Member]

Stack trace was reporting a SQL which had a join with the view, SQL is dynamic SQL generated from old cognos tool impromptu.

00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  Current process (0x5add01c2) infected with signal 11 (SIGSEGV)
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  Address 0x0x00000000008767c4 (LeAlignedDataRow::writeRow(unsigned char*, int, sdes*, sdes*)+0xa4), siginfo (code, address) = (1, 0x0x0000002e00008d0e)
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  **** Saved signal context (0x0x00007f61ed82be00): ****
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  uc_flags: 0x1, uc_link: 0x(nil)
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  uc_sigmask: 0x416004 0xb 0x1 0x8d0e
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  uc_stack: ss_sp: 0x(nil), ss_size: 0x0, ss_flags: 0x2
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  General Registers (uc_mcontext.gregs):
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       PC : 0x00000000008767c4 (LeAlignedDataRow::writeRow(unsigned char*, int, sdes*, sdes*)+0xa4)
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel          RAX : 0x00007f64f3c219c0  RBX : 0x0000002e00008d0e
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel          RCX : 0x0000000000000001  RDX : 0x00007f64f3c219b0
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       RBP : 0x00007f61ed82c440  RSP : 0x00007f61ed82c3b0
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       R8  : (nil)  R9  : (nil)
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       R10 : (nil)  R11 : 0x0000003876c83f3a
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       R12 : 0x00000000fffffffe  R13 : 0x00007f64f3c218c0
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       R14 : 0x00007f64f3c117b0  R15 : 0x0000000000000014
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       RDI : 0x00007f64f3c21988  RSI : 0x00007f64f3c117b0
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       RIP : 0x00000000008767c4  CSGSFS : 0x0000000000000033
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       TRAPNO : 0x000000000000000e  ERR : 0x0000000000000004
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel       EFL : 0x0000000000010282
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  **** end of signal context ****
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  ************************************
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  SQL causing error :
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  ************************************
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text:  select T11.dt_key, T1.firm_nm, T2.clrg_corp_id, case  when T3.mfr_line_item='ENC' and T4.ENC0<case  when not T3.ovrd is null then T3.ovrd when T3.mltplr is null then T3.mfr_amt else T3.mltplr*T3.mfr_amt end  then T4.ENC0 else NULL end , substring('09/2016',1,2), T5.dt_key, case  when T4.DRP1<=-0.25 then T4.DRP1 else NULL end , case  when T4.DRP3<=-0.5 then T4.DRP3 else NULL end , case  when T4.LOS1<=-0.15 then T4.LOS1 else NULL end , case  when
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: se  when (T5.excss_nc_bas!=0 and T5.nc!=0) then convert(float(48),T5.tot_ai)/T5.nc else NULL end >12.0 then case  when T5.excss_nc_bas!=0 and T5.nc!=0 then convert(float(48),T5.tot_ai)/T5.nc else NULL end  else NULL end , case  when case  when (T5.excss_nc!=0 and T6.agg_dr_items!=0) then convert(float(48),T5.nc)/T6.agg_dr_items else NULL end <0.05 then case  when T5.excss_nc!=0 and T6.agg_dr_items!=0 then convert(float(48),T5.nc)/T6.agg_dr_items
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: se NULL end  else NULL end , case  when case  when (isnull(T5.min_nc_rqrd,0)!=0) then convert(float(48),T5.nc)/T5.min_nc_rqrd else case  when (isnull(T5.min_nc_rqrd_alt,0)!=0) then convert(float(48),T5.nc)/T5.min_nc_rqrd_alt else NULL end  end <=1.2 then case  when isnull(T5.min_nc_rqrd,0)!=0 then convert(float(48),T5.nc)/T5.min_nc_rqrd else case  when isnull(T5.min_nc_rqrd_alt,0)!=0 then convert(float(48),T5.nc)/T5.min_nc_rqrd_alt else NULL end
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: nd  else NULL end , case  when case  when T1.anlst_grpng is null then 'No Value' else T7.anlys_nm end  is null then 'novalue' else case  when T1.anlst_grpng is null then 'No Value' else T7.anlys_nm end  end , case  when T8.ds='Watchlist                     ' then 'w' else NULL end , T9.excptn_resn, T1.intrl_rtng_cd, case  when T4.LOS3<=-0.30 then T4.LOS3 else NULL end , case  when case  when T4.DRP1<=-0.25 then T4.DRP1 else NULL end  is null and
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: se  when T4.DRP3<=-0.5 then T4.DRP3 else NULL end  is null and case  when T4.LOS1<=-0.15 then T4.LOS1 else NULL end  is null and case  when case  when (T5.excss_nc_bas!=0 and T5.nc!=0) then convert(float(48),T5.tot_ai)/T5.nc else NULL end >12.0 then case  when T5.excss_nc_bas!=0 and T5.nc!=0 then convert(float(48),T5.tot_ai)/T5.nc else NULL end  else NULL end  is null and case  when case  when (T5.excss_nc!=0 and T6.agg_dr_items!=0) then convert(
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: oat(48),T5.nc)/T6.agg_dr_items else NULL end <0.05 then case  when T5.excss_nc!=0 and T6.agg_dr_items!=0 then convert(float(48),T5.nc)/T6.agg_dr_items else NULL end  else NULL end  is null and case  when case  when (isnull(T5.min_nc_rqrd,0)!=0) then convert(float(48),T5.nc)/T5.min_nc_rqrd else case  when (isnull(T5.min_nc_rqrd_alt,0)!=0) then convert(float(48),T5.nc)/T5.min_nc_rqrd_alt else NULL end  end <=1.2 then case  when isnull(T5.min_nc_rqr
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: 0)!=0 then convert(float(48),T5.nc)/T5.min_nc_rqrd else case  when isnull(T5.min_nc_rqrd_alt,0)!=0 then convert(float(48),T5.nc)/T5.min_nc_rqrd_alt else NULL end  end  else NULL end  is null and case  when T3.mfr_line_item='ENC' and T4.ENC0<case  when not T3.ovrd is null then T3.ovrd when T3.mltplr is null then T3.mfr_amt else T3.mltplr*T3.mfr_amt end  then T4.ENC0 else NULL end  is null and case  when T4.LOS3<=-0.3 then T4.LOS3 else NULL end  is
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: ull then 1 else 0 end  from dafrs01.dbo.v_min_rqrmnt_n_mltplr T3, dafrs01.dbo.balance_sheet_fact T11, dafrs01.dbo.net_capital_fact T5, dafrs01.dbo.enc_month_v T4, dafrs01.dbo.fifteen_c33_fact T6, dafrs01.dbo.survllnce_stat T8, dafrs01.dbo.clearing_corp_lookup T10, dafrs01.dbo.clearing_corp_mbrshp T2 LEFT OUTER JOIN dafrs01.dbo.annotation_watchlist_v T9 on T2.clrg_corp_id=T9.clrg_corp_id and T2.clrg_corp_nm=T9.clrg_corp_nm and T2.id=T9.id, dafrs01
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: bo.profile_dimension T1 LEFT OUTER JOIN dafrs01.dbo.anlys_group T7 on T1.anlst_grpng=T7.anlys_id where case  when case  when T1.anlst_grpng is null then 'No Value' else T7.anlys_nm end  is null then 'novalue' else case  when T1.anlst_grpng is null then 'No Value' else T7.anlys_nm end  end  in ('ANalezny            ', 'asanchez            ', 'dsissokho           ', 'ezalechansky        ', 'Jingzhou He         ', 'jmacchia            ', 'KKaneko
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text:         ', 'lregnier            ', 'Michael Fields      ', 'mwilkens            ', 'mwilliams           ', 'No Value            ', 'None                ', 'Ravin Robert        ', 'SMalhotra           ') and T1.firm_type='Broker/Dealer' and case  when (T1.filng_freq='A') then 'Annual' when (T1.filng_freq='S') then 'Semi-Annual' when (T1.filng_freq='M') then 'Monthly' when (T1.filng_freq='Q') then 'Quarterly' else T1.filng_freq end ='Monthly    ' a
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text:  T2.mbrshp_stat='Participant' and T10.clrg_corp_ds='DTC               ' and T2.clrg_corp_nm=T10.clrg_corp_nm and T8.survllnce_stat=T2.survllnce_stat and T5.rpt_type=T6.rpt_type and T5.dt_key=T6.dt_key and T5.id=T6.id and T4.dt_key=T5.dt_key and T4.id=T5.id and T5.rpt_type=T11.rpt_type and T5.dt_key=T11.dt_key and T5.id=T11.id and T2.id=T11.id and T3.mbrshp_subtype=T2.mbrshp_subtype and T3.mbrshp_type=T2.mbrshp_stat and T3.clrg_corp_id=T2.clrg_cor
00:0008:00000:00164:2016/10/31 08:14:09.59 server  SQL Text: id and T3.clrg_corp_nm=T2.clrg_corp_nm and T3.id=T2.id and T2.id=T1.id FOR READ ONLY
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  curdb = 32 tempdb = 2 pstat = 0x10010000 p2stat = 0x40101000
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  p3stat = 0x800 p4stat = 0x0 p5stat = 0x8 p6stat = 0x10 p7stat = 0x10000
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  lasterror = 0 preverror = 0 transtate = 0
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  curcmd = 264 program = Impromptu
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  extended error information: hostname: Windows PC Client login: Cognos_jmacchia
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x0000000001640c5b pcstkwalk+0x3a3()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x0000000001640733 ucstkgentrace+0x263()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x000000000163c66f ucbacktrace+0x51()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x0000000000789c87 terminate_process+0xa87()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x0000000001690028 kisignal+0x31f()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000008767c4 LeAlignedDataRow::writeRow(unsigned char*, int, sdes*, sdes*)+0xa4()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x000000000086be3d LeCacheRow::writeRow(le_row*)+0x2d()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000018e134e LeSQCache::LeCacheInsert()+0x1e()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000018d08eb LeSQFilterOp::runSubEvals(LeEvals*, ExeCtxt&, int)+0x17b()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000018d09c1 LeSQFilterOp::_LeOpNext(ExeCtxt&)+0x61()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000008087a6 LeDMLOp::bulkInsClose(ExeCtxt&, int)+0xac8()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000018c7054 LeRestrictOp::_LeOpNext(ExeCtxt&)+0x234()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x000000000081affa LeEmitSndOp::_LeOpNext(ExeCtxt&)+0x1ba()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x0000000000801ec7 LePlanNext+0xf7()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  [Handler pc: 0x0x000000000100bdd0 le_execerr installed by the following function:-]
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x000000000101812d exec_lava+0x53d()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x0000000001172ed2 curs_fetch+0xe2()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000011d0ede s_execute+0x2abe()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  [Handler pc: 0x0x000000000127ae70 hdl_stack installed by the following function:-]
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  [Handler pc: 0x0x00000000012102e0 s_handle installed by the following function:-]
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x0000000001213490 sequencer+0xaf0()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000007cd27d tdsrecv_fetch+0x13d()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  [Handler pc: 0x0x0000000001595830 ut_handle installed by the following function:-]
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  pc: 0x00000000007eecfa conn_hdlr+0x122a()
00:0008:00000:00164:2016/10/31 08:14:09.59 kernel  end of stack trace, spid 164, kpid 1524433346, suid 766
00:0013:00000:00857:2016/10/31 08:21:48.25 kernel  Cannot read, host process disconnected:  3636 spid: 857
00:0010:00000:00473:2016/10/31 08:39:23.42 kernel  Cannot read, host process disconnected:  3544 spid: 473

[Former Member]

Hi Mark,

Thanks for your reply. Originally, a case was opened with tech support and I was told to post question on scn. My apologies for not getting back on this post, we did have another ongoing issue (I will open a separate thread).

Developer had space(255) in ELSE part in each case statement, in troubleshooting it was changed to space(256).

Definition of view

CREATE VIEW dbo.annotation_watchlist_v AS

SELECT a.id, a.clrg_corp_nm, a.clrg_corp_id,

substring(MAX(

CASE WHEN annotn_type = 'surv_oresn' THEN convert(char(8),annotn_entd_dt, 112) + convert(char(8),annotn_entd_dt, 108) + convert(char(239), annotn_text)

ELSE space(256) END), 17, 239) as "orig_resn",

substring(MAX(CASE WHEN annotn_type = 'surv_dresn' THEN convert(char(8),annotn_entd_dt, 112) + convert(char(8),annotn_entd_dt, 108) + convert(char(239), annotn_text)

ELSE space(256) END), 17, 239) as "drop_resn",

substring(MAX(CASE WHEN annotn_type = 'surv_resn' THEN convert(char(8),annotn_entd_dt, 112) + convert(char(8),annotn_entd_dt, 108) + convert(char(239), annotn_text)

ELSE space(256) END), 17, 239) as "remain_resn",

substring(MAX(CASE WHEN annotn_type = 'surv_exep' THEN convert(char(8),annotn_entd_dt, 112) + convert(char(8),annotn_entd_dt, 108) + convert(char(239), annotn_text)

ELSE space(256) END), 17, 239) as "excptn_resn"

from annotation a

where

clrg_corp_nm <> NULL

group by a.id, a.clrg_corp_nm, a.clrg_corp_id