Re: Excel 2003 Automation error refreshing Database queries

From: <shaw.marsh_at_gmail.com>
Date: 2 Aug 2005 19:01:53 -0700
Message-ID: <1123034513.316706.100540@g49g2000cwa.googlegroups.com>

---

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

Received on Tue Aug 02 2005 - 21:01:53 CDT