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.

SQL XML (Re: SQL cursors: This was a funny one)

Just so that I have said it: Don’t come here for XQuery expert advice, I don’t understand any part of XML. I just copy other people. Still, I am pretty good at copying. I got a reply on my post “SQL cursor: This was a funny one” that was so nice, I think it deserves a new post:

--------------------------------------------
Michael Rys:

You mean you could write the following simple expression with XML:

SELECT (SELECT * FROM Customers FOR XML AUTO, ELEMENTS, TYPE).query(
'for $c in /Customers
return
<Customers>{
for $p in $c/*
order by local-name($p)
return $p
}</Customers>'
)

:-)
eoMichael Rys:
--------------------------------------------

USE AdventureWorks
GO

CREATE PROCEDURE pXmlAllFieldsSorted(@tableName nvarchar(MAX))
AS
DECLARE @sql AS nvarchar(MAX)
SET @sql = 'SELECT (SELECT * FROM ' + @tableName + ' FOR XML AUTO, ELEMENTS, TYPE)' +
'.query(''for $c in /' + @tableName + ' return <' + @tableName +
'>{ for $p in $c/* order by local-name($p) return $p' +
'}</' + @tableName + '>'')'

PRINT @sql
EXEC (@sql)
GO

pXmlAllFieldsSorted 'HumanResources.Employee'