Sage 100

SQL Stored Procedures. I have created a linked server in SQL 2014 to Sage 100. This uses the 64 bit driver. I can connect to and view the Sage tables. I can run a stored procedure to import Sage 100 data into SQL tables and can create views and pivots of this data BUT, I can only exec the stored procedure that references this ODBC connection ON the SQL server itself. I can exec any stored procedure that only references SQL tables and views. My thought was to create the same ODBC connection on the workstation but I need to use the 32 bit driver there. Is there any hope?

Do I need to create a 64 bit DSN

Sorry. Post got away from me. Do I need the same 64 bit DSN on each workstation that the linked server is using (in addition to the DSN connection to the SQL server)?

I wouldn't think so. Wouldn't you just need the ODBC connection to the SQL Server (probably the Native Client driver), and then the SQL Server would use its linked server to run the proc.

I tried to simplify the investigation. I have a view of a SQL table and I can create a link to it in the Access Frontend using the Native SQL DSN. I have a view of GL_Account in the SQL database. I attempt to link to it with the SQL native DSN

And no go. I get ODBC call failed.

Are the SQL Server and Sage 100 servers separate, or is Sage 100 on the same box as SQL?

Separate. So from a workstation you bounce to the SQL server, then to Sage server (or not, in this case). Works great running the Frontend on the SQL server though.

Is there a specific ODBC error you are getting?

If MAS and SQL are on separate servers, then you may be hitting a Kerberos issue as SQL is unable to impersonate the user. The attached document is from BizNet, but you can ignore the parts related to SSRS.

Thanks so much for this. I'll get on this today.