How do I setup a linked server from MS SQL Server 2k to Postgres?

Posted on 2009-04-07
Medium Priority
Last Modified: 2013-12-25
I need to know the parameters to setup a linked server between MS SQL Server 2k and Postgres 8.3. More specifically, the provider, data source, provider string, and location that SQL Server asks for in the linked server properties dialog.
Question by:lukeinjax
  • 2
LVL 12

Accepted Solution

udaya kumar laligondla earned 500 total points
ID: 24088271

EXEC sp_droplinkedsrvlogin @rmtsrvname = 'PostgreSQL', @locallogin = NULL

EXEC sp_DropServer 'PostgreSQL'

EXEC sp_AddLinkedServer
@server = 'PostgreSQL',
@srvproduct = 'Microsoft OLE DB Provider for ODBC Driver',
@provider = 'MSDASQL',
@datasrc = 'PostgreSQL', -- a previously created and configured ODBC data
@location = 'localhost',
@catalog = 'public'

EXEC sp_AddLinkedSrvLogin
@rmtsrvname = 'PostgreSQL',
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = 'postgre', -- User and password created in PostgreSQL pgAdmin
@rmtpassword = 'password'
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24088278

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question