Support
Screen Connect
Pay Online

SWK Technologies

Software Solutions & Services

How to Create a SQL Linked Server using a Sage ODBC

Purpose: Speed up reports currently in Sage that may run slow

George Wirtjes
Senior Consultant – Sage 100
SWK Technologies, Inc.

This walkthrough will show the steps to creating a SQL linked server in Sage 100 using a Sage ODBC to connect the databases and speed up reporting that may be running slow. An Open Database Connectivity is an API designed to help access and jointly manage separate database management systems, allowing you to export and view reports between different applications like Sage 100cloud and Excel. Follow the step-by-step guide below to get started:

Creating a SQL Linked Server with Sage ODBC Steps:

  1. Install Sage ODBC by following the image example guide below. NOTE: these examples were completed in the 64-bit (x64) version of Sage 100

sage-odbc-1sage-odbc-2

sage-odbc-3sage-odbc-4

  1. Copy the Connection String

connection-string

  1. Open SQL Studio
    1. Click on Server Objects, Linked Servers, Providers, then Right Click MSDASQL and go to Properties
    2. Ensure that “Level zero only” and “Allow inprocess” are checked

sql-studio

  1. Create a new linked server by Right Clicking on Linked Server and selecting New Linked Server

linked-server

  1. Select on General: Name the Linked server whatever you like
    1. For the provider, use Microsoft OLD DB Provider for ODBC Drivers
    2. Following the screen below, copy the string from your ODBC Setup Set the “Data source” as the ODBC name you setup

linked-server-properties

  1. Click on Server Options and ensure it looks like the screenshot below.

server-options

And that’s how you create a SQL linked server in Sage 100 using a Sage ODBC.  You can run queries off that by using the following samples and changing the server names, etc.:

  • SELECT *
  • FROM OPENQUERY([YOUR ODBC NAME], N’SELECT * FROM so_salesorderhistorydetail’)

Speak to the Sage 100 Experts for More Tips & Tricks

This is one of many tips and tricks SWK Technologies can provide to Sage ERP users. SWK is a top Sage Partner with intimate knowledge of Sage 100 and Sage 100cloud, and we leverage this experience to make sure you capture the best possible value from your software investment.

Contact SWK today to answer all of your questions on how to best use your Sage 100 or Sage 100cloud ERP.

For more than 30 years, SWK Technologies has been helping SMBs get more out of their mission-critical technology with targeted, smart solutions and advice that can ease your financial management processes so you can make faster, better business decisions. Curious to learn more? We are always happy to introduce you to the options that can help you increase agility and drive business performance. Get in touch using the form below. We will get back to you soon!

Talk to the Sage 100 Support Experts

Category: , , ,