1

I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which point I get an error like this on the SQL Server machine, and the query utterly fails:

> The XA Transaction Manager attempted to load the XA resource manager
> DLL. The call to LOADLIBRARY for the XA resource manager DLL failed:
> DLL=C:\Program Files\psqlODBC\0905\bin\pgxalib.dll, HR=%3, File=%2
> Line=%3.%0

The DLL is in fact that location, so the registry seems to be pointing to the right file. The ODBC driver is 64bit and so is my OS. "File=%2" is pointing to something on the d drive, which doesn't make sense to me, since d drive is a DVD. MSDTC is running... what am I missing?

I have toggled Linked Server Properties "Enable Promotion of Distributed Transactions for RPC" to both "True" and "False" and this doesn't change the issue and does not produce a different error.

Otherwise, scouring the Internet has brought me nothing.

Last thing to point out, my query isn't actually doing any updating - it is just pulling data. So I'm not sure why MSDTC get's invoked in the first place...

Improve this question
5
  • 3
    Possible duplicate of Security implications disabling promotion of distributed transaction for linked server
    – Hannah Vernon ♦
    May 26, 2016 at 4:08
  • 1
    Unfortunately even when I set promotion to DT for linked server to false - there were still the same error. I have no idea why. DTC runs under network service account and I have given that account permissions to postgres odbc folder. So far no errors, but I'm giving it a day before posting an answer!
    – Dina
    May 26, 2016 at 15:34
  • I seem to have resolved the error above on the SQL server machine, but the web server still logs an intermittent error (tho less frequently now): "Unknown Error Detected System.Data.SqlClient.SqlException (0x80131904): The operation could not be performed because OLE DB provider "MSDASQL" for linked server "[lkname]" was unable to begin a distributed transaction." Not sure if I should start a new question since I seem to have multiple problems here. And I'm resolving them piece at a time.
    – Dina
    May 26, 2016 at 17:15
  • The problem at the web server might be related to Kerberos authentication. Ask a separate question, and provide a link to this question if you feel it's relevant to do so. Add as much detail as you can, including instructions to repro the issue.
    – Hannah Vernon ♦
    May 26, 2016 at 17:43
  • 1
    You should probably add the T-SQL definition of the linked server to your question here (and the new question if you decide to ask one!)
    – Hannah Vernon ♦
    May 26, 2016 at 17:45

1 Answer 1

Reset to default
0

I fixed the problem by changing folder permissions on the Postgresql ODBC driver folder and giving read/execute access to Network Service. Because MS DTC runs under network service, which I confirmed by looking at the service properties.

That got rid of the error!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.