Unable to run StoredProcedure with OpenRowSet from machine other than the one the SQL Server is running 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?
LVL 50
Dale FyeAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor 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.
0
 
Anthony PerkinsCommented:
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.
0
 
Dale FyeAuthor 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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Anthony PerkinsCommented:
Make sure Allow remote access is enabled too:
SELECT *
FROM sys.configurations
WHERE configuration_id = 117
0
 
Dale FyeAuthor Commented:
Have been diverted from this question for a couple of short notice suspenses.  Hope to get back to it next week.
0
 
Dale FyeAuthor 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
0
 
Dale FyeAuthor Commented:
Thanks for resolving that AC.
0
 
Anthony PerkinsCommented:
Did I?  If that is the case, I am glad to hear it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.