search

tds-fdw / tds_fdw

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

foreign data wrapper issue for windows #260

Closed OmPrakash-52 closed 4 years ago

OmPrakash-52 commented 4 years 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

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 outputs of running git log --source -n 1 on your git clone from a console

Version of PostgreSQL

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

Version of FreeTDS

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

From a console:

Logs

Please capture the logs when the error you are reporting is happening

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

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

i am using ,

  1. Postgres 12
  2. Microsoft SQL SERVER 2014
  3. Microsoft SQL SERVER MANAGEMENT STUDIO 2018

Query

CREATE EXTENSION tds_fdw;

CREATE SERVER mssql_svr FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'localhost', port '1433', database 'test' );

CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username 'vishal', password 'vishal');

CREATE FOREIGN TABLE employee ( Id integer, Name varchar (50)) SERVER mssql_svr OPTIONS (schema_name 'dbo', table_name 'employee');

These four queries are excueting but when i exceute this query ,

select * from employee;

it gives me this #error

ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist (localhost), OS #: 10061, OS Msg: Unknown error, Level: 9 SQL state: HV00N

juliogonzalez commented 4 years ago

Well, the error speaks for itself: Server is unavailable or does not exist (localhost)

Seems FreeTDS is basically unable to connect to localhost:1433. Either MSSQL is down, either it's not listening localhost for some reason, either there's a strange firewall issue, either MSSQL is not on localhost but on other instance, either Windows does not understand localhost (would be strange but...)

OmPrakash-52 commented 4 years ago

Well, the error speaks for itself: Server is unavailable or does not exist (localhost)

Seems FreeTDS is basically unable to connect to localhost:1433. Either MSSQL is down, either it's not listening localhost for some reason, either there's a strange firewall issue, either MSSQL is not on localhost but on other instance, either Windows does not understand localhost (would be strange but...)

Thanks for your reply @juliogonzalez ,ya you are right my mssql is not using the port no 1433 so i simply configure my mssql server port no to 1433 and restart the server and now it is working

OmPrakash-52 commented 4 years ago

STEPS TO SOLVE THE ISSUE

  1. Right click on my pc\this pc
  2. There will be a option called manage(Enter)
  3. In Mange u will see a option called Services and Application
  4. Expand Services and Application , and see there will be a option called Protocols for SQLExpress
  5. Under that option as u will click on the option on the right hand side of the panel u will get three option ->Shared Memory ->Named Pipes ->TCP/IP

NOTE:-SEE THAT ALL ARE ENABLE

  1. Go to TCP\IP option and right click on it go to properties and see the last category called ipall
  2. First make the value of TCP\IP dynamic port to blank(Dont make it to zero).If the TCP\IP port is set to 1433 then leave it ,dont change....... Otherwise if the TCP\IP port feild is blank then enter the port no and restart the server

NOTE:- SOMETIMES PORTNO 1433 IS ALREADY BEING USED BY SOME OTHER APPLICATION SO U CAN TRY IT WITH DIFFERENT PORT NO (eg:1434) AND RESTART THE SERVER