Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sEmQo-003aL0-G5 for pgsql-general@arkaria.postgresql.org; Wed, 05 Jun 2024 08:55:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sEmQo-006tmU-Gi for pgsql-general@arkaria.postgresql.org; Wed, 05 Jun 2024 08:55:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sEmQn-006tmM-SC for pgsql-general@lists.postgresql.org; Wed, 05 Jun 2024 08:55:42 +0000 Received: from smtp2.us.opalstack.com ([23.106.47.103]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sEmQi-0005os-Fa for pgsql-general@lists.postgresql.org; Wed, 05 Jun 2024 08:55:40 +0000 Received: from [192.168.2.217] (syn-066-075-224-100.res.spectrum.com [66.75.224.100]) by smtp2.us.opalstack.com (Postfix) with ESMTPSA id DD667D77FC; Wed, 5 Jun 2024 08:55:32 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=getspatial.com; s=dkim; t=1717577733; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=HojO9qXn4qJjIeFHlP7rnDOQGgNUlGAnh3x5JBuhOpM=; b=QOC7SZ9Hdlm+1Mx+Yc7lxKgGN358Smls1tXYvXQW93Yc2Rk4WzYFSCwrL6qrIiTIDrvQxC 2XTFW8q6zLnjXM3NWe53oALG/hClcfz+rZkoNSk74YEo5x/9PRYmyMh0aUi3OgQ2lNsRyL uHnUjIzdqd+CEZGYga1yjGLYHpAfKmI= Content-Type: multipart/alternative; boundary="------------H6tNJCjt17j9xrc51IR9cczG" Message-ID: Date: Wed, 5 Jun 2024 01:55:29 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Unable to connect to any data source for foreign server To: Muhammad Salahuddin Manzoor Cc: pgsql-general References: Content-Language: en-US From: Russell Mercer In-Reply-To: X-Spam-Status: No, score=-0.09 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------H6tNJCjt17j9xrc51IR9cczG Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi, Getting back to this after a bit.  I am still having no luck.  I checked the settings for the postgres windows user between the new and old servers, and ensure they are matching.  The user has full control over the directory the file I want to connect to is located in. Here are the results from running ogr_fdw_info: CREATE SERVER myserver   FOREIGN DATA WRAPPER ogr_fdw   OPTIONS (     datasource '\\coib-gis\City_GIS\Data\Assessor\Current\CityofIB.xlsx',     format 'XLSX'); CREATE FOREIGN TABLE cityofib (   fid bigint,   pn bigint,   ckdigit varchar,   tra integer,   contrctcd integer,   frctint integer,   marstat varchar,   ownstat varchar,   ownname varchar,   mailaddr varchar,   zip integer,   mlchgdt integer,   stsno varchar,   stsstreet varchar,   prpcd integer,   prpdesc varchar,   cvnet integer,   cvland integer,   imps integer,   pprop integer,   applyr varchar,   xcd1 integer,   xamt1 integer,   xcd2 varchar,   xamt2 integer,   xcd3 varchar,   xamt3 integer,   doctype integer,   docno integer,   docdat integer,   mapno varchar,   origcutno integer,   origcutdt integer,   tracutno integer,   tracutdt integer,   redrftno integer,   redrftdt integer,   oldtra integer,   oldpn bigint,   acre double precision,   units integer,   zone integer,   usecode integer,   srcd varchar,   sryr integer,   taxstst varchar,   assmtyr integer,   transdt integer,   yreffct integer,   tlvgarea integer,   bedrms integer,   baths integer,   gstalls integer,   pool varchar,   view varchar,   usqft integer,   asqft integer ) SERVER myserver OPTIONS (layer 'CityofIB'); When I run the command given above to create the server, I receive the following error: ERROR:  unable to connect to data source "\\coib-gis\City_GIS\Data\Assessor\Current\CityofIB.xlsx" SQL state: HV00N This same error occurs whether I try to connect to an XLSX or even just a CSV.  Any ideas would be greatly appreciated. Thanks, Russell On 5/29/2024 3:15 AM, Muhammad Salahuddin Manzoor wrote: > Greetings, > > While configuring ORG_FWD. > I hope you are setting path like server name and absolute path to file > on your network share and network share has appropriate permissions. > > '\\remote_server\path\to\file.xlsx' on network share > > Create user mapping if required. > > check your config through > ogr_fdw_info -s "\\\\remote_server\\path\\to\\file.xlsx" -l > > To diagnose the issue you can check permissions, network > accessability, path format should include server name and absolute > file path and any compatability issues. > > Regards, > Salahuddin. > > On Wed, 29 May 2024, 14:42 Russell Mercer, wrote: > > Hi, > > I'm running into a problem connecting to a foreign server with my > Postgres database.  I am using the OGR_FDW trying to connect to an > Excel > "XLSX".  I had this database on another server, and the foreign > server > was set up and working properly there.  I am trying to change the > path > to the file which it is referencing, and I am now receiving an error. > In addition, I tried to just create a new foreign server to that same > "XLSX" file and it also gave the error of "Failed to connect to data > source". > > I used the OGR_FDW_INfo tool to make sure I had the foreign server > reference structured correctly, but it matches what I had before, and > didn't make a difference. > > I'm a bit lost as to where to look for possible solutions. One idea I > have is that perhaps it is a permissions issue with accessing other > files on the server where Postgres is installed, with the > "postgres" user. > > Some basic info: > > Server:  Windows Server 2022 > PostgreSQL 16.2 - Installed using Stack Builder > > Any information or ideas as to a solution, would be very much > appreciated. > > Thanks, > Russell > > > --------------H6tNJCjt17j9xrc51IR9cczG Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hi,

Getting back to this after a bit.  I am still having no luck.  I checked the settings for the postgres windows user between the new and old servers, and ensure they are matching.  The user has full control over the directory the file I want to connect to is located in.

Here are the results from running ogr_fdw_info:

CREATE SERVER myserver
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource '\\coib-gis\City_GIS\Data\Assessor\Current\CityofIB.xlsx',
    format 'XLSX');

CREATE FOREIGN TABLE cityofib (
  fid bigint,
  pn bigint,
  ckdigit varchar,
  tra integer,
  contrctcd integer,
  frctint integer,
  marstat varchar,
  ownstat varchar,
  ownname varchar,
  mailaddr varchar,
  zip integer,
  mlchgdt integer,
  stsno varchar,
  stsstreet varchar,
  prpcd integer,
  prpdesc varchar,
  cvnet integer,
  cvland integer,
  imps integer,
  pprop integer,
  applyr varchar,
  xcd1 integer,
  xamt1 integer,
  xcd2 varchar,
  xamt2 integer,
  xcd3 varchar,
  xamt3 integer,
  doctype integer,
  docno integer,
  docdat integer,
  mapno varchar,
  origcutno integer,
  origcutdt integer,
  tracutno integer,
  tracutdt integer,
  redrftno integer,
  redrftdt integer,
  oldtra integer,
  oldpn bigint,
  acre double precision,
  units integer,
  zone integer,
  usecode integer,
  srcd varchar,
  sryr integer,
  taxstst varchar,
  assmtyr integer,
  transdt integer,
  yreffct integer,
  tlvgarea integer,
  bedrms integer,
  baths integer,
  gstalls integer,
  pool varchar,
  view varchar,
  usqft integer,
  asqft integer
) SERVER myserver
OPTIONS (layer 'CityofIB');

When I run the command given above to create the server, I receive the following error:

ERROR:  unable to connect to data source "\\coib-gis\City_GIS\Data\Assessor\Current\CityofIB.xlsx"

SQL state: HV00N

This same error occurs whether I try to connect to an XLSX or even just a CSV.  Any ideas would be greatly appreciated.

Thanks,

Russell

On 5/29/2024 3:15 AM, Muhammad Salahuddin Manzoor wrote:
Greetings,

While configuring ORG_FWD.
I hope you are setting path like server name and absolute path to file on your network share and network share has appropriate permissions.

'\\remote_server\path\to\file.xlsx' on network share

Create user mapping if required.

check your config through
ogr_fdw_info -s "\\\\remote_server\\path\\to\\file.xlsx" -l

To diagnose the issue you can check permissions, network accessability, path format should include server name and absolute file path and any compatability issues.

Regards,
Salahuddin.

On Wed, 29 May 2024, 14:42 Russell Mercer, <rmercer@getspatial.com> wrote:
Hi,

I'm running into a problem connecting to a foreign server with my
Postgres database.  I am using the OGR_FDW trying to connect to an Excel
"XLSX".  I had this database on another server, and the foreign server
was set up and working properly there.  I am trying to change the path
to the file which it is referencing, and I am now receiving an error. 
In addition, I tried to just create a new foreign server to that same
"XLSX" file and it also gave the error of "Failed to connect to data
source".

I used the OGR_FDW_INfo tool to make sure I had the foreign server
reference structured correctly, but it matches what I had before, and
didn't make a difference.

I'm a bit lost as to where to look for possible solutions.  One idea I
have is that perhaps it is a permissions issue with accessing other
files on the server where Postgres is installed, with the "postgres" user.

Some basic info:

Server:  Windows Server 2022
PostgreSQL 16.2 - Installed using Stack Builder

Any information or ideas as to a solution, would be very much appreciated.

Thanks,
Russell



--------------H6tNJCjt17j9xrc51IR9cczG--