Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lgp16-0004x7-Bq for pgsql-odbc@arkaria.postgresql.org; Wed, 12 May 2021 13:35:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lgp14-0001rf-0Z for pgsql-odbc@arkaria.postgresql.org; Wed, 12 May 2021 13:35:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lgp13-0001qb-59 for pgsql-odbc@lists.postgresql.org; Wed, 12 May 2021 13:35:09 +0000 Received: from mail-qb1can01on060a.outbound.protection.outlook.com ([2a01:111:f400:fe5c::60a] helo=CAN01-QB1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lgp0z-0005XI-Ld for pgsql-odbc@lists.postgresql.org; Wed, 12 May 2021 13:35:07 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=if9e8cK+SE5/InN13jAvOfNBq2Qvguch98lWC94SM5/Hs0Z4DcNEuE2gQSTgQgNk/jZA1VARKqIwT3WoserOGe9ZkIs9XA7AgsyqFen42gY/acLCqD/TczSEZlgYeLsW1Vr5LaQZb4wPiP7jixMWCobcNgQ0ae2MjKSWNsgaaNoHzMp9s3QGU/gy5B/nhYfnkunSrGT1p9sCeNm1Ds+fUj0ZWqR6Ur/dcXW+2QJJ7IGW1xuaESIsR3LnDk1E1NZSMV8OUwa5GVHSacIZK1WlboC83BjoXd4D+zD4vPrmm0UdbTfc1R/cdWL+mDJ114kn9I80NN8UbKJ5nTOHn5CqaA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=bqc0ANHEWK5E3A4nyRnbyORCnVA93WGV5lbjlnwnfH4=; b=Z89/5951pwFRxwdshpiS+mzQH/ypTdlyoBFINQfIy5FY6X6i+xKprqvBYpTA+w4M+xr/n580wFzQStIGahWkqlD+ODzkvS3gxJn3SIxX/Hg2RAadbelGSyZreZhzsOUxJRDvdSYYfYX6/PLcwlGWO3cAOYOQzsU7CbQ8EjFn2dGNJGG9j212iELr8UOF1p4JYZL0H0M9dW6LpclKhtkZ6aNvA3ffElInp0Q6A9LWWYo7UAaQng7KzXw+v5Qe83J1P05cYfR7V1oU69H3RrvOX/5qB4TUlYrsG8GCrTrb3Sf5Fb2vEbgZikYfV5919EJZKHkD2/0y/R60xuYz4d1ZJw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=axper.com; dmarc=pass action=none header.from=axper.com; dkim=pass header.d=axper.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=axper.onmicrosoft.com; s=selector2-axper-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=bqc0ANHEWK5E3A4nyRnbyORCnVA93WGV5lbjlnwnfH4=; b=L+15F6fOEadKhzCnVt9XexTiRWDKDTljS2EV6d7sXQog6DggCvuMaJRuJhEsaavTg4rRteCUTZYzEBUBGVMt754wpFojYm6hd/LQNHfwEffUE426YlH0N1ZplCcGVUQ6wtQsmYnHXgDe6tk4WL3I2tePAPex/XPoSMgls7h/TU8= Received: from YQBPR0101MB1299.CANPRD01.PROD.OUTLOOK.COM (2603:10b6:c00:f::28) by QB1PR01MB3521.CANPRD01.PROD.OUTLOOK.COM (2603:10b6:c00:3a::11) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.4108.31; Wed, 12 May 2021 13:35:01 +0000 Received: from YQBPR0101MB1299.CANPRD01.PROD.OUTLOOK.COM ([fe80::e8f4:91d:806b:8ef5]) by YQBPR0101MB1299.CANPRD01.PROD.OUTLOOK.COM ([fe80::e8f4:91d:806b:8ef5%5]) with mapi id 15.20.4108.032; Wed, 12 May 2021 13:35:01 +0000 From: David Parenteau To: "Inoue,Hiroshi" CC: "pgsql-odbc@lists.postgresql.org" Subject: Re: ODBC Calling Fill - Unexptected closed connection after 2 hours Thread-Topic: ODBC Calling Fill - Unexptected closed connection after 2 hours Thread-Index: AQHXReAepyYLSWq2iEmmXGx+oKB2JKrfAcMAgADYbBs= Date: Wed, 12 May 2021 13:35:01 +0000 Message-ID: References: , In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: gmail.com; dkim=none (message not signed) header.d=none;gmail.com; dmarc=none action=none header.from=axper.com; x-originating-ip: [208.88.110.10] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 13b97390-b80b-4a22-c7ba-08d9154abe43 x-ms-traffictypediagnostic: QB1PR01MB3521: x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:10000; x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: BT38FCY2zVLi0k02o6toe/KxI75hlOko7cXROvlJaz/qZsukcRCdasQFs5BAjHPfOksZqzErhbFIzw1URF3ysP6QXvBUIy8rAqG2Hgv4bTwFqjZsts3r4LhlYs9DfW4kke+Jrr2PylX9dL8kmoxrHLzCM0qfSXfG6nr+XIqG4hmdNm6XrLdHBRCSP5/xdW02UlQtbH9VE9WbC2Z9/ssJOsnSa8LSfImxEk9d/YT3Do+a7NfvEG1Ng6uKzMqZjpgWbXqnmyTH084M7tQ3bArITbxmr5DCcMNeDKkNj38JW4jbNlajNS5UQCaUbZeSovRnsxvLcSK5qAXnx4i2XZU/BCu8otuxaPP3TrCpWoqvZE7w2jdaFfIAINZTJhdR8fumeyLyJSgv0Bp8a/lBvvwI2rBpCsaqpagmhcBCuzUZJOPGVJou/HNTiNG8WG1ZNsZH9qEecI5Wz/WwOXNhPyvF1kqH2sNiOlcD8cAubAXrtMpA6RDU8RCLFZ4qzxpIuFKEziRmlbzXLztVB78wNp3VjAwtBP2eq+847eOzPjmH1yyOQgXBUMhszishKUAYLvC17Nz2RROK9YMElojl6nm146gwTeR7N+i8DUd37kQii6k= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:YQBPR0101MB1299.CANPRD01.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(396003)(39840400004)(346002)(366004)(376002)(136003)(33656002)(86362001)(53546011)(6916009)(2906002)(83380400001)(71200400001)(38100700002)(76116006)(64756008)(66556008)(8936002)(4326008)(66946007)(66476007)(66446008)(122000001)(5660300002)(478600001)(26005)(8676002)(316002)(7696005)(52536014)(19627405001)(6506007)(55016002)(9686003)(186003);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata: =?iso-2022-jp?B?YW5mVXpLMDFDZjlyUitDUlBuTGNNdTJMelIvTjNtZEVRcHEwcG1sNDEx?= =?iso-2022-jp?B?Z3hkRkgwWFhldEtDc2srNThKTk5BZXhHa1lITERUL2pEak5MdG5sWC9y?= =?iso-2022-jp?B?cXE5WDVEdzVXUnFYWGN6RzRNY0YvT1AzWlBlTVlVa1ZSN1Z5N3VteEIr?= =?iso-2022-jp?B?Q0M2TCtIODdvYUVRK1N4cXdXMFlBdnlZa1ZBZ1BDUDdzbk9na1MwbUFl?= =?iso-2022-jp?B?ZXpCVVlzTkRUQjVOTDFRQTRoVU1SSjN5Rm02aGF5S1JxSjF6M3NmdHBs?= =?iso-2022-jp?B?K0xmenN6SGVIV3J2NmhGUUhaSndUVEpibUMwamY5YVdVZzZBaWRhUVhJ?= =?iso-2022-jp?B?eU9vaWdoZXVLWEVFVklSTjJnUUV5a2NlcVVaZUpvY2NJVG4zNEJYN3lO?= =?iso-2022-jp?B?WFV2VXZIUFA4aDlZSDFRK3d5UkxZYVVtcEN5THh0dGhhRW12bjVBd0FL?= =?iso-2022-jp?B?dkRHb2Z3VHJSeEFUTmptQzVIbTdSS2liVktoZ1VHMWwvdncreFZ4NHJp?= =?iso-2022-jp?B?WEUzS3ovd1FtY0gvSnlaLzM5N3BBSE12RS9lVEVRbXBRNFZUZGFWSUhF?= =?iso-2022-jp?B?cURjcjZGM1MyOEZTdGdtVjNHdnFOWlpnYkY1a0NsWnJQYkRTQXJ2SVQ3?= =?iso-2022-jp?B?OUZ0RWd3OHBuV05oN0xzRm9kTFJpbHFKWjNscEg1UTl4aVpUaGlOZnBt?= =?iso-2022-jp?B?clNrbi9mZXlkaDVpU25uUWVvWjJwdThFY0N0eDh3NzVETmZ5WUNVZzF1?= =?iso-2022-jp?B?dFE0cjc5elRuSEwyS0w1aEJReHJCQ2U4dnhqZTVXOVNNSnVaMU14Z0pC?= =?iso-2022-jp?B?bjAwd0VzOXdUZ3d3Y1VUdHJod0sweXRSRlk1UFNIckVQWjU1V29kQkh0?= =?iso-2022-jp?B?SUszQ2RFc3VxT3NlNGFwYm5vR3JnaEZtVGlGaFRBdldMNWhnUEpHZ2xU?= =?iso-2022-jp?B?S0xyOGpIb3ZwcVUxVjBxWngwQ0xsaUw3cC96OG5selY2ODdKbmlHVTVT?= =?iso-2022-jp?B?NTEwN296cXhmOVhGK2Njd3F0NVhERGV5ajNBSEpMQUUvM2JSMnZtWTFv?= =?iso-2022-jp?B?YnkvTU1vd0RSNnNNOHhNRUVjK1lnQzVLQlBSUEhhTnAyc3pBaGhZOGJs?= =?iso-2022-jp?B?QWpKSElIaUJYbFRjZUxDOUdkLzdWVFFHci9Pc3YzdklLZWtEZzBHd2o1?= =?iso-2022-jp?B?SmFQSUtiUnJNNzAwSmIyODYxSmR1RHFwQkp1NjJiZW9FMzVaaVlzbVhy?= =?iso-2022-jp?B?bDB6SGtXa1JDTEQvZGlGL2haMCtmdVlFdFdzeHNBTmFHSm41MDhRbW9I?= =?iso-2022-jp?B?Z000MTAzSmtLa2xHM1pvdkRBbDR1NlNpS0Y2VllLYVZkcGpxeEJMaWRR?= =?iso-2022-jp?B?RTZiREtJQitlaDNFOXRvN2syclQ1VEU2aklBS2NvR1g0WTZnWW81Y1ZP?= =?iso-2022-jp?B?cndkTXlIWnhkZThGNW9tL0dWUnVPTm1lT0kwRUFjcTVzdXBoNFlkVVpR?= =?iso-2022-jp?B?TGw0Mjl6aXVSbXpkRCtWWFc3emZ3eTZuT1MrZ3RNU01XZnNET2M0SVUr?= =?iso-2022-jp?B?M0QwendidnRtejJFdlM1cXhhRDJIRFdRVFVtNU5GbEg1bkZiT1ZxaDh1?= =?iso-2022-jp?B?RmtCTkhaUnBlMThKeGU0NmN1NmpmZDVDMHd5dTFaUjRPMkVqd3U4SmtY?= =?iso-2022-jp?B?ZE45QTNUaFRDMVA4OHk5NWZzU2RxbjBlRjhvandtVXVsOWtSRXBSRmsy?= =?iso-2022-jp?B?TDZGYVcyQkNGbGRvV25zVWZMa2tRd3k1Q0NFK2hSVmFsb3pWMWtCRlE5?= =?iso-2022-jp?B?bks5ZG5VcTdSRUt5MU5HTGNZaCtJb09ONjZJbzFEeWxSYm9zbTZvSEU4?= =?iso-2022-jp?B?bWg4aENOUU9UaFR6ZkFpbDRLMkFzPQ==?= x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_YQBPR0101MB1299B690D4A46C326CE16202C2529YQBPR0101MB1299_" MIME-Version: 1.0 X-OriginatorOrg: axper.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: YQBPR0101MB1299.CANPRD01.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 13b97390-b80b-4a22-c7ba-08d9154abe43 X-MS-Exchange-CrossTenant-originalarrivaltime: 12 May 2021 13:35:01.2242 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 25a5f507-f32a-4534-ba73-9466da238c43 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: 2NsTAPqgpOb393cHAIyTB4hpi8/FHZM/tfhlZQ2DROZbiYIfT8VVAJtwQTIp2YsF9qoGJjfjaJmWY3H5R8qzew== X-MS-Exchange-Transport-CrossTenantHeadersStamped: QB1PR01MB3521 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_YQBPR0101MB1299B690D4A46C326CE16202C2529YQBPR0101MB1299_ Content-Type: text/plain; charset="iso-2022-jp" Content-Transfer-Encoding: quoted-printable Actually, I already have these timeout limits in my code for a while. My qu= ery takes less than 10 minutes for all execution in the past. $connection.ConnectionTimeout =3D 3600 #10 minutes $cmd.CommandTimeout=3D3600; #10 minutes The Keepalive 120 on the ODBC might have worked since the today execution r= un first time but in less than 5 minutes. Let's see what arrives then if lo= nger than 5 minutes (might be an other default timeout limit triggering my = 2hrs problem randomly...) Thanks, David Parenteau. ________________________________ From: Inoue,Hiroshi Sent: Tuesday, May 11, 2021 8:35 PM To: David Parenteau Cc: pgsql-odbc@lists.postgresql.org Subject: Re: ODBC Calling Fill - Unexptected closed connection after 2 hour= s Hi David, How about increasing the CommandTimeout(default 30sec)? $cmd.CommandTimeout =3D 300, for example. regards, Hiroshi Inoue 2021=1B$BG/=1B(B5=1B$B7n=1B(B11=1B$BF|=1B(B(=1B$B2P=1B(B) 6:14 David Parent= eau >: Using PostgreSQL 12.3 (Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, c= ompiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit I use driver PostgresSQL Unicode(x64) version 13.00.00.00 I have a query that is executed through an ODBC connection in a Powershell = 5.1 script. I use the Fill() method to retreive about 3500 records daily. W= hen the script works, it takes 2-5 minutes to execute and retrieve data. Probleme is that the script "fails" half of the time approx. When this occu= rs, the powershell script does stop only after 2h and 30 seconds. We double checked the postgres logs and when this occurs, we see that the q= uery successfully completed within 6 minutes always. I don't know what to = look for. any Idea? Below is the error got: Executed as user: NT Service\SQLSERVERAGENT. A job step received an error a= t line 94 in a PowerShell script. The corresponding line is '(New-Object sy= stem.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null '. Correct the = script and reschedule the job. The error information returned by PowerShell= is: 'Exception calling "Fill" with "1" argument(s): "The connection has be= en disabled." '. Process Exit Code -1. Not too familiar with postgreSQL. Thanks! --_000_YQBPR0101MB1299B690D4A46C326CE16202C2529YQBPR0101MB1299_ Content-Type: text/html; charset="iso-2022-jp" Content-Transfer-Encoding: quoted-printable
Actually, I already have these timeout limits in my code for a while. My qu= ery takes less than 10 minutes for all execution in the past.

$connection.ConnectionTimeout =3D 3600 #10 minutes
$cmd.CommandTimeout=3D3600; #10 minutes

The Keepalive 120 on the ODBC might have worked since the today execution r= un first time but in less than 5 minutes. Let's see what arrives then if lo= nger than 5 minutes (might be an other default timeout limit triggering my = 2hrs problem randomly...)

Thanks,

David Parenteau.



From: Inoue,Hiroshi <h= inoue205@gmail.com>
Sent: Tuesday, May 11, 2021 8:35 PM
To: David Parenteau <dparenteau@axper.com>
Cc: pgsql-odbc@lists.postgresql.org <pgsql-odbc@lists.postgresql.= org>
Subject: Re: ODBC Calling Fill - Unexptected closed connection after= 2 hours
 
Hi David,

How about increasing the CommandTimeout(default 30sec)?
$cmd.CommandTimeout =3D 300, for example.

regards,
Hiroshi Inoue

2021=1B$BG/=1B(B5=1B$B7n=1B(B11=1B$= BF|=1B(B(=1B$B2P=1B(B) 6:14 David Parenteau <dparenteau@axper.com>:
Using PostgreSQL 12.3 (Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc-linux-g= nu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
I use driver PostgresSQL Unicode(x64)  version 13.00.00.00


I have a query that is executed through an ODBC connection in a Powershell = 5.1 script. I use the Fill() method to retreive about 3500 records daily. W= hen the script works, it takes 2-5 minutes to execute and retrieve data.
Probleme is that the script "fails" half of the time approx. When= this occurs, the powershell script does stop only after 2h and 30 seconds.=  
We double checked the postgres logs and when this occurs, we see that the q= uery successfully completed within 6 minutes always.  I don't know wha= t to look for. any Idea?

Below is the error got:

Executed as user: NT Service\SQLSERVERAGENT. A job step received an error a= t line 94 in a PowerShell script. The corresponding line is '(New-Object sy= stem.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null  '. Correct= the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "Fill= " with "1" argument(s): "The connection has been disabl= ed."  '.  Process Exit Code -1.

Not too familiar with postgreSQL.

Thanks!
--_000_YQBPR0101MB1299B690D4A46C326CE16202C2529YQBPR0101MB1299_--