Thursday, November 24, 2005

Access database from SQL 2005/64

How to read Access database data from 64-bit SQL Server 2005.
1) Install SQL Server Express 2005, 32-bit
2) Configure both SQL Servers to use distributed queries
3) Create a database with a stored procedure in SQL Server Express to handle OLEDB requests
4) Link up SQL Server Express to SQL Server
5) Test it

OK. Let’s get to the details…

1) Install SQL Server Express 2005, 32-bit
http://www.microsoft.com/sql/editions/express/default.mspx

2) Configure both SQL Servers to use distributed queries
On both servers, run:
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go

3) Create a database with a stored procedure in SQL Server Express to handle OLEDB requests
On SQL Server Express, run:
USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name=N'oledb32')
CREATE DATABASE [oledb32] ON ( NAME=N'oledb32', FILENAME=N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\oledb32.mdf' , SIZE=3072KB , MAXSIZE=UNLIMITED, FILEGROWTH=1024KB )
LOG ON ( NAME=N'oledb32_log', FILENAME=N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\oledb32_log.ldf' , SIZE=1024KB , MAXSIZE=2048GB , FILEGROWTH=10%)
GO
USE [oledb32]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name=N'oledb32')
EXEC ('CREATE SCHEMA [oledb32] AUTHORIZATION [dbo]')
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[oledb32].[select_from_openrowset]') AND type in (N'P', N'PC'))
EXEC dbo.sp_executesql @statement=N'CREATE PROCEDURE [oledb32].[select_from_openrowset](
@select nvarchar(max)=''SELECT *'',
@provider nvarchar(max)=''Microsoft.Jet.OLEDB.4.0'',
@datasource nvarchar(max)='''',
@user_id nvarchar(max)=''Admin'',
@password nvarchar(max)='''',
@provider_string nvarchar(max)=NULL,
@query_or_object nvarchar(max)='''')
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql AS nvarchar(max)
SET @sql=@select +
'' FROM OPENROWSET('' +
'''''''' + @provider + '''''',''
IF NOT @provider_string IS NULL
SET @sql=@sql + '''''''' + @provider_string + '''''',''
ELSE
SET @sql=@sql + '''''''' + @datasource + '''''';'''''' + @user_id + '''''';'''''' + @password + '''''',''
SET @sql=@sql + @query_or_object + '')''
PRINT @sql
EXEC (@sql)
END
'

4) Link up SQL Server Express to SQL Server
On your 64-bit SQL Server, run:
sp_addlinkedserver @server='SqlOledb32', @srvproduct='', @provider='SQLNCLI', @datasrc='(local)\sqlexpress'
go
sp_serveroption 'SqlOledb32', 'rpc out', TRUE
go

5) Test it
On your 64-bit SQL Server, run:
SqlOledb32.oledb32.oledb32.select_from_openrowset @datasource='D:\some_valid_db_name.mdb',
@query_or_object='some_query_or_table'

I would like to explain the steps, but time is up.
Share and enjoy.

3 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. I'm trying a different approach, but I got some problems...

    - I Create a linked server (to a access db) into the 32 bit SQLExpress instance
    - Then, into the 64 bit default instance, I created a linked server to the 32 bit SQLServer instance, using the access linked server name as catalog. This second step fails because "login failed for user sa"... I am sure that the password matches...

    can you help me?

    (I need this trics because I cannot modify the queries, that now in a sql 2000 work)

    ReplyDelete
  3. do you have something for the newest versions of sql server x64 regular and 32bit express? and office 2007 (excel importing?)

    or do you know if this works to bridge them i havent tried im installing right now

    i will post you back if it works and what changes i had to make for it to work if i get it to work :)

    ReplyDelete