?
Solved

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

Posted on 2014-03-05
8
Medium Priority
?
239 Views
Last Modified: 2014-07-11
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?
0
Comment
Question by:Dale Fye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39908292
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
 
LVL 48

Author Comment

by:Dale Fye
ID: 39908310
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39908317
Make sure Allow remote access is enabled too:
SELECT *
FROM sys.configurations
WHERE configuration_id = 117
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 48

Author Comment

by:Dale Fye
ID: 39957686
Have been diverted from this question for a couple of short notice suspenses.  Hope to get back to it next week.
0
 
LVL 48

Author Comment

by:Dale Fye
ID: 40187548
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 40189482
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
 
LVL 48

Author Closing Comment

by:Dale Fye
ID: 40189490
Thanks for resolving that AC.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40189503
Did I?  If that is the case, I am glad to hear it.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question