x Want to participate? Join the group to interact
Options
  • State Verified Answer
  • Replies 3 replies
  • Subscribers 106 subscribers
  • Views 3353 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
47
topics
95
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
965
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
GeekOnCall

Linked Server Connections Failing in SQL 2014 through ODBC

SOLVED
Posted By

We have installed 64 bit ODBC driver and the Sage 100 Standard ERP 2014 workstation software on our new Windows server 2012 Datacenter server with SQL 2014 Standard.  The system's ODBC connections have been configured and can access the table count and state connected successfully.  The linked server connections configured like the way they were on the Windows server 2003 in SQL 2005 SP4.  The drive has been mapped on the server in the same way as previous.  We cannot test the Linked server connection without it getting stuck as a process or pull any OpenQuery's or OpenRowSet's.  The connections still work from the SQL 2005 server and the ODBC on our new server all connect fine.  Is there a setting in SQL 2014 or anywhere else to allow linked servers?  The service accounts are all running as the same users as before and have access to the shares.  Please assist!

  • 0

    When I set up some queries on a SQL Express v2014 install I did the following:

    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

  • 0 in reply to Kevin M
    verified answer
    As it turns out, yesterday I deleted the linked server connections, made the changes like you listed on the linked server provider and also changed the impersonate settings to use an admin account on the machine in question. I also filled in the user account information if connection is not specified to a Sage100 account that had access to the data. The last change I made was to put in the unc path rather than mapped drive location in the paths for linked server and odbc. With all the changes completed, I was able to connect and my stored procedures and their associated jobs are now running again. I had mapped the drives before for the user accounts for the server and sql agent. It still hadn't been working. Now it works as before in the old sql 2005 server. YAY!
  • 0 in reply to GeekOnCall
    Stored procedures run through the SQL service, not a user context, and mapped drives will never be recognized by a service. Always use UNC's or a local path when setting something up for a service to use.

*Community Hub is the new name for Sage City