Help: linked server for Excel spreadsheet 1

[bperry]

Does anyone know how, or can provide a clear step-by-step example, of how to set up a linked server so that an ordinary (non-sa) SQL user can read from that spreadsheet via a Select statement.

We've already gotten the Select working with an OPENROWSET function, except it won't work with anyone with sa.

So I'm thinking we need to go the linked server route (which would be little neater anyway.) Either the EM approach or the sp_add_linked_server approach would probably be okay.

This is a continuation of thread183-267681. I'm sure the guy would appreciate some advice that actually works, instead of what I've been giving him.

thanx
bperry

 

[gailshusband]

Using attached servers is pretty straightforward. I am including the steps I used to set it up. This was run under SQL 2000.

1. Using the master database run the sp_addlinkedserver stored procedure to add the Excell Spreadsheet as a linked server as follows.

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\sap\performance\sap_st031.xls'
   NULL,
   'Excel 5.0'
GO

The parameters are @server,@srvproduct,@provider,@datasrc, @location, and @provstr respectively. As you notice @location is null for Excell. The @srvproduct and @provider should always be as shown. The @server is what you name the source and the @datasrv parameter is the path of the Excel spreadsheet.

After doing this you can go to SQL Enterprise Manager and confirm that the linked server exists.

2. All queries must be performed against ranges of cells that have names. In this case I named the range "WeeklyData".

3. In my testing I had to close the Excel Spreadsheet in order to run queries against it. If the spread sheet was open SQL Query Analyzer reported an OLE DB error.

4. I then ran the following query and received a response.

select [Ave CPU Resp time] from excelsource...weeklydata



It seems pretty straightforward, however the caveat that the Excel Spreadsheet needs to be closed can limit it's usefulness.

Hope this helps,

GailsHusband

 

[bperry]

Okay, that's a fantastic start. Thanks very much.

(1) The sp_addlinkedserver command seems to run fine. When we go into EM, I can now see the new linked server. If I expand it and click on 'Tables', I get an authentication error.

"Error 7399: Microsoft Jet OLEDB reported an error. Authentication failed."

That's probably not a good thing, is it?


(2) If I continue on anyway and try to do a Select on a named range, I then get this error. (No surprise really.)

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
-------------------------

I feel we must be pretty close, there can't be a whole lot wrong here.

All suggestions much appreciated.

bperry

 

[gailshusband]

I also recieved an error when I attempted to look at the tables from EM. I was working on a test server and so I was able to stop and start SQL server and was able to continue. I would recommend not looking at the tables of an Excel Spreadsheet that is functioning as an attached server through EM.

Try restarting SQL Server (if possible) and then just run the query. I will try to play with it this evening and see if I can duplicate the problem, and also determine if there is a way that Excel can be kept opened while running the query.

Regards,
GailsHusband

 

[gailshusband]

The problem is that you are most likely running the query under a SQL Server user account. If you run the query under Integrated Windows security, (and your user account has RW permission on the excel spreadsheet) you will not have any problem.

Alternatively you can use the sp_addlinkedsrvlogin stored procedure as follows to allow you to read and write to the spreadsheet.

sp_addlinkedsrvlogin 'ExcelSource', false, 'SAPAdm', NULL, NULL

You will then have permissions to run the query.

Regards,
GailsHusband

 

[bperry]

Hi all,
Based on gailshusband's (gh) remarks today, this is what I have found this evening.

It seems to make perfect sense that a mere mortal user would have trouble running the Select (i.e. reading the file). Since this file is outside the database, ir's the OS, not SQL Server, that must determine who can/cannot read the file. Whereas Don's 'god user' probably maps to either the local or domain Administrator, which has rights to access the hard drive - that's why that would work.

In EM, I examined the linked server entries made by gh's command, and now I find that the following seems to work every time for me, without fail:

In Enterprise Manager:
[ol]
[li]Under Security/Linked Servers, right-click and select New Linked Server. You'll get a window dialog with 3 tabs.

[li]Under the General Tab,
Linked Server: Key a name you like, say EXCELSERVER

Server Type: Select the radio button 'Other Data Source', and in the pulldown, select: Microsoft Jet 4.0 OLE DB Provider.

Product Name: Type Jet 4.0

Data Source: Type the path and name of your spreadsheet. ie. c:\temp\sales.xls (Actually, it seems to work both with and without the .xls extension.)

Provider String: Type Excel 5.0


[li]Under the Security Tab,
Click the radio button 'Be Made In This Security Context'. Then, type the name and password of an NT user or group that has local access rights to the spreadsheet. You could set up a special user for just this purpose, which is what I might do; other people might use something like the local administrator's account.

[li] That's it for configuration!

[li] Like gh said, the spreadsheet needs a named data range for this stuff to work. I created one called MyRange.

[li] Now a Select statement looks like this:
Select * from EXCELSERVER...MyRange.

[li] I notice that if the first row of your data range consists of column headings, then those headings will be returned by the Select as the column names.
[/ol]

Hopefully, gh and Don can try to duplicate these results and hopefully will find it works okay.

Don, let's also see what gh has to say tomorrow. Gosh, we must be close.

bperry

 

[donishere]

Can this linked server be created on the fly?

The spreadsheet that I will want to extract data from is one that is uploaded via an http form and will likely have different filenames every time.

I want to be able to add linked servers through ASP code. Adding linked servers using the user account I want to use does work in EM and Query Analyzer (QA). But, it does not work in ASP code.

---
User does not have permission to perform this action.
---

Anyone know why it allow the action in EM/QA but not in ASP?

I, too, believe we are close to tackling this one.

Don

 

[bperry]

>>But, it does not work in ASP code.
>>Anyone know why it allow the
>>action in EM/QA but not in ASP?
---
User does not have permission to perform this action.
---

It is hard to diagnose from a distance, but from BOL here is the relevant comment re: permissions on sp_addlinkedserver

Execute permissions default to members of the sysadmin and setupadmin fixed server roles

So without knowing the particular details, the answer (somehow) is that the user logged in with EM or QA must have these permissions, while the userid that your web server uses to connect to the database does not. The particular client (ASP, VB, EM, whatever) shouldn't matter.

So, I would check to see how the connection object in ASP is established to contact the database. I would say, without being dead certain, that it can hardly be the same user logging in thru QA.


I, too, believe we are close to tackling this one.
Actually, I feel we have moved a little farther away, and have now come in a circle. I suggested moving away from the dynamic OPENROWSET function (because of security limitations accessing the local hard drive) to the linked server model, where we are able to specify the local security we need (as described in my previous post.) But now it turns out that has a limitation because the spreadsheet (we learn) will be changing its name.

Hopefully, gh will have a suggestion today.

 

[donishere]

I think I got the server permissions working. I also believe that I got linked servers to be created through ASP code. From what I've tried, the user account must be given Security Admin and Setup Admin rights on the server, otherwise, it won't work. The one change I did make was to the sp_addlinkedsrvlogin stored procedure. The first null should have been Admin:

sp_addlinkedsrvlogin 'ExcelSource', false, 'TrafficWeb', 'Admin', NULL

bperry or gh, can you guys try this out?

Don

 

[bperry]

I don't have my laptop with me today to try.

But if it works for you, there you go.

 

[donishere]

But now it turns out that has a limitation because the spreadsheet (we learn) will be changing its name.

I got that part figured out. The name of the file will be an ASP variable that will be passed as part of the sp_addlinkedserver call.

Don

 

[bperry]

Doesn't seem like this should be so hard, does it?