SEO

Hi,

We are experiencing "Automation Error: The object involved has
disconnected from itsd client" errors when executing the following VBA
code: "Selection.QueryTable.Refresh BackgroundQuery:=False"
The error does not occur consistently and many spreadsheets have never
exhibited the problem. A small group of spreadsheets often show the
problem but not at consistent times or at the same query. The
spreadsheets were originally written with Excel 97 using Windows 2000,
MDAC 2.6 and Oracle version 8.1.7 and ran without any such errors, only
showing this error since we upgraded to XP SP2 and Office 2003 SP1
against Oracle 9.2.
We have tried different versions of MDAC; on Windows 2000 server with
Office 2003; with Merant ODBC drivers and had the same error.

Any advice you may have is appreciated.

On 1 Aug 2005 23:06:26 -0700, shaw.marsh@gmail.com wrote:

>Hi,
>
>We are experiencing "Automation Error: The object involved has
>disconnected from itsd client" errors when executing the following VBA
>code: "Selection.QueryTable.Refresh BackgroundQuery:=False"
>The error does not occur consistently and many spreadsheets have never
>exhibited the problem. A small group of spreadsheets often show the
>problem but not at consistent times or at the same query. The
>spreadsheets were originally written with Excel 97 using Windows 2000,
>MDAC 2.6 and Oracle version 8.1.7 and ran without any such errors, only
>showing this error since we upgraded to XP SP2 and Office 2003 SP1
>against Oracle 9.2.
>We have tried different versions of MDAC; on Windows 2000 server with
>Office 2003; with Merant ODBC drivers and had the same error.
>
>Any advice you may have is appreciated.

The obvious advice to enable ODBC tracing and/or sqlnet tracing
applies. How to enable sqlnet tracing I have posted numerous times in
comp.databases.oracle.server


--
Sybrand Bakker, Senior Oracle DBA

Thanks for the reply Sybrand. We have generated trace logs - both ODBC
and Oracle, but these have not been able to help us figure out the
issue. They show that the query is submitted to ODBC but does not get
passed to the Oracle server. I am happy to post all or part of them if
anyone would like.

Right now your assertion can't be verified (due to lack of
information), so you probably need to post the logs, if you can't
disclose more.
Can you connect to Oracle at all from that system? If you can the
problem is in the ODBC layer.

--
Sybrand Bakker
Senior Oracle DBA

-----------------------------------------------------------------------------
Thanks again Sybrand.
There seems a bit of a trade off with how much information you dump in
a post up-front - we were hoping someone had experienced the or a
similar problem and knew of a solution.

We certainly can connect to Oracle (as mentioned in the original post
the problem is intermittent - so they sometimes work) and all other
applications using the databases don't have a problem - so as you
suggest we suspect it is also in the ODBC layer. Also, based on other
"automation errors" posts and Microsoft knowledge base searches, this
error would normally indicate some COM style disconnect.

Here is the ODBC trace - as you can see it terminates rather abruptly:

e 4d4-29c ENTER SQLAllocEnv
HENV * 048C4174

e 4d4-29c EXIT SQLAllocEnv with return code 0
(SQL_SUCCESS)
HENV * 0x048C4174 ( 0x048d1768)

e 4d4-29c ENTER SQLAllocConnect
HENV 048D1768
HDBC * 048C4178

e 4d4-29c EXIT SQLAllocConnect with return code 0
(SQL_SUCCESS)
HENV 048D1768
HDBC * 0x048C4178 ( 0x048d1810)

e 4d4-29c ENTER SQLSetConnectOption
HDBC 048D1810
UWORD 103 <SQL_LOGIN_TIMEOUT>
SQLULEN 45

e 4d4-29c EXIT SQLSetConnectOption with return code 0
(SQL_SUCCESS)
HDBC 048D1810
UWORD 103 <SQL_LOGIN_TIMEOUT>
SQLULEN 45

e 4d4-29c ENTER SQLDriverConnectW
HDBC 048D1810
HWND 002D02AA
WCHAR * 0x1F7F8B88 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7F8B88
SWORD 8
SWORD * 0x00000000
UWORD 1 <SQL_DRIVER_COMPLETE>

e 4d4-29c EXIT SQLDriverConnectW with return code 1
(SQL_SUCCESS_WITH_INFO)
HDBC 048D1810
HWND 002D02AA
WCHAR * 0x1F7F8B88 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7F8B88
SWORD 8
SWORD * 0x00000000
UWORD 1 <SQL_DRIVER_COMPLETE>

DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed (0)

e 4d4-29c ENTER SQLAllocStmt
HDBC 048D1810
HSTMT * 048C417C

e 4d4-29c EXIT SQLAllocStmt with return code 0
(SQL_SUCCESS)
HDBC 048D1810
HSTMT * 0x048C417C ( 0x048d21c8)

e 4d4-29c ENTER SQLExecDirectW
HSTMT 048D21C8
WCHAR * 0x04392824 [ 841] "
SELECT BUSTRUCT.CODE, DEALS.DEAL_DT, Sum(QQR_DPV.END_MTOM)\ d\ aFROM
QUANTUM.BUSTRUCT BUSTRUCT, QUANTUM.DEALS DEALS, QUANTUM.QQR_DPV
QQR_DPV, QUANTUM.QQR_POOL_RUN QQR_POOL_RUN, QUANTUM.SECTYPE SECTYPE,
QUANTUM.SWDEALS SWDEALS\ d\ aWHERE BUSTRUCT.THEKEY = DEALS.ENTITY AND
DEALS.DEAL_NO = SWDEALS.DEAL_NO AND SECTYPE.THEKEY = DEALS.SECTYPE AND
QQR_POOL_RUN.ENTITY_ID = BUSTRUCT.THEKEY AND QQR_POOL_RUN.INSTRUMENT_ID
= SECTYPE.THEKEY AND QQR_POOL_RUN.POSITION_DATE = DEALS.DEAL_DT AND
QQR_DPV.RUN_ID = QQR_POOL_RUN.RUN_ID AND QQR_DPV.ENTITY_ID =
QQR_POOL_RUN.ENTITY_ID AND QQR_DPV.INSTRUMENT_ID =
QQR_POOL_RUN.INSTRUMENT_ID AND QQR_DPV.DEAL_NO = DEALS.DEAL_NO AND
((BUSTRUCT.CODE In ('CASHFUND')) AND (SECTYPE.TRANSTYPE='SI') AND
(DEALS.DEAL_DT>{ts '2003-12-31 00:00:00'}))\ d\ aGROUP BY
BUSTRUCT.CODE, DEALS.DEAL_DT\ d\ aORDER BY BUSTRUCT.CODE,
DEALS.DEAL_DT"
SDWORD 841