Access database from SQL 2005/64
Moved:
http://information-management-enabled.blogspot.com/2005/11/access-database-from-sql-200564.html
posted by ' at 17:54
Thursday, November 24, 2005
http://information-management-enabled.blogspot.com/2005/11/access-database-from-sql-200564.html
Previous Posts
- OLAP defined
- Hardware problems solved, for now...
- Aaaaaaaaaah What a day!
- Hardware problems, I hope
- Move it all into your new home. Sql 2005.
- SQL XML (Re: SQL cursors: This was a funny one)
- Rootkit distributed by SONY
- -web-database-server OH YEAH database-web-server-
- SQL cursors: This was a funny one
- Hardcore Programming (dis)cont
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