Scheduling an automation script

juman.shawi_625 · October 23, 2009, 1:39pm

Automation script “HEALTHNET” had errors while executing. The execution message and steps are listed below.

Continue on Error Exception Messages:

Exception Messages:

Encountered problem with spooling data to disk.

Closing result spool file.

12288 rows lost from the current result.

Build started

Activity ‘ExceptionEmail’ validation warning: Text of message body is not specified

Build Completed

Executing default description activity

Destination folder name: W:\FL-Tampa-Woodland\RNCBSC\DM\Reports\Monthly Reports\Automation\

File will be copied and not moved

Copied file W:\FL-Tampa-Woodland\RNCBSC\DM\Reports\Monthly Reports\Automation\HealthNet_.xls to W:\FL-Tampa-Woodland\RNCBSC\DM\Reports\Monthly Reports\Automation\HealthNet_2009-10-23.xls

Connecting to ONW_RPT.WORLD (JJSHAWI), NETWORX_OWNER

Connection To: ONW_RPT.WORLD (JJSHAWI), NETWORX_OWNER

Connection successful

Connection logon: JJSHAWI

Last schema: NETWORX_OWNER

Default schema: NETWORX_OWNER

Auto Commit: False

Connection successful.

Executing script: W:\FL-Tampa-Woodland\RNCBSC\DM\DMToadScripts\MonthlyReports\ScriptEditor\HEALTHNET_CONT.sql

Executed Statement

Executed: SELECT DISTINCT T1.PROVPARENTID

, T1.PROVID

, T1.PROVNAME

, T1.ADDR1

, T1.ADDR2

, T1.CITY

, T1.STATE

, T1.ZIP

, T2.SERVICECATID

, T4.COUNTYNAME

FROM TBLPROV T1,TBLPROVIDERSVCCATEGORY T2,TBLPROVCONTRZIP T3, TBLZIPCODEAREA T4

WHERE T3.ZIPCODE = T4.ZIP

AND T1.PROVID = T3.PROVID

AND T2.PROVID = T1.PROVID

AND T1.PROVPARENTID <> 3

AND (T1.PROVID <> 2050 And T1.PROVID <> 9782)

AND (T1.STATE = ‘CA’)

AND (T2.SERVICECATID = ‘THH’)

AND (T2.PROVSVCCATRATING BETWEEN 50

AND 59)

AND (T2.SVCCATRATINGST = ‘CA’)

ORDER BY T1.PROVPARENTID ASC

Script execution complete

Thread (30) Adding Export Notification Subscriber 10/07/2009 8:15:27 AM

Thread (30) Export Started [10/07/2009 8:15:27 AM]

Thread (30) ** START 10/07/2009 8:15:27 AM

Thread (30) Export Wizard: Building object list

Thread (30) Build List 10/07/2009 8:15:28 AM

Thread (30) Exporting Data

Thread (30) BEGIN 10/07/2009 8:15:28 AM : Query

Thread (30) Object SQL Query started.

Thread (58) InternalReadBackground - start

Thread (58) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 126)

ORDER BY TBLCIGNAFCO.CIGNA_FCO_NO ASC,

TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC

Thread (14) DoneReadTreadWriter

Thread (14) DoneReadThreadWriter finally - adapter.RowsRead 2626

Thread (30) END 10/07/2009 8:15:35 AM : Query

Thread (30) Object SQL Query finished.

Thread (30) Export Finished [10/07/2009 8:15:35 AM]

Thread (30) Build Time 00:00:01.1092898

Thread (30) Complete Time 00:00:08.3899806

Thread (30) ** END 10/07/2009 8:15:35 AM

Thread (52) Adding Export Notification Subscriber 10/07/2009 8:55:39 AM

Thread (52) Export Started [10/07/2009 8:55:39 AM]

Thread (52) ** START 10/07/2009 8:55:39 AM

Thread (52) Export Wizard: Building object list

Thread (52) Build List 10/07/2009 8:55:40 AM

Thread (52) Exporting Data

Thread (52) BEGIN 10/07/2009 8:55:40 AM : Query

Thread (52) Object SQL Query started.

Thread (94) InternalReadBackground - start

Thread (94) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 143)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (93) DoneReadTreadWriter

Thread (93) DoneReadThreadWriter finally - adapter.RowsRead 9

Thread (52) END 10/07/2009 8:55:41 AM : Query

Thread (52) Object SQL Query finished.

Thread (52) Export Finished [10/07/2009 8:55:41 AM]

Thread (52) Build Time 00:00:01.0468415

Thread (52) Complete Time 00:00:02.7342875

Thread (52) ** END 10/07/2009 8:55:41 AM

Thread (52) Adding Export Notification Subscriber 10/07/2009 8:55:44 AM

Thread (52) Export Started [10/07/2009 8:55:44 AM]

Thread (52) ** START 10/07/2009 8:55:44 AM

Thread (52) Export Wizard: Building object list

Thread (52) Build List 10/07/2009 8:55:45 AM

Thread (52) Exporting Data

Thread (52) BEGIN 10/07/2009 8:55:45 AM : Query

Thread (52) Object SQL Query started.

Thread (94) InternalReadBackground - start

Thread (94) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 32)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (92) DoneReadTreadWriter

Thread (92) DoneReadThreadWriter finally - adapter.RowsRead 3170

Thread (52) END 10/07/2009 8:55:52 AM : Query

Thread (52) Object SQL Query finished.

Thread (52) Export Finished [10/07/2009 8:55:52 AM]

Thread (52) Build Time 00:00:00.9687190

Thread (52) Complete Time 00:00:07.5466335

Thread (52) ** END 10/07/2009 8:55:52 AM

Thread (95) Adding Export Notification Subscriber 10/07/2009 9:35:31 AM

Thread (95) Export Started [10/07/2009 9:35:31 AM]

Thread (95) ** START 10/07/2009 9:35:31 AM

Thread (95) Export Wizard: Building object list

Thread (95) Build List 10/07/2009 9:35:32 AM

Thread (95) Exporting Data

Thread (95) BEGIN 10/07/2009 9:35:32 AM : Query

Thread (95) Object SQL Query started.

Thread (84) InternalReadBackground - start

Thread (84) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 136)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (81) DoneReadTreadWriter

Thread (81) DoneReadThreadWriter finally - adapter.RowsRead 47

Thread (95) END 10/07/2009 9:35:34 AM : Query

Thread (95) Object SQL Query finished.

Thread (95) Export Finished [10/07/2009 9:35:34 AM]

Thread (95) Build Time 00:00:01.4999520

Thread (95) Complete Time 00:00:04.8123460

Thread (95) ** END 10/07/2009 9:35:36 AM

Thread (95) Adding Export Notification Subscriber 10/07/2009 9:35:40 AM

Thread (95) Export Started [10/07/2009 9:35:40 AM]

Thread (95) ** START 10/07/2009 9:35:40 AM

Thread (95) Export Wizard: Building object list

Thread (95) Build List 10/07/2009 9:35:41 AM

Thread (95) Exporting Data

Thread (95) BEGIN 10/07/2009 9:35:41 AM : Query

Thread (95) Object SQL Query started.

Thread (81) InternalReadBackground - start

Thread (81) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 144)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (84) DoneReadTreadWriter

Thread (84) DoneReadThreadWriter finally - adapter.RowsRead 46

Thread (95) END 10/07/2009 9:35:44 AM : Query

Thread (95) Object SQL Query finished.

Thread (95) Export Finished [10/07/2009 9:35:44 AM]

Thread (95) Build Time 00:00:01.2187110

Thread (95) Complete Time 00:00:05.0623380

Thread (95) ** END 10/07/2009 9:35:45 AM

Thread (95) Adding Export Notification Subscriber 10/07/2009 9:35:48 AM

Thread (95) Export Started [10/07/2009 9:35:48 AM]

Thread (95) ** START 10/07/2009 9:35:48 AM

Thread (95) Export Wizard: Building object list

Thread (95) Build List 10/07/2009 9:35:50 AM

Thread (95) Exporting Data

Thread (95) BEGIN 10/07/2009 9:35:50 AM : Query

Thread (95) Object SQL Query started.

Thread (81) InternalReadBackground - start

Thread (81) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 128)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (23) DoneReadTreadWriter

Thread (23) DoneReadThreadWriter finally - adapter.RowsRead 14

Thread (95) END 10/07/2009 9:35:52 AM : Query

Thread (95) Object SQL Query finished.

Thread (95) Export Finished [10/07/2009 9:35:52 AM]

Thread (95) Build Time 00:00:01.4843275

Thread (95) Complete Time 00:00:03.7030065

Thread (95) ** END 10/07/2009 9:35:52 AM

Thread (95) Adding Export Notification Subscriber 10/07/2009 10:42:59 AM

Thread (95) Export Started [10/07/2009 10:42:59 AM]

Thread (95) ** START 10/07/2009 10:42:59 AM

Thread (95) Export Wizard: Building object list

Thread (95) Build List 10/07/2009 10:43:00 AM

Thread (95) Exporting Data

Thread (95) BEGIN 10/07/2009 10:43:00 AM : Query

Thread (95) Object SQL Query started.

Thread (37) InternalReadBackground - start

Thread (37) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 148)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (25) DoneReadTreadWriter

Thread (25) DoneReadThreadWriter finally - adapter.RowsRead 929

Thread (95) END 10/07/2009 10:43:04 AM : Query

Thread (95) Object SQL Query finished.

Thread (95) Export Finished [10/07/2009 10:43:04 AM]

Thread (95) Build Time 00:00:01.0000128

Thread (95) Complete Time 00:00:05.2813176

Thread (95) ** END 10/07/2009 10:43:04 AM

Thread (95) Adding Export Notification Subscriber 10/07/2009 10:43:07 AM

Thread (95) Export Started [10/07/2009 10:43:07 AM]

Thread (95) ** START 10/07/2009 10:43:07 AM

Thread (95) Export Wizard: Building object list

Thread (95) Build List 10/07/2009 10:43:09 AM

Thread (95) Exporting Data

Thread (95) BEGIN 10/07/2009 10:43:09 AM : Query

Thread (95) Object SQL Query started.

Thread (25) InternalReadBackground - start

Thread (25) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 130)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (37) DoneReadTreadWriter

Thread (37) DoneReadThreadWriter finally - adapter.RowsRead 380

Thread (95) END 10/07/2009 10:43:14 AM : Query

Thread (95) Object SQL Query finished.

Thread (95) Export Finished [10/07/2009 10:43:14 AM]

Thread (95) Build Time 00:00:01.3593924

Thread (95) Complete Time 00:00:06.5625840

Thread (95) ** END 10/07/2009 10:43:14 AM

Thread (95) Adding Export Notification Subscriber 10/07/2009 10:43:17 AM

Thread (95) Export Started [10/07/2009 10:43:17 AM]

Thread (95) ** START 10/07/2009 10:43:17 AM

Thread (95) Export Wizard: Building object list

Thread (95) Build List 10/07/2009 10:43:19 AM

Thread (95) Exporting Data

Thread (95) BEGIN 10/07/2009 10:43:19 AM : Query

Thread (95) Object SQL Query started.

Thread (25) InternalReadBackground - start

Thread (25) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 137)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (80) DoneReadTreadWriter

Thread (80) DoneReadThreadWriter finally - adapter.RowsRead 9

Thread (95) END 10/07/2009 10:43:20 AM : Query

Thread (95) Object SQL Query finished.

Thread (95) Export Finished [10/07/2009 10:43:20 AM]

Thread (95) Build Time 00:00:01.4375184

Thread (95) Complete Time 00:00:03.4219188

Thread (95) ** END 10/07/2009 10:43:21 AM

Thread (95) Adding Export Notification Subscriber 10/07/2009 10:43:26 AM

Thread (95) Export Started [10/07/2009 10:43:26 AM]

Thread (95) ** START 10/07/2009 10:43:26 AM

Thread (95) Export Wizard: Building object list

Thread (95) Build List 10/07/2009 10:43:27 AM

Thread (95) Exporting Data

Thread (95) BEGIN 10/07/2009 10:43:27 AM : Query

Thread (95) Object SQL Query started.

Thread (32) InternalReadBackground - start

Thread (32) InternalReadBackground - after ExecuteReader - command SELECT TBLCIGNAFCO.CARID,

TBLCIGNAFCO.CIGNA_FCO_NO,

TBLCIGNAFCOCODE.“NAME”,

TBLCIGNAFCO.CIGNA_HMO_CD,

TBLCIGNAHMOCODE.“NAME”,

TBLCIGNAFCOADDR.CIGNAFCOADDRID,

TBLCIGNAFCOADDR.ADDR1,

TBLCIGNAFCOADDR.ADDR2,

TBLCIGNAFCOADDR.CITY,

TBLCIGNAFCOADDR.STATE,

TBLCIGNAFCOADDR.ZIP,

TBLCIGNAFCOADDR.PHONE

FROM ( ( NETWORX_OWNER.TBLCIGNAFCO TBLCIGNAFCO

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOCODE TBLCIGNAFCOCODE

ON (TBLCIGNAFCO.CIGNA_FCO_NO = TBLCIGNAFCOCODE.CIGNA_FCO_NO)

AND (TBLCIGNAFCO.CARID = TBLCIGNAFCOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAHMOCODE TBLCIGNAHMOCODE

ON (TBLCIGNAHMOCODE.CARID = TBLCIGNAFCOCODE.CARID)

AND (TBLCIGNAFCO.CIGNA_HMO_CD = TBLCIGNAHMOCODE.CIGNA_HMO_CD)

AND (TBLCIGNAFCO.CARID = TBLCIGNAHMOCODE.CARID))

INNER JOIN

NETWORX_OWNER.TBLCIGNAFCOADDR TBLCIGNAFCOADDR

ON (TBLCIGNAFCO.CIGNAFCOADDRID = TBLCIGNAFCOADDR.CIGNAFCOADDRID)

WHERE (TBLCIGNAFCO.CARID = 123)

ORDER BY TBLCIGNAFCO.CIGNA_HMO_CD ASC,

TBLCIGNAFCOADDR.CIGNAFCOADDRID ASC,

TBLCIGNAFCO.CIGNA_FCO_NO ASC

Thread (87) DoneReadTreadWriter

Thread (87) DoneReadThreadWriter finally - adapter.RowsRead 1619

Thread (95) END 10/07/2009 10:43:31 AM : Query

Thread (95) Object SQL Query finished.

Thread (95) Export Finished [10/07/2009 10:43:31 AM]

Thread (95) Build Time 00:00:01.0937640

Thread (95) Complete Time 00:00:05.7813240

Thread (95) ** END 10/07/2009 10:43:31 AM

Thread (8) Adding Export Notification Subscriber 10/09/2009 9:01:24 AM

Thread (8) Export Started [10/09/2009 9:01:25 AM]

Thread (8) ** START 10/09/2009 9:01:25 AM

Thread (8) Export Wizard: Building object list

Thread (8) Build List 10/09/2009 9:01:29 AM

Thread (8) Exporting Data

Thread (8) BEGIN 10/09/2009 9:01:29 AM : Query

Thread (8) Object SQL Query started.

Thread (6) InternalReadBackground - start

Thread (6) InternalReadBackground - after ExecuteReader - command SELECT DISTINCT TBLPROV.PROVPARENTID

, TBLPROV.PROVID

, TBLPROV.PROVNAME

, TBLPROV.ADDR1

, TBLPROV.ADDR2

, TBLPROV.CITY

, TBLPROV.STATE

, TBLPROV.ZIP

, TBLPROVIDERSVCCATEGORY.SERVICECATID

, TBLZIPCODEAREA.COUNTYNAME

, TBLPROVIDERSVCCATEGORY.PROVSVCCATRATING

, TBLPROVIDERSVCCATEGORY.SVCCATRATINGST

FROM

((NETWORX_OWNER.TBLPROVCONTRZIP TBLPROVCONTRZIP

INNER JOIN

NETWORX_OWNER.TBLZIPCODEAREA TBLZIPCODEAREA

ON (TBLPROVCONTRZIP.ZIPCODE = TBLZIPCODEAREA.ZIP))

INNER JOIN

NETWORX_OWNER.TBLPROV TBLPROV

ON (TBLPROV.PROVID = TBLPROVCONTRZIP.PROVID))

INNER JOIN

NETWORX_OWNER.TBLPROVIDERSVCCATEGORY TBLPROVIDERSVCCATEGORY

ON (TBLPROVIDERSVCCATEGORY.PROVID = TBLPROV.PROVID)

WHERE (TBLPROV.PROVPARENTID <> 3)

AND (TBLPROV.PROVID <> 2050 And TBLPROV.PROVID <> 9782)

AND (TBLPROV.STATE = ‘CA’)

AND (TBLPROVIDERSVCCATEGORY.SERVICECATID = ‘THH’)

AND (TBLPROVIDERSVCCATEGORY.PROVSVCCATRATING BETWEEN 50

AND 59)

AND (TBLPROVIDERSVCCATEGORY.SVCCATRATINGST = ‘CA’)

ORDER BY TBLPROV.PROVPARENTID ASC

Thread (4) DoneReadTreadWriter

Thread (4) DoneReadThreadWriter finally - adapter.RowsRead 27

Thread (8) END 10/09/2009 9:01:38 AM : Query

Thread (8) Object SQL Query finished.

Thread (8) Export Finished [10/09/2009 9:01:38 AM]

Thread (8) Build Time 00:00:04.4058552

Thread (8) Complete Time 00:00:13.4362960

Thread (8) ** END 10/09/2009 9:01:38 AM

Thread (8) Adding Export Notification Subscriber 10/09/2009 9:01:54 AM

Thread (8) Export Started [10/09/2009 9:01:54 AM]

Thread (8) ** START 10/09/2009 9:01:54 AM

Thread (8) Export Wizard: Building object list

Thread (8) Build List 10/09/2009 9:01:55 AM

Thread (8) Exporting Data

Thread (8) BEGIN 10/09/2009 9:01:55 AM : Query

Thread (8) Object SQL Query started.

Thread (6) InternalReadBackground - start

Thread (6) InternalReadBackground - after ExecuteReader - command SELECT DISTINCT TBLPROV.PROVPARENTID

, TBLPROV.PROVID

, TBLPROV.PROVNAME

, TBLPROV.ADDR1

, TBLPROV.ADDR2

, TBLPROV.CITY

, TBLPROV.STATE

, TBLPROV.ZIP

, TBLPROVIDERSVCCATEGORY.SERVICECATID

, TBLZIPCODEAREA.COUNTYNAME

, TBLPROVIDERSVCCATEGORY.PROVSVCCATRATING

, TBLPROVIDERSVCCATEGORY.SVCCATRATINGST

, TBLCONTRACTINFO.CONTRACTNAME

, TBLZIPCODEAREA.STATE

FROM

(((NETWORX_OWNER.TBLPROVCONTRZIP TBLPROVCONTRZIP

INNER JOIN

NETWORX_OWNER.TBLZIPCODEAREA TBLZIPCODEAREA

ON (TBLPROVCONTRZIP.ZIPCODE = TBLZIPCODEAREA.ZIP))

INNER JOIN

NETWORX_OWNER.TBLPROV TBLPROV

ON (TBLPROV.PROVID = TBLPROVCONTRZIP.PROVID))

INNER JOIN

NETWORX_OWNER.TBLPROVIDERSVCCATEGORY TBLPROVIDERSVCCATEGORY

ON (TBLPROVIDERSVCCATEGORY.PROVID = TBLPROV.PROVID))

INNER JOIN

NETWORX_OWNER.TBLCONTRACTINFO TBLCONTRACTINFO

ON (TBLPROVCONTRZIP.KID = TBLCONTRACTINFO.KID)

WHERE (TBLPROV.PROVPARENTID <> 3)

AND (TBLPROV.PROVID <> 2050 And TBLPROV.PROVID <> 9782)

AND (TBLPROV.PROVNAME NOT LIKE ‘%Z%’)

AND (TBLPROV.STATE = ‘CA’)

AND (TBLPROVIDERSVCCATEGORY.SERVICECATID = ‘THH’)

AND (TBLPROVIDERSVCCATEGORY.PROVSVCCATRATING >= 60)

AND (TBLPROVIDERSVCCATEGORY.SVCCATRATINGST = ‘CA’)

AND (TBLCONTRACTINFO.CONTRACTNAME NOT LIKE ‘%PT SPEC%’)

AND (TBLZIPCODEAREA.STATE = ‘CA’)

ORDER BY TBLPROV.PROVPARENTID ASC

Thread (15) DoneReadTreadWriter

Thread (15) DoneReadThreadWriter finally - adapter.RowsRead 558

Thread (8) END 10/09/2009 9:02:06 AM : Query

Thread (8) Object SQL Query finished.

Thread (8) Export Finished [10/09/2009 9:02:06 AM]

Thread (8) Build Time 00:00:01.0311576

Thread (8) Complete Time 00:00:12.1551608

Thread (8) ** END 10/09/2009 9:02:06 AM

Thread (8) Adding Export Notification Subscriber 10/09/2009 9:02:25 AM

Thread (8) Export Started [10/09/2009 9:02:25 AM]

Thread (8) ** START 10/09/2009 9:02:25 AM

Thread (8) Export Wizard: Building object list

Thread (8) Build List 10/09/2009 9:02:26 AM

Thread (8) Exporting Data

Thread (8) BEGIN 10/09/2009 9:02:26 AM : Query

Thread (8) Object SQL Query started.

Thread (6) InternalReadBackground - start

Thread (6) InternalReadBackground - after ExecuteReader - command SELECT T1.PROVPARENTID AS PARID,

T1.PROVID,

T1.FEDERALTAXID,

T1.SHORTNAME,

T1.PROVNAME,

T1.ADDR1,

T1.ADDR2,

T1.CITY,

T1.STATE,

T1.PHONE,

T1.FAX,

SUM (CASE WHEN T2.SERVICECATID = ‘ADMIN’ THEN ‘1’ ELSE NULL END)

AS ADMIN,

SUM(CASE WHEN T2.SERVICECATID = ‘HME’ THEN ‘1’ ELSE NULL END) AS HME,

SUM (CASE WHEN T2.SERVICECATID = ‘HIT’ THEN ‘1’ ELSE NULL END) AS HIT,

SUM (CASE WHEN T2.SERVICECATID = ‘HOSPICE’ THEN ‘1’ ELSE NULL END)

AS HOSPICE,

SUM (CASE WHEN T2.SERVICECATID = ‘MED’ THEN ‘1’ ELSE NULL END) AS MED,

SUM (CASE WHEN T2.SERVICECATID = ‘O/P’ THEN ‘1’ ELSE NULL END) AS OP,

SUM (CASE WHEN T2.SERVICECATID = ‘RESP’ THEN ‘1’ ELSE NULL END) AS

RESP,

SUM (CASE WHEN T2.SERVICECATID = ‘THH’ THEN ‘1’ ELSE NULL END) AS THH,

SUM (CASE WHEN T2.SERVICECATID = ‘WHS’ THEN ‘1’ ELSE NULL END) AS WHS

FROM TBLPROV T1, TBLPROVIDERSVCCATEGORY T2

WHERE T1.PROVID = T2.PROVID

AND ( ( (T2.SERVICECATID) <> ‘EBA’

AND (T2.SERVICECATID) <> ‘DISP’

AND (T2.SERVICECATID) <> ‘REHAB’

AND (T2.SERVICECATID) <> ‘INSTAY’

AND (T2.SERVICECATID) <> ‘LAB’

AND (T2.SERVICECATID) <> ‘SUBACUTE’)

AND ( (T1.PROVID) <> 2050 AND (T1.PROVID) <> 9782)

AND ( (T1.SHORTNAME) NOT LIKE ‘Z%’)

AND ( (T2.PROVSVCCATRATING) >= 30))

GROUP BY T1.PROVPARENTID,

T1.PROVID,

T1.FEDERALTAXID,

T1.SHORTNAME,

T1.PROVNAME,

T1.ADDR1,

T1.ADDR2,

T1.CITY,

T1.STATE,

T1.PHONE,

T1.FAX

ORDER BY T1.PROVNAME

/*** IF DECIDED TO USE DECODE FUNCTION INSTEAD OF CASE STATEMENT

SUM (DECODE (SERVICECATID,‘ADMIN’, ‘1’,NULL)) AS ADMIN,

SUM (DECODE (SERVICECATID,‘RESP’, ‘1’,NULL))AS RESP,

SUM (DECODE (SERVICECATID,‘HME’, ‘1’,NULL)) AS HME ***/

/***THE FOLLOWING DID NOT WORK FOR ME TO CREATE A CROSS TAB

–TRANSFORM First(“X”) AS Expr1

–PIVOT T2.SERVICECATID; ****/

Thread (4) DoneReadTreadWriter

Thread (8) Adding Export Notification Subscriber 10/09/2009 9:03:11 AM

Thread (8) Export Started [10/09/2009 9:03:11 AM]

Thread (8) ** START 10/09/2009 9:03:11 AM

Thread (8) Export Wizard: Building object list

Thread (8) Build List 10/09/2009 9:03:12 AM

Thread (8) Exporting Data

Thread (8) BEGIN 10/09/2009 9:03:12 AM : Query

Thread (8) Object SQL Query started.

Thread (4) DoneReadThreadWriter finally - adapter.RowsRead 10491

Thread (8) END 10/09/2009 9:03:13 AM : Query

Thread (8) Object SQL Query finished.

Thread (8) Export Finished [10/09/2009 9:03:13 AM]

Thread (8) Build Time 00:00:01.1717700

Thread (8) Complete Time 00:00:48.4339220

Thread (8) ** END 10/09/2009 9:03:13 AM

Thread (14) DoneReadTreadWriter

Thread (14) DoneReadThreadWriter finally - adapter.RowsRead 27

Thread (8) END 10/09/2009 9:03:16 AM : Query

Thread (8) Object SQL Query finished.

Thread (8) Export Finished [10/09/2009 9:03:16 AM]

Thread (8) Build Time 00:00:01.1405447

Thread (8) Complete Time 00:00:04.8902807

Thread (8) ** END 10/09/2009 9:03:16 AM

Thread (8) Adding Export Notification Subscriber 10/09/2009 9:03:20 AM

Thread (8)

that need to be run at a certain time/date but get script error message (my computer is on but locked…) , if to test instantly it works and does not fail (the eror reference tables that my sql script does not refrence to at all):-

Debbie_Peabody · October 23, 2009, 9:19pm

The error “Encountered problem with spooling data to disk” only comes from one place. If you have a result set that is over 2048 rows we start serializing it to disk. It is in this code where we are serializing that an unknown error is occurring. I suspect it is data driven. Most of your result sets are smaller than this.

Can you first check your temp dir and make sure there are no restrictions on size and that it is not out of space.
C:\Documents and Settings%your username%\Local Settings\Temp

Then isolate which of the queries has the larger result sets. Execute this in the Editor and try saving as a TEF file. This area also using the serialization.

If you don’t mind sending me the result set, this will be best. They are all going to pass through this same section of code so I am not sure we can get all of the rows. If not send a sample set of data to my email address.

Debbie
dpeabody@quest.com

william.blais_937 · December 14, 2009, 4:33pm

Hello Debbie,

My name is Bill Blais, a director at the same company that Juman, who posted this original thread works for. I have some further information on what we are experiencing that I hope you will be able to help with.

We have created 19 tas scripts that each connect to an oracle 10g database, select some rows to an Excel file (no more than 1000 rows each), and then email the files out.

I have deployed the jobs to a Windows Vista server that is 64-bit (with TOAD for DA 2.5.1 32-bit installed). We are not using the windows job scheduler. Rather we take the command line that the windows job scheduler would normally call and put it in a .bat file to be called via another scheduling tool. The bat file mechanism is working fine and is calling Toad, passing in the tas file path of the file we want to run. Here’s what we are experiencing:

On a fresh install of TOAD, we call the first of the 19 automation scripts

The first one runs fine, creates the excel file we want with the data we want in it and sends the email.

We call the second automation script. The script runs fine, creates the Excel file and sends the email. HOWEVER, when I look in the TOAD log file that got created for the job, there are entries referencing the SQL (and timestamp) from the first job we ran.

And so on and so forth, each time we run a new job, there are references in the log file to all of the old SQL that is not even part of the current job.

Eventually, it gets to a point where we’ve run too many jobs and by the 17th or 18th job, the job fails with the log entry that Juman references in this thread above (“Encountered problem with spooling data to disk”). HOWEVER, I can see that the Excel file that the job that fails was trying to create actually does get created and all the data is properly put into it. Only the email didn’t get sent out. Further, I can open the Excel file just fine. And if I were to have run this particular job that failed earlier in the process, it would have completed just fine and sent the email and not errored out.

From here on out, ALL jobs (even ones that worked earlier) are failing with the same error and behavior. They create the Excel files just fine but there’s all the references to SQL that ran earlier in their log files and the email doesn’t get sent out and there’s the error about spooling to the disk.

I even tried the following:

Removing ALL files in the user’s temp folder (C:\users<user>\AppData\Local\Temp on Windows Vista). Still is somehow keeping a reference to old SQL.
Removing temp files and rebooting. Same result.
Removing the actual *.sql files that are being invalidly referenced. Same result.

The ONLY thing that seems to clear out the cache of old job data is reinstalling TOAD for DA, which obviously we can’t continue to do in a production environment.

It seems to me that the following is occurring:

Each time a job runs, the .tmp files get created to serialize the data before writing it to the Excel file.
The next time any job runs, there’s some left over handle to the old tmp files that is somehow being connected in with the new job.
Over time, there’s just way too much temp data being dragged in and after successfully creating an Excel file with the proper data and closing it, the code of TOAD is trying to write all the garbage temp data into a file that has already been closed, causing the spooling to disk error we are seeing.

There is plenty of temp space available on this server, so it’s not that either.

Anyway, Debbie, this is a pretty complicated error and we’d like to escalate this as quickly as we can as we are looking to go into production very soon. Is there any way we can have a phone call tomorrow (Tuesday, 12/15) so I can better and more quickly explain what we are seeing to you? I am on the east coast and available anytime after 7 AM ET). I know neither you or I can leave our phone numbers on this message board but you should be able to see my email address and send me contact info? Or could you at least advice ASAP on this thread as to next steps?

Thank you very much for your help and your anticipated timely response.

Bill, Director of Business Intelligence at client.

Debbie_Peabody · December 14, 2009, 5:18pm

Wow. What timing. I was just working with another customer on this very same issue. I have entered CR69,439 and CR69,434 for this issue and will be handling it shortly.

The problem is that Export is writing to a log file and not cleaning up the log file. When automation inserts the log file to include in the automation log it goes through some serialization code and throws the error you see.

To work around this issue, impelement the following things:

Change the 'On Error' attribute of all of the 'Select to File' activites to 'ContinueOnError'. This will allow the script to finish even if there is an error.
Add a 'Delete FIle' acitivity at the beginning of each script to delete the export log. Use the path below but change the user name.
C:\Documents and Settings%Username%\Application Data\Quest Software\Toad for Data Analysts 2.5.1\exportbenchmark.txt

Let me know how this goes.

Debbie
P.S. You could actually turn logging off all together and this would also solve the issue.

william.blais_937 · December 14, 2009, 5:29pm

Okay, I’ll give this a try and let you know. Thank you for the quick response. FYI, I also called and opened a support ticket under my customer account. Someone is supposed to be calling me in the next hour or so. I’ll tell them about this thread and that you have responded…

Also, I’d like to leaving logging on so that I have a record of both successful and failed jobs, so that’s not really an option for me…

Thanks,
Bill Blais

william.blais_937 · December 15, 2009, 8:52am

Debbie,

Deleting the ExportBenchmarks.txt file at the beginning of every script run has helped with my issue. I could probably leave each select to file activity at “stoponerror” because I’m deleting this file, but I did change them all to “continueonerror” just in case. My only concern is that I may have 2-3 jobs running at the same time on a server and the second job to run will be deleting the file while the first job is trying to write to it. I’ve tested this and there doesn’t seem to be an issue other than some of the logging info missing in the first job’s log file, but no errors seem to occur. I’ll move forward with this for now. I assume a fix for this will be in some future release so I don’t have to keep putting this delete file activity into every job I write?

Also, it seems there’s a bug where I can set up my job to send an error email within my script settings but if I change “ShowWarnings” to False, no email will ever be sent even if there are errors. As soon as I set this to True and there are errors, emails get sent, but not when it’s set to False. Seems like incorrect behavior to me…

And one other question for you. I am calling toad.exe from a .bat script instead of through Windows scheduler. I’d like to be able to check the status code of calling toad.exe to see if I’m getting a 0 or something else. Does your current code set an exit status code for toad.exe if an error occurs within the a script that is called command line? If not, might be good to do in future releases…

Thanks,
Bill

william.blais_937 · December 15, 2009, 8:58am

One other thing that just popped up now. I’m getting a similar contention issue with email activities. When two scripts try to send an email at the same time, the MailBeeLog.txt file is in use by the first script and thus the second one errors out. I’m guessing if I change my email activities to “continueonerror” as well, it will at least continue to send the email even if it can’t log to that txt file, but it’s be best if you could fix the issue so that there’s not even the contention. The tool should be able to handle multiple tas scripts running at the same time and not have these type of contention issues…

Thanks,
Bill

Debbie_Peabody · December 15, 2009, 10:50am

I’m glad the work around is getting you up and going.

My logging solution that I will be working on will use unique log files. Once the fix is in place you will not need to add the delete file activity.

RE: Emails. The “ShowWarnings” should have nothing to do with whether an error email is sent or not. What is your value of “Email on Error”. This is the only controlling factor of whether an error email is sent or not.

RE: Return code. We currently do not have a return code from automation. Good idea. I entered CR69,584 for this and we can include in version 3.0. (Too late for an enhancement to version 2.6)

Debbie

Debbie_Peabody · December 15, 2009, 10:55am

Do you have logging as Verbose or Basic? I believe that if you put the logging level to Basic you will get adequate logging and the MailBee log file is not used.

You mentioned that you are using a .bat to execute by scheduler. Is this kicking off the automation script and not waiting for the exit? If this is the case, and you have 19 scripts, then you would have 19 instances of Toad running at the same time. Do you need to have them all running at the same time? Or this is just a byproduct of using the .bat file?

Debbie

william.blais_937 · December 15, 2009, 11:21am

My logging solution that I will be working on will use unique log files. Once the fix is in place you will not need to add the delete file activity.

BB: Okay, sounds good. Might want to do the same for email log as well.

RE: Emails. The “ShowWarnings” should have nothing to do with whether an error email is sent or not. What is your value of “Email on Error”. This is the only controlling factor of whether an error email is sent or not.

BB: “Email on Error” = TRUE, LoggingLevel=Verbose, when I set ShowWarnings to FALSE, I never get any error email of any kind (and I’ve had many different types of errors occur). When I set ShowWarnings to TRUE, I get error emails.

RE: Return code. We currently do not have a return code from automation. Good idea. I entered CR69,584 for this and we can include in version 3.0. (Too late for an enhancement to version 2.6)

BB: Thanks. Will be a useful feature

william.blais_937 · December 15, 2009, 11:25am

Do you have logging as Verbose or Basic? I believe that if you put the logging level to Basic you will get adequate logging and the MailBee log file is not used.

BB: Understood. Thanks.

You mentioned that you are using a .bat to execute by scheduler. Is this kicking off the automation script and not waiting for the exit? If this is the case, and you have 19 scripts, then you would have 19 instances of Toad running at the same time. Do you need to have them all running at the same time? Or this is just a byproduct of using the .bat file?

BB: No, the bat file does wait for the automation script to exit. I don’t want to run all 19 at once, but I also don’t want the bottleneck on a production server of having to run only one at a time. All of these contentions issues could definitely occur using the Windows scheduler just as easily as using the batch script. My only point to you is that you are guaranteed to have customers who want to be able to run many automation scripts at the same time on a server in a production environment…

Debbie_Peabody · December 15, 2009, 11:59am

Yup. You are correct about the Error email. I confirmed this. I will fix for the 2.6 release. Entered as CR69,586.

Thanks for bringing that to my attention.

Debbie

Debbie_Peabody · December 15, 2009, 12:00pm

Understood. I will add this type o scalability to our test cases.

Thanks.

Debbie

Debbie_Peabody · January 14, 2010, 6:10pm

The issue regarding the spooling data to disk and deleting log files has been resolved and is included in today’s Beta posting 2.6.0.460. If it is possible can you download and test that this fix works for you?

Debbie