July 17, 2008 at 12:18 am
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=2;Database=E:\test.xls;',
'SELECT * FROM [Sheet1]')
getting following error
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.".
Suggest me.
July 17, 2008 at 12:31 am
Shiv,
I think, you have to check your Excel sheet.
I mean to say 'Sheet1' is present or not..
or it present in different name, then specified it in your query..
Cheers!
Sandy.
--
July 17, 2008 at 12:37 am
Check whether excel you are importing have sheet as wrksheet name.
July 17, 2008 at 5:47 am
hi sandy
every think is ok sheet is there with the same name but still
i am getting error.
July 17, 2008 at 7:51 am
When linked to Excel SQL server will look for a table like section.
Thus by trying to read Sheet1 is not identified as a table section.
To acomplish such read you must name the section to import included the header columns...
on Excel select the area to load and then choose from the menu Insert - Name - Define... and give it exactly the name you are using on your sql to get the data.... (customers... ) then save.
That way SQL will read such area as a table and load it.
Hope this helps... JO
July 17, 2008 at 2:15 pm
Try:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=2;Database=E:\test.xls;',
'SELECT * FROM [$Sheet1]')
* Noel
July 17, 2008 at 10:38 pm
No, I am getting same error.For your information i
writing code on sql 2005
i have try these as well but it did not work any more
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE with override
GO
SELECT a.* FROM OPENROWSET(BULK N'E:\ch.txt',SINGLE_CLOB) AS a;
DECLARE @a varchar(100)
SELECT @a='E:\ch.txt';
BEGIN TRY
EXEC ('SELECT * FROM OPENROWSET(BULK N' + @a + '+ ) AS Document')
END TRY
BEGIN CATCH
PRINT 'ERROR'
END CATCH;
Set NOCOUNT ON
create table #dir
(output varchar(2000))
insert into #dir
exec master.dbo.xp_cmdshell 'dir E:\ch.txt'
if exists (Select * from #dir where output like '%E:\ch.txt%')
begin
Print 'File found'
--Add code you want to run if file exists
end
else
begin
Print 'No File Found'
--Add code you want to run if file does not exists
end
drop table #dir
it is giving no file found
July 17, 2008 at 10:39 pm
Shiv,
hope you got the solution...:)
Cheers!
Sandy.
--
July 17, 2008 at 10:45 pm
It has nothin to do with SQL 2005.
Do the following:
Open you Excel file
Goto File -> Properties
Click on the "Contents" tab
If you don't see "Sheet1" under Named Ranges, your query will not work.
July 17, 2008 at 11:09 pm
Shiv,
please correct this query:
correct query:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=2;Database=C:\test.xls;',
'SELECT * FROM [Sheet1$]')
you missed the "$" symbol...after Sheet1..:)
Cheers!
Sandy.
--
July 18, 2008 at 12:17 am
i got the solution when i was connection the sql server that
was defferent machine/server and i was locating my system/local drive with openrowset
i have checked it with the help of follwing command
exec master.dbo.xp_cmdshell 'dir E:\'
it show me all the file exist in perticular drive but that was
different machine file to which i was connected.then i cannect to my
local server database engine and it work fine.
thanks for your help
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply