Free SQL Help for Devs, DBAs, and the Curious

In today's data driven world, the ability to seamlessly interact with various Database Management Systems (DBMS) is essential. Over the years, database providers have developed different SQL dialects which can make the interaction between different DBMS challenging. This blog post will walk you through one such scenario, executing distributed queries between SQL Server and PostgreSQL.

Let's start with a brief overview of distributed queries. Essentially, a distributed query is a SQL statement that accesses data from multiple DBMS. SQL Server uses a technology known as Linked Servers for this purpose. A linked server allows SQL Server to execute commands against OLE DB data sources on remote servers, which include other instances of SQL Server, Oracle databases, Excel workbooks, and even text files.

To begin with, let's create a linked server object to a PostgreSQL database. For this, we would be using SQL Server Management Studio (SSMS). The first step is to install an OLE DB provider for the PostgreSQL database. One such provider is the PostgreSQL OLE DB Provider from PGNP, which supports both 32-bit and 64-bit architectures.

-- Creating a linked server to PostgreSQL
EXEC sp_addlinkedserver 
   @server = N'PG_LINK', 
   @srvproduct = N'PostgreSQL', 
   @provider = N'PGNP.1', 
   @datasrc = N'hostname', 
   @catalog = N'database_name';

Once the linked server is created, we can now execute SQL commands against the PostgreSQL database. The OPENQUERY function is used to initiate the distributed query. The first parameter of this function is the linked server name, and the second parameter is the SQL statement to be executed.

-- Querying data from PostgreSQL
SELECT * 
FROM OPENQUERY(PG_LINK, 'SELECT * FROM public."Customers"');

Now, if you need to execute a distributed transaction that needs to write data into both SQL Server and PostgreSQL, you can use the BEGIN DISTRIBUTED TRANSACTION statement. This ensures that either all the operations are successful, or none of them affects the databases.

-- Starting a distributed transaction
BEGIN DISTRIBUTED TRANSACTION
-- Insert into SQL Server
INSERT INTO SQLServerDB.dbo.Orders (OrderID, CustomerID)
VALUES (1, 'CUST123');

-- Insert into PostgreSQL
EXEC('INSERT INTO public."Orders" ("OrderID", "CustomerID") VALUES (1, ''CUST123'');') AT PG_LINK;
COMMIT TRANSACTION;

In conclusion, SQL Server provides a robust mechanism for executing distributed queries and transactions across different DBMS. The key is to configure the linked server correctly and to ensure that the appropriate OLE DB provider is installed. Whether you are working with PostgreSQL, MySQL, DB2, or Azure SQL, SQL Server has the ability to interact with all these databases seamlessly.