SQL Server Performance Forum – Threads Archive

Importing xls-> sql …. ERROR

Hi, help me please with this ..
I am using statement for importing xls file -> sql server tables in stored procedure. Sql server is 2000 . Sample :
Declare @sqlCommand varchar(6000), @tmp varchar(255) if exists (select * from dbo.sysobjects where id = object_id(N’importXLS’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table importXLS set @sqlCommand= ‘Select * into importXLS FROM OPENROWSET(‘+char(39)+’Microsoft.Jet.OLEDB.4.0’+char(39)+’,’ +char(39)+’Excel 8.0;Database=’+ @tmp+char(39)+’,’+char(39)+’SELECT * FROM [Linky$]’+char(39)+’)’ execute (@sqlCommand)
— @sqlcommand is
Select * into importXLS FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=d:p_kalkulackaupload23.XLS’,’SELECT * FROM [Linky$]’)
—- — procedure returns message — Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’.
OLE DB error trace [Non-interface error: CoCreate of DSO for Microsoft.Jet.OLEDB.4.0 returned 0x80040154].
Where is problem , thanks ,Lubo

---

1. sp_addlinkedserveruser – covered in BOL
2. You can map users on the non ‘sa’ role to the ‘sa’ user directly on the security tab of the Enterprice manager linked server. Check this as well
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=46148 —————————————-
http://spaces.msn.com/members/dineshasanka

---

HI, I tried such code –Here we set up the linked server using the JET provider
EXEC sp_addlinkedserver N’ExcelLink’,
@srvproduct = N”,
@provider = N’Microsoft.Jet.OLEDB.4.0′,
@datasrc = N’C: emp est.xls’,
@provstr = N’Excel 8.0;’
GO

---

Did it work? Madhivanan Failing to plan is Planning to fail

---

i must write my mesage to more windows. I don’ know where is proble but i can’t write long here msg PART I -execute sp_dropserver ‘ExcelLink’ EXEC sp_addlinkedserver N’ExcelLink’,
@srvproduct = N”,
@provider = N’Microsoft.Jet.OLEDB.4.0′,
@datasrc = N’C: emp est.xls’,
@provstr = N’Excel 8.0;’

---

PART II SELECT * FROM OPENQUERY(ExcelLink, ‘select * from [test$]’)
GO
but here is an error , test.xls has sheet names Test

---

btW … I HAVE INSTALL sql server on more pc as local. This code is correct on other pc . It is wrong on my pc. Both are version SQL Server 2000.

---

]]>