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
228 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 (Access MVP)
  • 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 47

Author Comment

by:Dale Fye (Access MVP)
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39957686
Have been diverted from this question for a couple of short notice suspenses.  Hope to get back to it next week.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
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 500 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 47

Author Closing Comment

by:Dale Fye (Access MVP)
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now