Query against excel file

Query against excel file

Post by UWFzcGV » Thu, 05 Nov 2009 04:06:01


I'm trying to connect Sql Server to an excel file and I'm using the following
in a query and getting the following error message...



Msg 15247, Level 16, State 1, Procedure sp_addlinkedserver, Line 26

User does not have permission to perform this action.

Msg 7202, Level 11, State 2, Line 2

Could not find server 'EXCEL' in sysservers. Execute sp_addlinkedserver to
add the server to sysservers.



-------------Here is what I was using.


EXEC sp_addlinkedserver EXCEL,

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'J:\Operations\DailyWeekly OPS\Mark\LMS\Conversion\LMSPAgents.xls',

NULL,

'Excel 5.0;'

GO

SELECT *

FROM EXCEL...LMSPAgents

GO
 
 
 

Query against excel file

Post by Russell Fi » Thu, 05 Nov 2009 06:39:01

First of all, you do not have the permissions to run the
"sp_addlinkedserver" stored procedure. The Books Online say that it
requires the "ALTER ANY LINKED SERVER" permission to be able to run
sp_addlinkedserver.

Either you need to be granted that privilege or you need to get your DBA to
create the linked server for you.

Second, the SELECT failed because the linked server does not exist, since
you could not create it. Once the linked server is created, and assuming
you have rights to the folder containing the excel file, it should work as
expected.

RLF

 
 
 

Query against excel file

Post by John Bel » Thu, 05 Nov 2009 06:42:01


The example on BOL is:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyData\DistExcl.xls',
NULL,
'Excel 5.0'
GO

Differences are: the server name is in single quotes, you have a semi-colon
in the provider string 'Excel 5.0'. If the J drive is a mapped drive you
should use a UNC.

John