March 15, 2006 at 8:12 am
Creating a Linked Server to an Excel spreadsheet works great for me, except that the spreadsheet has to be copied to a local directory (on SQL Server box) for it to work from t-sql. I have found that UNC (using a network share) does not work. Is this true or am I just doing something wrong? Any way to avoid having to copy the spreadsheet to my SQL Server machine first?
March 15, 2006 at 8:28 am
Hi Charles - a colleague of mine used the following to access a remote Excel workbook and it works fine.
SELECT *
FROM
OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0', 'Data Source= \\MANMEA0007\Transfer\UPS3\Inbox\CampaignStamping.xls;extended Properties=''Excel 8.0;IMEX=1''')...CampaignStamping$
March 15, 2006 at 8:34 am
Hmmmm...didn't think of the OPENDATASOURCE thing. And it probably doesn't require a Named Range, either. I will definitely try it out.
Thanks, Allen.
March 15, 2006 at 8:50 am
I think you can use a named range or a sheet - im not sure but if the name ends in a $ then it's a sheet if not it expects it to be a range (or the other way round).
hth.
March 15, 2006 at 9:17 am
Unfortunately, I have the same problem with OPENDATASOURCE. It works great as long as the spreadsheet is local...kind of sounds like a share Permissions issue, perhaps.
cjb
March 15, 2006 at 9:25 am
That would be my guess - can you update the workbook using Excel in it's remote loacation?
March 15, 2006 at 9:45 am
Yes. There are no special restrictions placed on the workbook or share. And I do not have this problem with other t-sql operations.
cjb
March 15, 2006 at 9:51 am
You are executing the sql loged in as yourself with Windows authentication?
March 15, 2006 at 11:30 am
Yep, that's correct. And SQL Server Service is running under a special domain account that has been given explicit permissions to the share.
March 15, 2006 at 1:56 pm
It turns out that all the techniques (Linked Server, OPENDATASOURCE, and OPENROWSET) work just fine with UNCs on my SQL Server 2005 test machine. I read a KB article that indicated the problem with my production 2000 server (on Win2003) was an outdated Jet 4.0 driver which turns out not to be the case. Perhaps something is locked down and I just don't know what it is--sure would like to know, though.
cjb
March 15, 2006 at 4:15 pm
Charles;
Is your Windows authentication logon defined in sysadmin?
If not, then opendatasource (and openrowset or linked server) do not use the SQL Server service account for access, but the SQL Server Agent Proxy Account. (This was a change instituted in SP3, to prevent non-sa users from accessing xp_cmdexec.)
Go to EM, right click on SQL Server Agent, go to Job System tab, uncheck Non-SysAdmin job step proxy account, and set the proxy account to an account that can reach your UNC file share.
Hope this helps
Mark
March 16, 2006 at 7:05 am
I tried what you said but I get the same result. I set SQLAgent's proxy account to the same domain service account as the MSSQLService, but no change. This service account has "Full" control over the network share in question. And, yes, my Windows logon is in the SysAdmin role.
Thanks,
cjb
March 16, 2006 at 7:23 am
I remember I could not access remote excel file by linked server or opnedatasource/openrowset before. But after I installed the pre-requisite (latest MDAC) of SQL 2000 SP4, I can do it now.
March 16, 2006 at 7:24 am
And just a reminder, if the file path has spaces, you need to use double quotes for the whole path and file name.
March 16, 2006 at 9:16 am
No spaces in UNC path.
A KB article indicated that version of MDAC was the culprit, specifically, the Jet4.0 component. I tried to update according to the article but the installation failed saying that it was already updated (with Win2003 SP1). While SQLSvr is, indeed, at SP3, I assume the Jet4.0 component would not update for the same reason. I might try it anyway.
Thanks,
cjb
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply