Sql Server 2012: Add a Linked Server to Postgresql

SQL Server 2012: Add a linked server to PostgreSQL

Ok, I found the solution.

In the pg_hba.conf file, I change the method for sending passwords from MD5 to trust. After reloading the server, my linked server connection works.

the entry is now:

Type Database User Address   Method

host all      all  x.x.x.x/x trust

In hope that help others peoples.

Error connecting PostgreSQL 9.5 (odbc) as Linked Server to SQL Server 2012

I found a solution that works for me.

The wizard in the Microsoft SQL Server Management Studio isn't helpful, I set up the connection by myself. I've tested it with selects and it works

Here's the code I used:

EXEC master.dbo.sp_addlinkedserver 
@server = N'NameShownInMSSQLSrvMgmtStudio', 
@srvproduct=N'PostgreSQL Unicode(x64)', 
@provider=N'MSDASQL', 
@provstr=N'Driver=PostgreSQL Unicode(x64);uid=postgres;Server=SERVERNAME;database=DBNAME;pwd=MyPWD;SSLmode=disable;PORT=5432'

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'postgresql',
@useself=N'True',
@locallogin=NULL,
@rmtuser=YourUser,
@rmtpassword=YourPW

SQL Server Linked Server to PostgreSQL Turkish Character Issue

The problem is that the source encoding is 8-bit Extended ASCII using Code Page 1254 -- Windows Latin 5 (Turkish). If you follow that link, you will see the Latin5 chart of characters to values. The value of the Ş character -- "Latin Capital Letter S with Cedilla" -- is 222 (Decimal) / DE (Hex). Your local server (i.e. SQL Server) has a default Collation of SQL_Latin1_General_CP1_CI_AS which is also 8-bit Extended ASCII, but using Code Page 1252 -- Windows Latin 1 (ANSI). If you follow that link, you will see the Latin1 chart that shows the Þ character -- "Latin Capital Letter Thorn" -- also having a value of 222 (Decimal) / DE (Hex). This is why your characters are getting translated in that manner.

There are a few things you can try:

1. Use sp_serveroption to set the following two options:

   EXEC sp_serveroption @server=N'linked_server_name',
                        @optname='use remote collation',
                        @optvalue=N'true';
   
   EXEC sp_serveroption @server=N'linked_server_name',
                        @optname='collation name',
                        @optvalue=N'Turkish_100_CI_AS';
   

   Not sure if that will work with PostgreSQL as the remote system, but it's worth trying at least. Please note that this requires that all remote column collations be set to this particular value: Turkish / Code Page 1254.

2. Force the Collation per each column:

   SELECT [ACCOUNTNUM], [NAME] COLLATE Turkish_100_CI_AS
   FROM   OPENQUERY(CARGO, 'SELECT taxno AS ACCOUNTNUM, title AS NAME FROM view_company');
   

3. Convert the string values (just the ones with character mapping issues) to VARBINARY and insert into a temporary table where the column is set to the proper Collation:

   CREATE TABLE #Temp ([AccountNum] INT, [Name] VARCHAR(100) COLLATE Turkish_100_CI_AS);
   
   INSERT INTO #Temp ([AccountNum], [Name])
     SELECT [ACCOUNTNUM], CONVERT(VARBINARY(100), [NAME])
     FROM  OPENQUERY(CARGO, 'SELECT taxno AS ACCOUNTNUM, title AS NAME FROM view_company');
   
   SELECT * FROM #Temp;
   

   This approach will first convert the incoming characters into their binary / hex representation (e.g. Ş --> 0xDE), and then, upon inserting 0xDE into the VARCHAR column in the temp table, it will translate 0xDE into the expected character of that value for Code Page 1254 (since that is the Collation of that column). The result will be Ş instead of Þ.

UPDATE

Option # 1 worked for the O.P.

copy large table from sql server to odbc linked database (postgresql) in ssms

I ended up using the OFFSET X ROWS FETCH NEXT Y ROWS ONLY introduced in SQL Server 2012 so the complete statement looked like this:

INSERT INTO OPENQUERY(POSTGRES,'SELECT * FROM targettable')
SELECT *
FROM sourcetable
ORDER BY 1
OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY

And everything is working and error appears! I actually iterate through the OFFSET value adding 10,000 to it on every iteration using dynamic SQL.

Not the cleanest or nicest solution. I think most people would be better writing something in another language as mentioned by Michal Niklas, but this worked for me.

Add Catalog to an Existing Linked Server

You can do this by sending dynamic SQL via the linked server:

EXEC DB4_LINK.master..sp_executesql N'CREATE DATABASE foo;';

Of course this requires that you have the permissions to do so, and it's a simplistic command assuming that the default settings are fine - you may want to customize the CREATE DATABASE command.

How to insert a row into a linked server table?

INSERT INTO [TargetServer].[TestDatabase].[dbo].TestTable (Name)
SELECT Name From [SourceServer].[SourceDatabase].[dbo].[Names] where Id = 1

Does merge command work with link on different sql servers? (S: Potrgesql, T: MsSQL = MERGE)

Yes you're right, the following code worked in the small table. Giving OleDB link to large tables will solve the situation. Sorry, there is currently no free OleDB provider for PostgreSQL.

    MERGE INTO [MYTEST_STAGE1].dbo.DimOrganizationType AS t USING
      (SELECT ID,
              CompanyID,
              Name,
              LocationTypeID
       FROM POSTGRESQL35W.erp.[ERP].OrganizationType) AS s (ID, CompanyID, Name, LocationTypeID) ON t.ID=s.ID WHEN MATCHED
    AND (isnull(t.CompanyID, 0)<>isnull(s.CompanyID, 0)
         OR t.Name<>s.Name
         OR isnull(t.LocationTypeID, 0)<>isnull(s.LocationTypeID, 0)) THEN
    UPDATE
    SET t.CompanyID=s.CompanyID,
        t.Name=s.Name,
        t.LocationTypeID=s.LocationTypeID,
        t.ModifiedDate=GETDATE(),
        t.ModifiedByUser=@@SERVERNAME+'/'+@@SERVICENAME WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID,
            CompanyID,
            Name,
            LocationTypeID,
            loadDate,
            LoadByUser)
    VALUES (s.ID, s.CompanyID, s.Name, s.LocationTypeID, GETDATE(), @@SERVERNAME+'/'+@@SERVICENAME) WHEN NOT MATCHED BY SOURCE THEN
    DELETE; 

---

Related Topics