Sage 100

I need to migrate an Access backend database to SQL as many of the Sage 100 based tables are huge. I've created System DSNs to link to Sage 100 data from Access but I'm stumped on how to link to Sage 100 from SQL. Any advise?

@KathrynScofield, I follow these steps: 1) Create a DSN linked to Sage 100 including company ID and user credentials. 2) Create a Lined Server in SQL using ODBC 3) Test the linked server (use a query similar to (Select * from OpenQuery(MAS-ABC, 'Select * from GL-Account') If the above works, you can create a stored procedure to extract data using a modification of the above syntax to Select INTO a new SQL table object or alternatively, you can create a View using the above syntax and then always reference the view. Regardless, the linked server object is your SQL gateway to Sage 100 data stored in Providex.

BTW, one potential gotcha is to ensure you know what the bitness is of the your SQL server. If you have the 64 bit version of SQL installed, make sure you have the 64 bit Providex ODBC drivers on the SQL server as well. Otherwise you will not be able to make the ODBC connection from within SQL. To confirm the bitness of your SQL install, run the following query : Select @@Version. This will return a really long string. Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) The part that matters is the (X64) following the version number. In my case, I am running the 64 bit version of SQL Express.

Thanks @ShawnSlavin. I'll try this today.

@KathrynScofield let me know if this works for you. If needed, I can remote onto your system and help you through the initial configuration.

Exactly what Shawn said!! Also there should be an article on the 90 Minds KB on how to setup a SQL Linked Server

@ShawnSlavin was spot on. Got it done once we installed the 64 bit driver. Thanks and onward!