SQL Server (Linked Server)

You can query QuickBooks data directly from Microsoft SQL Server by setting up Syntra ODBC as a linked server. This uses the MSDASQL provider (the built-in ODBC bridge in SQL Server) to connect through your Syntra ODBC data source.

Prerequisites

• Syntra ODBC installed and running
• An ODBC Data Source (DSN) configured (see ODBC Driver Setup)
• Microsoft SQL Server with MSDASQL provider available

Setting Up the Linked Server

Option A: Using T-SQL

Step 1: Create the Linked Server

Run the following in SQL Server Management Studio (SSMS) or any T-SQL client. The @datasrc value must match the name of your ODBC Data Source (DSN) exactly as it appears in the ODBC Data Source Administrator.

EXEC sp_addlinkedserver
    @server = N'SYNTRA',
    @srvproduct = N'',
    @provider = N'MSDASQL',
    @datasrc = N'Syntra QuickBooks';

Important: Use MSDASQL as the provider. Other providers (such as SSISOLEDB) will not work correctly with Syntra ODBC.

Note: @datasrc is the ODBC Data Source Name (DSN) you created during ODBC Driver Setup. If you named your DSN something different (e.g., SyntraODBC), use that name here instead.

Step 2: Configure Login Mapping

Map SQL Server logins to the Syntra credentials:

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'SYNTRA',
    @useself = N'FALSE',
    @rmtuser = N'sa',
    @rmtpassword = N'';

Set @rmtuser and @rmtpassword to match the credentials in your Syntra config.toml (default username is qbconnect).

Step 3: Enable RPC

Enable remote procedure calls so SQL Server can execute write operations on the linked server:

EXEC sp_serveroption @server = N'SYNTRA', @optname = 'rpc out', @optvalue = 'true';

Option B: Using SSMS GUI

1. Connect to your SQL Server in SSMS
2. Expand Server Objects > right-click Linked Servers > New Linked Server…

General Tab:

Field	Value
Linked server	SYNTRA
Server type	Other data source
Provider	Microsoft OLE DB Provider for ODBC Drivers
Product name	PostgreSQL
Data source	Your DSN name (e.g., Syntra QuickBooks)

Security Tab:

• Select Be made using this security context
• Remote login: sa
• With password: (leave blank)

Server Options Tab:

Option	Value
RPC	True
RPC Out	True

Click OK, then right-click the new SYNTRA linked server > Test Connection to verify.

Verifying the Connection

After setup, expand SYNTRA > Catalogs > qbconnect > Tables in the SSMS Object Explorer. You should see every QuickBooks table listed under the public schema (public.customers, public.invoices, public.accounts, and so on).

-- Quick connectivity test
EXEC sp_testlinkedserver N'SYNTRA';

Reading Data

Basic Queries

Use OPENQUERY to run SQL queries against QuickBooks from T-SQL:

-- List customers
SELECT * FROM OPENQUERY(SYNTRA, 'SELECT * FROM customers LIMIT 10');

-- Browse inventory items
SELECT * FROM OPENQUERY(SYNTRA, 'SELECT * FROM item_inventories LIMIT 100');

-- View invoices
SELECT * FROM OPENQUERY(SYNTRA,
    'SELECT ref_number, txn_date, customer_ref_full_name, balance_remaining
     FROM invoices LIMIT 10');

-- List accounts
SELECT * FROM OPENQUERY(SYNTRA, 'SELECT * FROM accounts LIMIT 10');

Filtering

Push filters inside the OPENQUERY call for best performance. Syntra pushes these directly to the data source:

-- Customers with a balance
SELECT * FROM OPENQUERY(SYNTRA,
    'SELECT full_name, balance FROM customers WHERE balance > 0');

-- Invoices for a specific customer
SELECT * FROM OPENQUERY(SYNTRA,
    'SELECT ref_number, txn_date, balance_remaining FROM invoices
     WHERE customer_ref_full_name = ''Acme Corporation''');

-- Unpaid invoices
SELECT * FROM OPENQUERY(SYNTRA,
    'SELECT ref_number, customer_ref_full_name, balance_remaining
     FROM invoices WHERE is_paid = false');

Joining with Local SQL Server Tables

Combine QuickBooks data with your local SQL Server tables:

-- Match QuickBooks customers with local orders
SELECT
    o.order_id,
    o.order_date,
    qb.full_name,
    qb.balance
FROM dbo.orders AS o
INNER JOIN OPENQUERY(SYNTRA, 'SELECT full_name, balance FROM customers') AS qb
    ON o.customer_name = qb.full_name;

-- Enrich local invoices with QB line item details
SELECT
    local_inv.invoice_number,
    qb_lines.item_ref_full_name,
    qb_lines.quantity,
    qb_lines.rate,
    qb_lines.amount
FROM dbo.local_invoices AS local_inv
INNER JOIN OPENQUERY(SYNTRA, 'SELECT txn_id, item_ref_full_name, quantity, rate, amount FROM invoice_lines') AS qb_lines
    ON local_inv.qb_txn_id = qb_lines.txn_id;

Four-Part Naming

You can also query using the four-part name syntax. Note that this pulls all rows before filtering locally, so OPENQUERY with server-side filters is preferred for large tables:

SELECT TOP 10 * FROM SYNTRA...customers;
SELECT TOP 5 * FROM SYNTRA...accounts;

Aggregates and Analytics

-- Customer balances summary
SELECT * FROM OPENQUERY(SYNTRA,
    'SELECT full_name, balance FROM customers WHERE balance > 0 ORDER BY balance DESC');

-- Invoice totals by customer
SELECT * FROM OPENQUERY(SYNTRA,
    'SELECT customer_ref_full_name, COUNT(*) AS invoice_count, SUM(subtotal) AS total
     FROM invoices GROUP BY customer_ref_full_name');

Writing Data

Write operations use EXEC ... AT SYNTRA syntax and go directly to QuickBooks in real time.

Creating Customers

EXEC ('INSERT INTO customers (name, phone, email)
       VALUES (''Acme Corporation'', ''555-0100'', ''billing@acme.com'')') AT SYNTRA;

Creating Transactions with Line Items

QuickBooks requires at least one line item when creating transactions. Use the line_ prefix to include line items inline:

-- Create an invoice with a line item
EXEC ('INSERT INTO invoices
    (customer_ref_full_name, txn_date, ref_number, memo,
     line_item_ref_full_name, line_quantity, line_rate)
    VALUES
    (''Acme Corporation'', ''2025-06-15'', ''INV-1001'', ''June consulting'',
     ''Consulting:Hourly'', ''10'', ''150.00'')') AT SYNTRA;

-- Create an estimate
EXEC ('INSERT INTO estimates
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
    VALUES
    (''Acme Corporation'', ''2025-06-01'',
     ''Widget-500'', ''5'', ''25.00'')') AT SYNTRA;

-- Create a sales receipt
EXEC ('INSERT INTO sales_receipts
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
    VALUES
    (''Acme Corporation'', ''2025-06-15'',
     ''Widget-500'', ''3'', ''50.00'')') AT SYNTRA;

-- Create a credit memo
EXEC ('INSERT INTO credit_memos
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
    VALUES
    (''Acme Corporation'', ''2025-06-20'',
     ''Widget-500'', ''1'', ''50.00'')') AT SYNTRA;

-- Create a purchase order
EXEC ('INSERT INTO purchase_orders
    (vendor_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
    VALUES
    (''Office Supplies Co'', ''2025-06-01'',
     ''Widget-500'', ''100'', ''10.00'')') AT SYNTRA;

-- Create a sales order
EXEC ('INSERT INTO sales_orders
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
    VALUES
    (''Acme Corporation'', ''2025-07-01'',
     ''Widget-500'', ''20'', ''45.00'')') AT SYNTRA;

Inline Line Columns

The line_ prefix maps columns to the transaction’s line entity. Common line columns:

Column	Description
line_item_ref_full_name	Item name
line_item_ref_list_id	Item by ListID
line_quantity	Quantity
line_rate	Unit price
line_amount	Total amount (overrides qty x rate)
line_desc	Line description
line_class_ref_full_name	Class assignment
line_sales_tax_code_ref_full_name	Tax code

Adding Line Items to Existing Transactions

To add additional line items to an existing transaction, insert into the line table using the parent’s txn_id:

-- First, find the invoice
SELECT * FROM OPENQUERY(SYNTRA,
    'SELECT txn_id, ref_number FROM invoices
     WHERE customer_ref_full_name = ''Acme Corporation''');

-- Then add a line item (replace with actual txn_id)
EXEC ('INSERT INTO invoice_lines (txn_id, item_ref_full_name, quantity, rate)
       VALUES (''TXN-12345'', ''Consulting:Hourly'', ''5'', ''200.00'')') AT SYNTRA;

Updating Records

-- Update a customer
EXEC ('UPDATE customers SET phone = ''555-0200''
       WHERE full_name = ''Acme Corporation''') AT SYNTRA;

-- Update an invoice memo
EXEC ('UPDATE invoices SET memo = ''Updated via linked server''
       WHERE ref_number = ''INV-1001''') AT SYNTRA;

Deleting Records

-- Delete transactions first, then the customer
EXEC ('DELETE FROM invoices WHERE customer_ref_full_name = ''Test Customer''') AT SYNTRA;
EXEC ('DELETE FROM customers WHERE full_name = ''Test Customer''') AT SYNTRA;

Note: QuickBooks keeps tombstone records for deleted names. You cannot reuse a deleted customer/vendor name.

Supported Transaction Types

Transaction	Read	Insert	Inline Lines	Update	Delete
Invoices	Yes	Yes	Yes	Yes	Yes
Credit Memos	Yes	Yes	Yes	Yes	Yes
Estimates	Yes	Yes	Yes	Yes	Yes
Sales Orders	Yes	Yes	Yes	Yes	Yes
Sales Receipts	Yes	Yes	Yes	Yes	Yes
Purchase Orders	Yes	Yes	Yes	Yes	Yes
Bills	Yes	Yes	No	Yes	Yes
Checks	Yes	Yes	No	Yes	Yes
Journal Entries	Yes	Yes	No	Yes	Yes
Customers	Yes	Yes	—	Yes	Yes
Vendors	Yes	Yes	—	Yes	Yes
Items	Yes	Yes	—	Yes	Yes
Employees	Yes	Yes	—	Yes	No

Removing a Linked Server

To drop an existing linked server and recreate it (useful when changing configuration):

EXEC sp_dropserver @server = N'SYNTRA', @droplogins = 'droplogins';

Then repeat the setup steps above to recreate it.

Troubleshooting

Common Errors

Error	Cause	Fix
Cannot create an instance of OLE DB provider MSDASQL	Provider not enabled	Run SSMS as Administrator, or enable MSDASQL in SQL Server’s Linked Server Providers
Login failed	Credential mismatch	Verify @rmtuser and @rmtpassword match your config.toml
OLE DB provider supplied inconsistent metadata	Wrong provider	Ensure you used MSDASQL, not SSISOLEDB or another provider
The transaction is empty (QB 3180)	Missing line items	Use line_ prefix columns to include at least one line item
The name is already in use (QB 3100)	Duplicate name	Customer/vendor names must be unique in QuickBooks
The name is already deleted (QB 3102)	Reusing deleted name	QuickBooks reserves deleted names permanently; use a different name

Recreating After Configuration Changes

If you change the ODBC DSN name or Syntra configuration, drop and recreate the linked server:

EXEC sp_dropserver @server = N'SYNTRA', @droplogins = 'droplogins';

EXEC sp_addlinkedserver
    @server = N'SYNTRA',
    @srvproduct = N'',
    @provider = N'MSDASQL',
    @datasrc = N'Syntra QuickBooks';

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'SYNTRA',
    @useself = N'FALSE',
    @rmtuser = N'sa',
    @rmtpassword = N'';

EXEC sp_serveroption @server = N'SYNTRA', @optname = 'rpc out', @optvalue = 'true';

32-bit vs 64-bit

If SQL Server is 64-bit, ensure the 64-bit ODBC DSN is configured. Use the 64-bit version of odbcad32.exe (located at C:\Windows\System32\odbcad32.exe). The 32-bit version is at C:\Windows\SysWOW64\odbcad32.exe.