0

I am trying to create a linked server to SAGE 50 using server in management studio but keep getting an error. I am hoping that someone has done this before and can provide some guidance.I set up and tested "ODBC data source administrator (64bit)" with as seen below and it works for Microsoft access when I used "External data - ODBC database" connection.

enter image description here

I then added a linked server with the security tab set up like so using the same credentials that I used to add it to Access like so: enter image description here

And set up the general tab like below and tested but keep getting this error enter image description here

Not sure what I am doing wrong as I google how to do this and the results only brought me this far.

Castell James
  • 329
  • 6
  • 23

1 Answers1

0

The only way I was able to get around this was to install a completely separate 32-bit SQL Server Express and create the linked server to SAGE there. Just install the minimal 32-bit server itself, you don't need an extra management studio or any additional fluff.

You can log onto both SQL versions through the same Management Studio. Use the 32-bit version of SQL to load data into staging tables. Then select that data directly from your 64-bit SQL using the same interface.

It's a dirty solution, and slight bit higher overhead, but my IT department was not able to find any better solution that would actually work with the 32-bit Pervasive SQL ~v10 that older versions of PeachTree employ for SAGE.

Tim Leaf
  • 388
  • 3
  • 15
  • Thanks Tim. I am thinking that another solution would be to create an SSIS package that pulls data from the SAGE tables into SQL Server and have that process scheduled to run daily. Thoughts? – Castell James May 08 '18 at 14:05
  • Not sure about that specifically, but any compact 32-bit database solution with the ability to connect via that ODBC should do the job. Just keep in mind that if you're exporting from PeachTree to files, that's yet another conversion you need to make, and then re-convert when loading the files into SQL Server 64-bit. I would recommend a solution that SQL Server 64-bit can connect to directly, if it's where your main code is located. The benefit of using another 32-bit SQL Server is that you can select directly from the 32-bit staging tables from your 64-bit SQL procedures. – Tim Leaf May 08 '18 at 17:13
  • 1
    I gave it a shot using SSIS to connect to ODBC 64bit connection. When the packages runs, it dumps the data i needed into the SQL server database. – Castell James May 08 '18 at 17:25
  • 1
    Correction ODBC *32bit* connection not 64bit. – Castell James May 09 '18 at 12:52