?
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
?
242 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
  • 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 49

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

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 49

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 49

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

864 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