From: ScheduleReports Monitoring <scheduledreportsmonitoring@axper.com>
To: "pgsql-odbc@lists.postgresql.org" <pgsql-odbc@lists.postgresql.org>
Subject: ODBC Get-Data using FIll() gets timeout after 2 hours half of the
 time
Thread-Topic: ODBC Get-Data using FIll() gets timeout after 2 hours half of
 the time
Thread-Index: AddFtDxeOv0svYgdS2afYC7h1BUD2g==
Date: Mon, 10 May 2021 16:01:36 +0000
Message-ID: 
 <QB1PR01MB36818619C4E7797EE63A4836DB549@QB1PR01MB3681.CANPRD01.PROD.OUTLOOK.COM>
Accept-Language: fr-CA, en-US
Content-Language: en-US
Content-Type: multipart/alternative;
	boundary="_000_QB1PR01MB36818619C4E7797EE63A4836DB549QB1PR01MB3681CANP_"
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 97b17786-d581-4c37-c71e-08d913cce3de
X-MS-Exchange-CrossTenant-originalarrivaltime: 10 May 2021 16:01:36.6120
 (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: 
 BcNHA9GkSnXKGQ5DiFd9BJrsgwomDAhm/XplTsNZy8K0dkm2kU1ohXo5xk+Lvj9IgcXyF6t03KjlPeQmtqHU06xosBi1Occz+aLq/wNIOs8J4hn1J3YIHMhhHKnvgzMl
X-MS-Exchange-Transport-CrossTenantHeadersStamped: YQXPR01MB4310
List-Id: <pgsql-odbc.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-odbc@lists.postgresql.org>
List-Owner: <mailto:pgsql-odbc-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-odbc>
Archived-At: 
 <https://www.postgresql.org/message-id/QB1PR01MB36818619C4E7797EE63A4836DB549%40QB1PR01MB3681.CANPRD01.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_QB1PR01MB36818619C4E7797EE63A4836DB549QB1PR01MB3681CANP_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I'm 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 have a daily scheduled job that calls a PowerShell script. This ps1 scrip=
t do a big SELECT on an ODBC connection to postgresql server above. The que=
ry itself completes between 3 and 6 minutes and return around 3500 records.=
 We looked on the postgres logs for this.
50% of the time I can say, the job takes 2:00:30 to run and failed. However=
, in the postgres logs, we can see that the query completed in less than 6 =
minutes. Also, when it fails, it gives this:

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.

I assume the query is fine. All my other ps1 script with smaller queries an=
d ODBC Fill() seem to work fine always, event those taking 2-3 minutes.
I don't know what I should check or what could be related to this.
Any idea what we should look at?
Thanks a lot
David Parenteau

--_000_QB1PR01MB36818619C4E7797EE63A4836DB549QB1PR01MB3681CANP_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-CA" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">I&#8217;m using &nbsp;PostgreSQL 12.3 (Ubuntu 12.3-1=
.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu=
1~18.04) 7.5.0, 64-bit<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I have a daily scheduled job that calls a PowerShell=
 script. This ps1 script do a big SELECT on an ODBC connection to postgresq=
l server above. The query itself completes between 3 and 6 minutes and retu=
rn around 3500 records. We looked
 on the postgres logs for this. <o:p></o:p></p>
<p class=3D"MsoNormal">50% of the time I can say, the job takes 2:00:30 to =
run and failed. However, in the postgres logs, we can see that the query co=
mpleted in less than 6 minutes. Also, when it fails, it gives this:<o:p></o=
:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Executed as user: NT Service\SQLSERVERAGENT. A job s=
tep received an error at line 94 in a PowerShell script. The corresponding =
line is '(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | ou=
t-null&nbsp; '. Correct the script and
 reschedule the job. The error information returned by PowerShell is: 'Exce=
ption calling &quot;Fill&quot; with &quot;1&quot; argument(s): &quot;The co=
nnection has been disabled.&quot;&nbsp; '.&nbsp; Process Exit Code -1.<o:p>=
</o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I assume the query is fine. All my other ps1 script =
with smaller queries and ODBC Fill() seem to work fine always, event those =
taking 2-3 minutes.
<o:p></o:p></p>
<p class=3D"MsoNormal">I don&#8217;t know what I should check or what could=
 be related to this.
<o:p></o:p></p>
<p class=3D"MsoNormal">Any idea what we should look at?<o:p></o:p></p>
<p class=3D"MsoNormal">Thanks a lot<o:p></o:p></p>
<p class=3D"MsoNormal">David Parenteau<o:p></o:p></p>
</div>
</body>
</html>

--_000_QB1PR01MB36818619C4E7797EE63A4836DB549QB1PR01MB3681CANP_--