Excel Workbook as Linked Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Excel Workbook as Linked Server

Author

Topic

Remco
Starting Member

2 Posts

Posted - 2002-09-16 : 06:51:12

verslons
SQL Server 7.00 - 7.00.961
Windows NT 4.0 (Build 1381: Service Pack 6)
Enterprise manager ...Management console v 1.1

Hi i'm remco i saw similar questions on the site but nothing solved my problem so here is my question

Q:
I'm try to link a multiple excel sheets to SQL server

i run the following proc :

EXEC sp_addlinkedserver 'ExcelSheets',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\myfile.xls', (this could be any file)
NULL,
'Excel 8.0'
GO

no errors

When i'm to open the linked server in Enterprise manager
i see a node called ExcelSheets below that tables.
When i click on tables i get the error..
Could not initialise Provide oledb 4.0 etc

When i run a query like this
SELECT *
FROM Excelsheets...Remco
go

I get the folowing error:

Server: Msg 7303, Level 16, State 2, Line 6
Could not initialise data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Not a valid account name or password.]

hmmm. Wrong password???

So i run this proc

EXEC sp_addlinkedsrvlogin 'ExcelSheets', 'false', 'sa', 'Admin', NULL
Go

no error

But I’m getting the same errors when try to getting results out of the linked server

Best regards,

Remco j v Grevenbroek
Software developer Sodexho nederland

pucci70
Starting Member

20 Posts

Posted - 2002-09-16 : 07:34:07

Try this:

sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\data\MySheet.xls', NULL, N'Excel 5.0'
GO

sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO

To access data from an Excel spreadsheet, associate a range of cells with a name. A named range can be accessed by using the name of the range as the table name.

================================
Thank You All For Your Help ^^
Luca

Remco
Starting Member

2 Posts

Posted - 2002-09-16 : 07:42:16

thanks for the fast reply,
unfortunatly this is basicly the same as what I did.

stil getting the same error : Server: Msg 7303..

remco

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-09-16 : 10:40:20

I see that you are passing Excel 8.0 as the provider string,
i think you should pass Excel 5.0 as pucci70 mentioned.

If that doesnt work try using the excel odbc driver instead of the Jet OleDB driver,(you will need to use the Oledb provider for ODBC to do that)


EXEC sp_addlinkedserver 
   'ExcelSheets', 
   '', 
   'MSDASQL',
   NULL,
   NULL,
   "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\somepath\mySpreadsheet.xls;DefaultDir=c:\somepath" '

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources