This is due to ODBC 32 bit & 64 bit are actually store in different directory. to viewing the new connection in 64 bit you will have adding the connection by launching the odbc at:

1. %systemdrive%\Windows\SysWoW64 folder,

2. run odbcad32.exe with administrator.

3. add SOTAMAS in odbc.

after added this, 64bit MAS will only able detect the new connection.

for more information on odbc issue you can refer the following link: -

support.microsoft.com/.../en-us

Thanks! But now I need help setting up a linked server in MS SQL 2012 64 bit. Getting an architectural mismatch error. Will create a new question...

The 32 bit ODBC administrator is here: %systemdrive%\Windows\SysWoW64

The 64 bit ODBC administrator is here: %systemdrive%\Windows\System32

It's confusing and I don't know why Microsoft did it this way, but that's what they did.

So perhaps I'm misunderstanding your instruction here. I need the 64 bit ODBC connection.

I was able to contact ProvideX to get the new 64 bit ODBC connection and installed it in DEMO mode, but am still having trouble getting a linked server set up using it.

or may be you can make a testing to ensure the connection was establish. just for instead, when you finish added a odbc in your workstation.

1. create a blank excel file.

2. go to "Data" tab -> "From Other Sources" -> "From Microsfot Query" (by right you'll see the odbc you added here)

3. by define your table to be dislpay or just using query such

select top 10 * from CI_Item

4. then just simply insert a table

if you are able to insert the table, which mean you have define your odbc correctly, if not then you have to create it in another path & see.

*hopefully it help by this testing

Did you ever figure this out?  I'm having the same issue testing 5.0 and cannot create a linked server because the driver provided is 32-bit.  Even in the 64-bit manager it shows the connection as 32-bit.  It's not an issue of where to go, it's that the driver isn't 64-bit.  Clicking "Add" only shows SQL drivers, the MAS driver isn't available to add in the 64-bit ODBC manager.

The x64 driver for Sage 100 will only install on a 2013 system.

There is a tip posted for an updated driver with the 2013 PU3 update.

Using the Sage 100 x64 Driver in a Linked Server environment:

• Stop SQL server and any other ODBC related tools that may have the driver in use.

• Install the x64 driver from the Sage 100 2013 posted Tip.  

• The driver installation requires a functioning a Sage 100 2013 Client workstation/Server installation  

Define a linked server

Create a "System" Data source

Define the x64 “System DSN" using the x64 ODBC Data Source Administrator

Include a Company Code, User and Password in the Logon Setup of the DSN. In the example queries below the Syetem DSN = MASLINK

From MS SQL Server Management Studio  

Scroll to Server Objects => Linked Servers => Providers

Right Click MSDASQL => Properties

The only options selected should be “Allow in Process” & “Level zero only”

Select the above options and click OK

From The SQL Server “New Query”

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

Execute the query

technet.microsoft.com/.../ms187569.aspx

Linked Servers Right Click => New Linked Server

Linked Server Name = DSN NAME

Server Type = Other Data Source

Provider = Microsoft OLE DB Provider for ODBC Drivers

Product name = DSN NAME

Data source = DSN NAME

Define a Connection String(Provider String)

Driver={MAS 90 4.0 ODBC Driver}; UID=ALL; PWD=all; Company=ABC;  Directory=\\UNC to ….\MAS90; LogFile=C:\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SILENT=1; SERVER=NotTheServer'

*Notes

SILENT=1;  Since SQL Server is running as a Service, any errors that force a dialog will not display and cause the process to continue running, adding SILENT = 1 will raise any errors encountered.

Click Ok to Save the Linked Server

Queries

Both OPENQUERY and OPENROWSET can be utilized to access Sage 100 Data.

Example 1

Select * from OpenRowSet('MSDASQL','Driver={MAS 90 4.0 ODBC Driver}; UID=ALL; PWD=all; Company=ABC;  Directory=\\UNC to ….\MAS90; LogFile=C:\PVXODBC.LOG; CacheSize=16; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SILENT=1;SERVER=NotTheServer',

'Select * FROM GL_Account ')

Example 2

SELECT * from OpenRowSet('MSDASQL','DSN=MASLINK; UID=ALL; Directory=\\cai514969\Sage 100 Standard ERP\MAS90;Company=ABC; LogFile=c:\PVXODBC.LOG; CacheSize=16; Debug=1; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SILENT=1; SERVER=NotTheServer',

'Select * FROM GL_Account ')

Example 4

SELECT * FROM OpenRowSet('MSDASQL','DSN=MASLINK;','SELECT * FROM GL_Account')

Example 5

SELECT * FROM OPENQUERY (MASLINK, 'Select * from GL_Account')

Set Query Options (New Query, Right Query Options) Execution Time Out to 60 seconds

If you get any errors Right Mouse Click the SQL Server connection and restart the service

Hi

I try to connect Sage 2013 to SQL Server 2012-64x

I have MAS 90 4.0 ODBC Driver 64x (Version 4.40.02.00, Date 9/28/2012)

The DSN works well, Linked Server is Successfull but when i try to select from linked server i get this error: (Sage2013x64 is my Linked Server)

SELECT * FROM OPENQUERY(Sage2013x64,'SELECT TOP 1 * FROM SO_SalesOrderHeader')

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "Sage2013x64" reported an error. The provider reported an unexpected catastrophic failure.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "Sage2013x64".

Please Heeeeeelp

The MAS 90 4.0 ODBC driver cannot hanle the 'SELECT TOP 1" statement. Don't even try.

Still has problem

Hi,

Does SELECT * FROM OPENQUERY(Sage2013x64,'SELECT * FROM SO_SalesOrderHeader') return the data set?

Thanks

John Nichols

Sage