May 13, 2011 at 4:46 pm
I'm receiving the below errors when running the following script. I have been looking at this for a long time but can't figure out the underlying issue. I'm running this on Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Developer Edition on Windows NT 6.0 (Build 6002: Service Pack 2). Please any suggestions?
Errors:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 17
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Actions:
USE MSDB
GO
EXEC sp_addLinkedServer
@server= 'xlsData',
@srvproduct = 'Jet 4.0',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\Backup\CHEMCO WAREHOUSE MASTER MATERIAL LIST_Revised_for_Import.xls',
@provstr = 'Excel 8.0; HDR=Yes'
begin transaction
set nocount on
DECLARE @loadtable TABLE
(ID integer identity(1,1),
PartName nvarchar(255),
Discription nvarchar(255),
Manufacturer Nvarchar(70),
Catagory Nvarchar(70),
Unit nvarchar(70),
Price decimal(28,8),
Cost decimal(28,8),
Quantity decimal(28,8),
Location nvarchar(50)
)
insert into @loadtable
(PartName, Discription, Manufacturer, Catagory, Unit, Price, Cost, Quantity, Location)
select --top 6000
xlsData.[P/N]
+ case when rank() OVER (PARTITION BY xlsData.[P/N] ORDER BY rand() DESC) > 1
THEN '-' + convert(nvarchar(10), rank() OVER (PARTITION BY xlsData.[P/N] ORDER BY rand() DESC))
else '' End ,
xlsData.DESCRIPTION,
isnull(nullif(rtrim(xlsData.[Manufacturer]),''),'Other Manufacturer'),
isnull(nullif(rtrim(xlsData.[Category]),''),'Unknown'),
isnull(xlsData.[Unit of Measure],0),
isnull(xlsData.[price],0.00), -- All chemco part prices are to be zero xlsData.[price]
isnull(xlsData.[price],0.00),--cost
xlsData.QTY, -- Quantity
xlsData.[Location]-- ISLN in wearhouse
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Backup\CHEMCO WAREHOUSE MASTER MATERIAL LIST_Revised_for_Import.xls";Extended properties="Excel 6.0; HDR=yes; IMEX=1"')...[fiber$] AS xlsData
where xlsData.[P/N] is not null
and len(rtrim(xlsData.[P/N])) > 0
--and xlsData.[Part#] = '35'
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
May 13, 2011 at 9:49 pm
If this is a 64 bit SQL Server instance, you may need to use this:
May 16, 2011 at 3:28 pm
You may need to restart the SQL service. I get this error about once a month and restarting the service clears it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply