1 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Some taxon statuses cause report wizard error

Came across the error listed below today. It only occurrs on some taxon statuses (newer ones, from what I can tell). I had a look in the database and found a problem with the REPORT_ATTRIBUTE_KEY.ATTRIBUTE_SQL column: in some of the fields there were quotes missing from the end of the values. For example, this:

#REPORT_OUTPUT.[BirdRed]='Yes

When it should be this:

#REPORT_OUTPUT.[BirdRed]='Yes'

However, fixing all of these did not fix the problem. Here's the error (the only thing I can see that might cause a problem is the lack of collate SQL_Latin1_General_CP1_CI_AS next to the status:

date/time         : 2010-04-12, 14:59:28, 128ms
computer name     : BRC_IT_OFFICER
user name         : CharlesR
operating system  : Windows XP Service Pack 3 build 2600
system language   : English
system up time    : 6 hours 1 minute 
program up time   : 1 minute 10 seconds 
processors        : 4x Intel(R) Xeon(R) CPU           E5405  @ 2.00GHz
physical memory   : 1483/3326 MB (free/total)
free disk space   : (C:) 108.35 GB (T:) 42.51 GB
display mode      : 1680x1050, 32 bit
process id        : $19f4
allocated memory  : 19.75 MB
command line      : RecorderApp.exe /2691394
executable        : RecorderApp.exe
exec. date/time   : 2009-08-05 13:49
version           : 6.14.4.212
madExcept version : 2.7k
exception class   : TExceptionPath
exception message : An error occurred executing the following SQL: SELECT Distinct Taxon_Occurrence.Taxon_Occurrence_Key AS Occurrence_Key, 'T' AS Type, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_EVENT_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SAMPLE_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as LIST_ITEM_KEY , Convert(char(1),Null) as [Bird:Red] into "#REPORT_OUTPUT" From ((Taxon_Occurrence LEFT JOIN Sample ON Taxon_Occurrence.sample_Key = Sample.Sample_Key ) LEFT JOIN Survey_Event ON Survey_Event.Survey_Event_Key = Sample.Survey_Event_Key ) LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE USR.Name_Key = 'THU00002000001TP') AND (Sample.Sample_Key IN (SELECT RecordKey FROM #Templist)).

main thread ($890):
008978d0 RecorderApp.exe  
7e4196c2 user32.dll       DispatchMessageA

thread $1ba0:
7c90e514 ntdll.dll     KiFastSystemCallRet
7c90df48 ntdll.dll     NtWaitForMultipleObjects
7c80958a kernel32.dll  WaitForMultipleObjectsEx
7e4195f3 user32.dll    MsgWaitForMultipleObjectsEx
7e4196a3 user32.dll    MsgWaitForMultipleObjects
>> created by main thread ($890) at:
4ec67b7f gdiplus.dll   

thread $1bec:
7c90e514 ntdll.dll   KiFastSystemCallRet
7c90daa8 ntdll.dll   NtReplyWaitReceivePortEx
>> created by main thread ($890) at:
77e7df36 RPCRT4.dll  

thread $1d0c:
7c90e514 ntdll.dll     KiFastSystemCallRet
7c90df58 ntdll.dll     NtWaitForSingleObject
7c8025d5 kernel32.dll  WaitForSingleObjectEx
>> created by main thread ($890) at:
7752fd64 ole32.dll     

thread $1c1c:
7c90e514 ntdll.dll     KiFastSystemCallRet
7c90df58 ntdll.dll     NtWaitForSingleObject
7c8025d5 kernel32.dll  WaitForSingleObjectEx
7c80253d kernel32.dll  WaitForSingleObject

thread $14cc:
7c90e514 ntdll.dll     KiFastSystemCallRet
7c90df48 ntdll.dll     NtWaitForMultipleObjects
7c80958a kernel32.dll  WaitForMultipleObjectsEx
>> created by main thread ($890) at:
77df8c8c advapi32.dll  

thread $c50:
7c90e514 ntdll.dll     KiFastSystemCallRet
7c90df58 ntdll.dll     NtWaitForSingleObject
7c8025d5 kernel32.dll  WaitForSingleObjectEx
7c80253d kernel32.dll  WaitForSingleObject

thread $1a34:
7c90e514 ntdll.dll     KiFastSystemCallRet
7c90df48 ntdll.dll     NtWaitForMultipleObjects
7c80958a kernel32.dll  WaitForMultipleObjectsEx
7c80a110 kernel32.dll  WaitForMultipleObjects

thread $1f38:
7c90e514 ntdll.dll   KiFastSystemCallRet
7c90daa8 ntdll.dll   NtReplyWaitReceivePortEx
>> created by thread $1bec at:
77e7df36 RPCRT4.dll  

modules:
00340000 C4DLL.DLL          1.0.0.1          T:\Recorder 6 Server
003b0000 MS5User.Dll        5.0.0.4          T:\Recorder 6 Server
00400000 RecorderApp.exe    6.14.4.212       T:\Recorder 6 Server
01460000 ms5cnv.dll         5.0.0.10         T:\Recorder 6 Server
01c70000 odbcint.dll        3.525.1132.0     C:\WINDOWS\system32
02a00000 xpsp2res.dll       5.1.2600.5512    C:\WINDOWS\system32
03f90000 SQLOLEDB.RLL       2000.85.1132.0   C:\Program Files\Common Files\System\Ole DB
03fa0000 msadcer.dll        2.81.1132.0      C:\Program Files\Common Files\System\msadc
042a0000 BulkLoad.ocx       3.0.0.25         T:\RECORD~1\Addins
04550000 DMAPSE~1.OCX       6.6.6.7          T:\RECORD~1\Addins
04720000 GOTOGR~1.OCX       6.0.0.1          T:\RECORD~1\Addins
048b0000 GoToKey.ocx        6.0.0.5          T:\RECORD~1\Addins
04a80000 NBNEXC~1.OCX       6.13.2.24        T:\RECORD~1\Addins
04cb0000 PHENOL~1.OCX       6.0.0.6          T:\RECORD~1\Addins
04f60000 SHOWUN~1.OCX       6.0.0.6          T:\RECORD~1\Addins
05140000 SMART.ocx          1.0.1.5          T:\RECORD~1\Addins
054d0000 STDVAL~1.DLL       6.14.3.208       T:\RECORD~1\Addins
056d0000 SURVEY~1.OCX       6.0.0.30         T:\RECORD~1\Addins
05d20000 VSNetRdr.dll       2.5.58.54        C:\WINDOWS\system32
05d80000 LMIRfsClientNP.dll 2.1.3.0          C:\WINDOWS\system32
064d0000 msader15.dll       2.81.1132.0      C:\Program Files\Common Files\System\ado
10000000 MS5.Dll            5.0.0.12         T:\Recorder 6 Server
16080000 mdnsNSP.dll        1.0.6.2          C:\Program Files\Bonjour
20000000 xpsp3res.dll       5.1.2600.5512    C:\WINDOWS\system32
3fde0000 msi.dll            4.5.6001.22159   C:\WINDOWS\system32
4dd00000 sqloledb.dll       2000.85.1132.0   C:\Program Files\Common Files\System\Ole DB
4de10000 msado15.dll        2.81.1132.0      C:\Program Files\Common Files\System\ado
4ec50000 gdiplus.dll        5.2.6001.22319   C:\WINDOWS\WinSxS\x86_Microsoft.Windows.GdiPlus_6595b64144ccf1df_1.0.6001.22319_x-ww_f0b4c2df
55600000 FwcWsp.dll         4.0.3442.654     C:\Program Files\Microsoft Firewall Client 2004
5ad70000 uxtheme.dll        6.0.2900.5512    C:\WINDOWS\system32
5b860000 NETAPI32.dll       5.1.2600.5694    C:\WINDOWS\system32
5d090000 comctl32.dll       5.82.2900.5512   C:\WINDOWS\system32
5edd0000 olepro32.dll       5.1.2600.5512    C:\WINDOWS\system32
60e30000 MSDATL3.dll        2.81.1132.0      C:\Program Files\Common Files\System\Ole DB
61880000 OLEACC.dll         7.0.2600.5884    C:\WINDOWS\system32
662b0000 hnetcfg.dll        5.1.2600.5512    C:\WINDOWS\system32
68000000 rsaenh.dll         5.1.2600.5507    C:\WINDOWS\system32
68100000 dssenh.dll         5.1.2600.5507    C:\WINDOWS\system32
69b10000 msxml4.dll         4.20.9876.0      c:\WINDOWS\system32
6c1b0000 DUSER.dll          5.1.2600.5512    C:\WINDOWS\system32
6d4f0000 DBNETLIB.DLL       2000.85.1132.0   C:\WINDOWS\system32
71a50000 mswsock.dll        5.1.2600.5625    C:\WINDOWS\system32
71a90000 wshtcpip.dll       5.1.2600.5512    C:\WINDOWS\System32
71aa0000 WS2HELP.dll        5.1.2600.5512    C:\WINDOWS\system32
71ab0000 WS2_32.dll         5.1.2600.5512    C:\WINDOWS\system32
71ad0000 wsock32.dll        5.1.2600.5512    C:\WINDOWS\system32
71b20000 mpr.dll            5.1.2600.5512    C:\WINDOWS\system32
71bf0000 SAMLIB.dll         5.1.2600.5512    C:\WINDOWS\System32
71c10000 ntlanman.dll       5.1.2600.5512    C:\WINDOWS\System32
71c80000 NETRAP.dll         5.1.2600.5512    C:\WINDOWS\System32
71c90000 NETUI1.dll         5.1.2600.5512    C:\WINDOWS\System32
71cd0000 NETUI0.dll         5.1.2600.5512    C:\WINDOWS\System32
71f80000 security.dll       5.1.2600.5512    C:\WINDOWS\system32
72b40000 appwiz.cpl         5.1.2600.5512    C:\WINDOWS\system32
73000000 winspool.drv       5.1.2600.5512    C:\WINDOWS\system32
73160000 oledb32.dll        2.81.1132.0      C:\Program Files\Common Files\System\Ole DB
74060000 msadce.dll         2.81.3002.0      C:\Program Files\Common Files\System\msadc
74320000 odbc32.dll         3.525.1132.0     C:\WINDOWS\system32
74720000 MSCTF.dll          5.1.2600.5512    C:\WINDOWS\system32
750b0000 RESUTILS.DLL       5.1.2600.5512    C:\WINDOWS\system32
750f0000 MTXCLU.DLL         2001.12.4414.706 C:\WINDOWS\system32
75130000 colbact.DLL        2001.12.4414.700 C:\WINDOWS\system32
75350000 OLEDB32R.DLL       2.81.1132.0      C:\Program Files\Common Files\System\Ole DB
755c0000 msctfime.ime       5.1.2600.5512    C:\WINDOWS\system32
75f60000 drprov.dll         5.1.2600.5512    C:\WINDOWS\System32
75f70000 davclnt.dll        5.1.2600.5512    C:\WINDOWS\System32
76380000 MSIMG32.dll        5.1.2600.5512    C:\WINDOWS\system32
76390000 IMM32.DLL          5.1.2600.5512    C:\WINDOWS\system32
763b0000 comdlg32.dll       6.0.2900.5512    C:\WINDOWS\system32
765b0000 MSDART.DLL         2.81.1132.0      C:\WINDOWS\system32
76620000 comsvcs.dll        2001.12.4414.702 C:\WINDOWS\system32
76780000 SHFolder.dll       6.0.2900.5512    C:\WINDOWS\system32
76790000 cryptdll.dll       5.1.2600.5512    C:\WINDOWS\system32
767a0000 ntdsapi.dll        5.1.2600.5512    C:\WINDOWS\system32
767f0000 schannel.dll       5.1.2600.5834    C:\WINDOWS\system32
769c0000 USERENV.dll        5.1.2600.5512    C:\WINDOWS\system32
76b40000 winmm.dll          5.1.2600.5512    C:\WINDOWS\system32
76d10000 CLUSAPI.DLL        5.1.2600.5512    C:\WINDOWS\system32
76d60000 iphlpapi.dll       5.1.2600.5512    C:\WINDOWS\system32
76f20000 DNSAPI.dll         5.1.2600.5625    C:\WINDOWS\system32
76f60000 WLDAP32.dll        5.1.2600.5512    C:\WINDOWS\system32
76fb0000 winrnr.dll         5.1.2600.5512    C:\WINDOWS\System32
76fc0000 rasadhlp.dll       5.1.2600.5512    C:\WINDOWS\system32
76fd0000 CLBCATQ.DLL        2001.12.4414.700 C:\WINDOWS\system32
77050000 COMRes.dll         2001.12.4414.700 C:\WINDOWS\system32
77120000 oleaut32.dll       5.1.2600.5512    C:\WINDOWS\system32
773d0000 comctl32.dll       6.0.2900.5512    C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.5512_x-ww_35d4ce83
774e0000 ole32.dll          5.1.2600.5512    C:\WINDOWS\system32
77920000 SETUPAPI.dll       5.1.2600.5512    C:\WINDOWS\system32
77a80000 crypt32.dll        5.131.2600.5512  C:\WINDOWS\system32
77b20000 MSASN1.dll         5.1.2600.5875    C:\WINDOWS\system32
77c00000 version.dll        5.1.2600.5512    C:\WINDOWS\system32
77c10000 msvcrt.dll         7.0.2600.5512    C:\WINDOWS\system32
77c70000 msv1_0.dll         5.1.2600.5876    C:\WINDOWS\system32
77dd0000 advapi32.dll       5.1.2600.5755    C:\WINDOWS\system32
77e70000 RPCRT4.dll         5.1.2600.5795    C:\WINDOWS\system32
77f10000 GDI32.dll          5.1.2600.5698    C:\WINDOWS\system32
77f60000 SHLWAPI.dll        6.0.2900.5912    C:\WINDOWS\system32
77fe0000 Secur32.dll        5.1.2600.5834    C:\WINDOWS\system32
7c800000 kernel32.dll       5.1.2600.5781    C:\WINDOWS\system32
7c900000 ntdll.dll          5.1.2600.5755    C:\WINDOWS\system32
7c9c0000 shell32.dll        6.0.2900.5622    C:\WINDOWS\system32
7e410000 user32.dll         5.1.2600.5512    C:\WINDOWS\system32
7e4b0000 HHCtrl.ocx         5.2.3790.4110    C:\WINDOWS\system32
7e720000 SXS.DLL            5.1.2600.5512    C:\WINDOWS\system32

Exception Stack

----------------
EOleException : Parameter object is improperly defined. Inconsistent or incomplete information was provided
Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Some taxon statuses cause report wizard error

Hi Charles
Heres the SQL I used to fix the missing quotes:

UPDATE Report_Attribute 
SET Attribute_Sql=CAST(Attribute_Sql AS VARCHAR(8000))+''''
where Attribute_Sql like '%=''Yes'

You are right though - this does not fix the entire problem. It's not the collation sequence though, the actual problem I get is caused by the : in the field name, which Delphi's query parser takes to mean a parameter. There is a fix for this in the pipeline but for now the following script could be used to avoid the use of colons in the field names.

UPDATE Report_Attribute 
SET Attribute_Sql=REPLACE(CAST(Attribute_Sql AS VARCHAR(8000)), ':', '-')
WHERE Attribute_Sql like '%:%'

UPDATE Report_Field
SET Field_Item_Name=REPLACE(Field_Item_Name, ':', '-')
WHERE Field_Item_Name like '%:%'
John van Breda
Biodiverse IT

johnvanbreda's Website