Error on sql script with 'openrowset'

Question

I have a problem with my SQL script:

SELECT SP.[MobileNumber], SP.[LastName], SP.[FirstName] FROM SampleTable1 SP INNER JOIN OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\devpc11\sample.xls;', 'SELECT MobileNumber, LastName, FirstName FROM [SampleData$]') T ON SP.[MobileNumber] = T.[MobileNumber] GO 

when I try to execute this, it generates this error:

Msg 7357, Level 16, State 2, Line 1 Unable to process the object "SELECT Mobile number, Last name, First name FROM [SampleData $]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0 for the linked server" (null) "indicates that either the object does not have columns or the current user does not have permissions for this object.

Is there any solution for this? I really can't find them in the last 3 hours. Basically, I just want to manipulate the data from the excel file and then save it in the sql server 2005 database, but at the moment I want to get the data from the excel file for the sql server .. thanks for the help ..

Answer 1

I got this to work with a spreadsheet locally. forget OPENROWSET

• Create a named range in your extended excel sheet. Tio do this, select the columns you want (including the headings), right-click and select Name Range. Give this name, it will be your table name.

  http://www.homeandlearn.co.uk/me/mes9p2.html

• Save and close the table. SQL Server will not be able to access it if you open it.

• Add linked server. Follow the instructions in Section E , which describes how to add a linked server for Excel tables:

  http://msdn.microsoft.com/en-us/library/ms190479.aspx

• You should be able to request a DS quite happily by following the instructions again.

Here is the code that works for me:

 EXEC sp_addlinkedserver 'ExcelSource4', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\sqlss.xls', NULL, 'Excel 5.0'; GO SELECT * FROM ExcelSource4...MyTable2; 

And finally. Start accepting answers and vote for any helpful answers. This is the lifeblood of StackOverflow.