search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
371 stars 101 forks source link

server closed the connection unexpectedly #362

Open CR0NYM3X opened 1 month ago

CR0NYM3X commented 1 month ago

Issue report

The following information is very important in order to help us to help you. Omission of the following details cause delays or could receive no attention at all.

Operating system

On recent GNU/Linux distributions, you can provide the content of the file /etc/os-release

NAME="Red Hat Enterprise Linux"
VERSION="8.10 (Ootpa)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="8.10"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Red Hat Enterprise Linux 8.10 (Ootpa)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:8::baseos"
HOME_URL="https://www.redhat.com/"
DOCUMENTATION_URL="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8"
BUG_REPORT_URL="https://bugzilla.redhat.com/"

REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_BUGZILLA_PRODUCT_VERSION=8.10
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8.10"

Version of tds_fdw

From a psql session, paste the outputs of running \dx

If you built the package from Git sources, also paste the output of running git log --source -n 1 on your git clone from a console

postgres@postgres# \dx tds_fdw
                                           List of installed extensions
+---------+---------+--------+-----------------------------------------------------------------------------------+
|  Name   | Version | Schema |                                    Description                                    |
+---------+---------+--------+-----------------------------------------------------------------------------------+
| tds_fdw | 2.0.3   | public | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server) |
+---------+---------+--------+-----------------------------------------------------------------------------------+
(1 row)

Version of PostgreSQL

From a psql session, paste the output of running SELECT version();

postgres@postgres# SELECT version();
+---------------------------------------------------------------------------------------------------------+
|                                                 version                                                 |
+---------------------------------------------------------------------------------------------------------+
| PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit |
+---------------------------------------------------------------------------------------------------------+
(1 row)

Version of FreeTDS

How to get it will depend on your Operating System and how you installes FreeTDS

From a console:

$ rpm -qa|grep freetds
freetds-1.4.16-1.el8.x86_64
freetds-libs-1.4.16-1.el8.x86_64

Logs

Please capture the logs when the error you are reporting is happening, as well as commands with their outputs if you are reporting a problem build or installing

_For problems using tdsfdw on PostgreSQL how to do it will depend on your system, but if your PostgreSQL is installed on GNU/Linux, you will want to use tail -f with the log of the PostgreSQL cluster

For MSSQL you will need to use the SQL Server Audit Log

Replace this with the commands and outputs

Sentences, data structures, data

This will depend on the exact problem you are having and data privacy restrictions

However the more data you provide, the more likely we will be able to help

As a bare minimum, you should provide


  CREATE SERVER mssql_master FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.5.100', port '1422', database 'master'); 
  CREATE USER MAPPING FOR postgres  SERVER mssql_master OPTIONS (username 'systest', password '123123123');

CREATE FOREIGN TABLE  "new_schema_test".propiedades_instancia (
 computername text  )

    SERVER mssql_master
   OPTIONS ( row_estimate_method 'showplan_all'    ,   query 'select SERVERPROPERTY("MachineName") AS computername'    );

postgres@postgres#  select *from propiedades_instancia  ;

         server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
Time: 1222.050 ms (00:01.222)
@>?
@>?

[NOTE] -> USER systest is sysadmin , try different versions of sql and same problem on same server

 select @@version
 Microsoft SQL Server 2022 (RTM-CU13-GDR) (KB5040939) - 16.0.4131.2 (X64)   Jun 21 2024 21:57:17   Copyright (C) 2022 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor) 

Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5040940) - 14.0.3471.2 (X64)   Jun 20 2024 09:33:43   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor)
CR0NYM3X commented 1 month ago

I can make other queries but that one won't let me "select SERVERPROPERTY("MachineName") AS computername"

CR0NYM3X commented 1 month ago

I found the solution :) you need to put a data type that will return

select cast(SERVERPROPERTY('MachineName') as varchar(255) ) AS computername