December 7, 2006 at 8:56 am
I execute an OpenQuery to fetch data from a DB2 database into the SQL
server 2000 database for further processing. This is a daily process -
and I regularly get the error message
Server: Msg 7399, Level 16, State 1, Line 4
OLE DB2 provider 'IBMDADB2' reported an error.
[OLE/DB provider returned message: SQL30081N A communication error has
been detected. Communication protocol being used: "TCP/IP".
Communication API being used: "SOCKETS". Location where the error was
detected: "171.177.121.137". Communication function detecting the
error: "connect". Protocol specific error code(s): "10061", "*", "*".
SQLSTATE=08001
]
OLE DB error trace [OLE/DB Provider 'IBMDADB2' IDBInitialize::Initialize
returned 0x80004005: ].
This brings the server down and we have to reboot. After the server is
rebooted, and I execute the query, it brings in the data without any
issues.
I executed the query from a production server, inside of a job,but as it
affected other processes with the server getting down, I moved the query
to a developement server - which is less loaded. As this also did not
work, today i tried doing it from another server, which is practically
not used for any processes, but this also failed.
There are 101 columns in the query. Here is number of rows, the query
fetched over the last week:
2006-11-30 00:00:00.000 154813
2006-12-01 00:00:00.000 114673
2006-12-02 00:00:00.000 94417
2006-12-05 00:00:00.000 236134
2006-12-06 00:00:00.000 137332
While today,I was trying to fetch 155099 rows
2006-12-07 00:00:00.000 155099
Here is the query:
SELECT *
INTO test..plastics_temp
FROM OPENQUERY([BACARDI],'SELECT
ACCT_NO,
MAIL_DT ,
CH_NO,
AVAIL_CR_AM,
CREDIT_LINE_AM,
ACCT_STS_CD,
CITY_NM,
ST_CD,
ZIP_CD,
COUNTRY_CD,
POSTAL_CD,
SSN_NO,
OPEN_DT ,
cast(BIRTH_DT AS CHAR(10)) AS BIRTH_DT,
MAIL_CD,
PMT_NOW_DUE_AM,
HOME_PHON_MAIL_NO,
WORK_PHONE_NO,
REISSUE_DT,
DLQ_30_CT,
DLQ_30_AM,
DLQ_60_CT,
DLQ_60_AM,
DLQ_90_CT,
DLQ_90_AM,
DLQ_HIST_13M_CD,
EXP_DT,
WORK_CARD,
HIGH_BAL_EVER_AM,
LST_YR_FC_AM ,
CAT1_PRMO_ID,
CAT_1_RT_TYP_CD,
CAT1_CNTRL_GRP_CD,
CAT1_START_DT,
CAT1_END_DT,
CAT1_CUR_RT_CD,
CAT1_RTN_RT_CD,
CAT1_END_BAL_AM,
CAT2_PRMO_ID,
CAT_2_RT_TYP_CD,
CAT2_CNTRL_GRP_CD,
CAT2_START_DT,
CAT2_END_DT,
CAT2_CUR_RT_CD,
CAT2_RTN_RT_CD,
CAT2_END_BAL_AM,
CAT3_PRMO_ID,
CAT_3_RT_TYP_CD,
CAT3_CNTRL_GRP_CD,
CAT3_START_DT,
CAT3_END_DT,
CAT3_CUR_RT_CD,
CAT3_RTN_RT_CD,
CAT3_END_BAL_AM,
CAT4_PRMO_ID,
CAT_4_RT_TYP_CD,
CAT4_CNTRL_GRP_CD,
CAT4_START_DT,
CAT4_END_DT,
CAT4_CUR_RT_CD,
CAT4_RTN_RT_CD,
CAT4_END_BAL_AM,
TRIAD_ATTR4_NO,
FICO_SCR_NO,
HNC_SCR_CD,
CUR_BHAV_SCOR_NO,
BKRPT_SCOR_NO,
MKT_GROUP_CD,
MKT_PRIM_ACQ_CD,
MKT_SCTR_ACQ_CD,
PLSTC_FRD_STS_CD,
PLSTC_ISSUE_RSN_CD,
MNFRM_PROCESS_DT,
CH_REC_IN,
STMT_IN,
ORIG_REPL_EXP_IN,
CAT1_ACTV_END_DT,
CAT1_ACTV_DT,
CAT2_ACTV_END_DT,
CAT2_ACTV_DT,
CAT3_ACTV_END_DT,
CAT3_ACTV_DT,
CAT4_ACTV_END_DT,
CAT4_ACTV_DT,
ACCT_TRANSP_IN,
RBRND_IN,
LST_HPHN_CHG_DT,
LST_BUS_PHN_CHG_DT,
LST_ADDR_CHG_DT,
CUR_ACCT_TP_CD,
CLMS_SCR_CD,
STS_CHG_DT,
FRAUD_ALERT_CD,
IDT_CD,
EDF_SCR_CD,
VIP_TIER_TP_NO,
CONV_ACCT_NO,
CONV_DT,
PROD_ACCT_NO,
CUSTOMER_NO,
CARD_CT
FROM usa.vru_plastics
WHERE CAST(mnfrm_process_dt AS DATE)>=''12/07/2006''
and CAST(mnfrm_process_dt AS DATE)<''12/08/2006''')
CAN ANY ONE BE ABLE TO HELP ON IT - AS TO WHY WE ARE GETTING THIS ERROR.
VJB
December 7, 2006 at 4:19 pm
It looks like it's an error at the DB2 server. Maybe the connection times out before the query is complete?
-SQLBill
December 7, 2006 at 4:37 pm
Looks like connection timeout.
Did you try to run more reasonable query within the same OPENQUERY?
_____________
Code for TallyGenerator
December 7, 2006 at 4:51 pm
There seems to be an interruption on your TCPIP connection. Are you using a wireless network?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply