I tried to implement this soltuion but had a problem using the 'Microsoft.Jet.OLEDB.4.0' driver from the SQL Server 2005 Express Edition (X32) instance on a 64 bit machine running Vista X64. Here is a summary of the problem where I have removed most of the complexity of the solution above just to focus on the problem I am having:
I am getting this error when trying to Select From a Table/Worksheet from a Linked Server connected to Excel
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLTEST_SP" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLTEST_SP".
I am running this in a SQL Server 2005 Express Edition (X32) instance on a 64 bit machine running Vista X64. I also have SQL Server X64 on the machine but there is no 64 bit version of the 'Microsoft.Jet.OLEDB.4.0' driver, so, I need to use SQL Server 2005 Express Edition for this.
Here is the code I am using to establish the Linked Server and the Select statement:
USE OLEDB32 GO
If Exists (select * from sys.servers where name = 'XLTEST_SP') BEGIN EXEC sp_dropserver 'XLTEST_SP', 'droplogins'; END GO DECLARE @RC int DECLARE @server nvarchar(128) DECLARE @srvproduct nvarchar(128) DECLARE @provider nvarchar(128) DECLARE @datasrc nvarchar(4000) DECLARE @location nvarchar(4000) DECLARE @provstr nvarchar(4000) DECLARE @catalog nvarchar(128) -- Set parameter values SET @server = 'XLTEST_SP' SET @srvproduct = 'Excel' SET @provider = 'Microsoft.Jet.OLEDB.4.0' SET @datasrc = 'c:\Temp1\SQLExcelTest.xls' SET @provstr = 'Excel 8.0' EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog GO
SELECT * FROM XLTEST_SP...Sheet1$ GO
Any help resolving the problem would be appreciated.
15 Comments:
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
I tried to implement this soltuion but had a problem using the 'Microsoft.Jet.OLEDB.4.0' driver from the SQL Server 2005 Express Edition (X32) instance on a 64 bit machine running Vista X64. Here is a summary of the problem where I have removed most of the complexity of the solution above just to focus on the problem I am having:
I am getting this error when trying to Select From a Table/Worksheet from a Linked Server connected to Excel
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLTEST_SP" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLTEST_SP".
I am running this in a SQL Server 2005 Express Edition (X32) instance on a 64 bit machine running Vista X64. I also have SQL Server X64 on the machine but there is no 64 bit version of the 'Microsoft.Jet.OLEDB.4.0' driver, so, I need to use SQL Server 2005 Express Edition for this.
Here is the code I am using to establish the Linked Server and the Select statement:
USE OLEDB32
GO
If Exists (select * from sys.servers where name = 'XLTEST_SP')
BEGIN
EXEC sp_dropserver 'XLTEST_SP', 'droplogins';
END
GO
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\Temp1\SQLExcelTest.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog
GO
SELECT * FROM XLTEST_SP...Sheet1$
GO
Any help resolving the problem would be appreciated.
Terry Clancy
Post a Comment
<< Home