Unable to run StoredProcedure with OpenRowSet from machine other than the one the SQL Server is running on

Dale Fye
Dale Fye used Ask the Experts™
on
I've got an Access application which does a number of things, one of which is uploading some data from Excel to SQL Server.  This was taking forever until ACPerkins helped me resolve an earlier question.

However, when the user who normally runs this upload attempts to do so from his computer he gets a runtime error: #3146 - ODBC--call failed.  When I moved the app from the SQL Server machine to my local machine, I have the same problem.

Any way to resolve this error?  The file being uploaded is on a shared network drive with the same path string regardless of whether it is running from the server of either of our local machines.  Would it make a difference if we used a UNC naming convention for the path?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
Have you enabled using OPENROWSET(), by default it is off.  I believe this is the correct value to check if it is enabled:
SELECT *
FROM sys.configurations
WHERE configuration_id = 16391

Let me know if  you need help enabling it.  And yes, it is an advanced option.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
AC,

Will take a look tomorrow.

The code we worked on that creates the temp table, then uses a dynamic SQL string to allow me to pass the path, filename, and sheet name to the stored procedure works when run from Access, if I'm running the application on the SQL Server.  Because of that, I would assume that OpenRowSet() is enabled, but I'll take a look at that setting in the morning.
Top Expert 2012

Commented:
Make sure Allow remote access is enabled too:
SELECT *
FROM sys.configurations
WHERE configuration_id = 117
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Have been diverted from this question for a couple of short notice suspenses.  Hope to get back to it next week.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Anthony,

Trying to clean some EE questions up and found this was still open, and since I never did resolve the issue thought i'd do some more digging.

SELECT * FROM sys.configurations WHERE configuration_id = 16391

Name: Ad Hod Cistributed Queries
Value: 1

SELECT * FROM sys.configurations WHERE configuration_id = 117

Name: Remote Access
Value : 1
Top Expert 2012
Commented:
And I just re-read your question and yes you cannot use mapped drives and the SQL Server account (not you) needs access to the file from the server.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks for resolving that AC.
Top Expert 2012

Commented:
Did I?  If that is the case, I am glad to hear it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial