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'

-web-database-server OH YEAH database-web-server-

Oh yeah.
Heard about a web database server?
Or a database web server?
Take a look at mine, a mean machine search server (made for AJAX solutions).

Test bed for 32-bit version and a URI for searching for “microsoft” in Norway:
http://searchpalette.com/g/g.html
http://searchpalette.com/SPaletteGorm/0/25/microsoft

My 64-bit version searching from 10Million records getting page 25 of people called “Bil” with page size 25.
http://62.89.36.69/spalette/24/25/bil
Outputing an unbelievable 3151 bytes (scripts not included) for representing 25 persons with address and all phone numbers in a millisecond (before it gets to the router).

See it in AJAX action. Type “bil” at http://firmakatalogen.com/ and hit page 9. The 1400-1500 bytes needed to represent the hits should come more or less instantly after you let go of the mouse button. If not, it is a network latency problem.

The server, working on a XEON 64 takes up 50MB RAM for serving 5GB of raw data. I guess I could have a hundred of them on the same (single processor) machine. Integration is done with SQL Server 2005 (and has been done for about a year now in production, hope I am not breaking NDAs etc. etc. but it was the best solution, so I did it)

Well, my 64-bit webservices database server will be even more fun.

Thank you, Microsoft and Intel.

SQL cursors: This was a funny one

This was a funny one from msdn forums:

Is it possible to order the column names in alphabetical order in Sql Server?

This is a problem that in my view elightens how much you can’t do with SQL.
First thought is to use CLR.
Since I never use SQL OPEN CURSOR and FETCH the obvious took some time.

I am sure there is a more elegant solution using XML in some way, but I think XML gets overly complex at times, so I avoid using it if there are more practical solutions. Even if they don’t always look that elegant.

Here is my solution:

-- SQL to output table with columns sorted

CREATE PROCEDURE pTableWithSortedFields(@tableName nvarchar(MAX))
AS
DECLARE @sql as nvarchar(MAX)
SET @sql = 'SELECT '

DECLARE temporary_cursor CURSOR FOR
select name from sys.all_columns
where object_id = OBJECT_ID(@tableName)
order by name

DECLARE @field as nvarchar(MAX)
OPEN temporary_cursor

FETCH NEXT FROM temporary_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @sql = @sql + '[' + @field + ']'
     FETCH NEXT FROM temporary_cursor INTO @field
     IF @@FETCH_STATUS = 0
          SET @sql = @sql + ','
END

CLOSE temporary_cursor
DEALLOCATE temporary_cursor

SET @sql = @sql + ' FROM ' + @tableName
print @sql
EXEC (@sql)

GO

pTableWithSortedFields 'sometable'

-- SQL to output table with columns sorted

Well, answering posts (when I get the time) can be a learning experience. I am sure I will use cursors for something again soon, another tool is somewhat sharpened

Hardcore Programming (dis)cont

This is posted from Word.

Hardcore Programming cont. will have to wait. I needed to put my AJAX database webservices server on 64-bit. Seems to work. HTTP.SYS was a real drag (with the newest platform SDK), so I had to do LoadLibrary and GetProcAddress on the lot. Hopefully it will pay itself at some point in the future.

Also, the SysWOW64 puzzled me a bit

Have a nice weekend
Yours, Gorm Braarvig

Hardcore Programming cont.

1. "Hello World"
New console application,
// stdafx.h
#include
// HelloConsoleWorld.cpp
int _tmain(int argc, _TCHAR* argv[]){
_tprintf(_T("Hello World")); ::MessageBox(NULL, _T("Hello MB World"), _T("MB World"), 0) ; return 0;}

dir:
debug - 40 960 HelloConsoleWorld.exe
release - 6 144 HelloConsoleWorld.exe
64bit debug - 31 232 HelloConsoleWorld.exe
64bit release - 7 168 HelloConsoleWorld.exe
64-bit versions are 1KB and 10KB larger. Fine.

Compiling to 64-bit was harder than I thought but creating two new configurations and compiling and linking all configurations I got what I wanted.
First looks at the images in a hex editor reveals that
- 64-bit version has something called _C_specific_handler, google'ing it gives me (as expected) nothing. Will I have problems getting rid of RTL?
- Both versions are compiled as UNICODE pr default. Nice.
Putting all 64-bit versions in ...\x64\... is nice, why not make a ...\x32\... too?
I am content with "Hello World" for now.


2. "DelayWithCancelMT"
New win32 app
Remove resource files, etc, all the code, replace with
#include "stdafx.h"
void APIENTRY _tMyWinMain(HINSTANCE, HINSTANCE, LPTSTR lpCmdLine, int nCmdShow)
{ ::MessageBox(NULL, _T("Hi"), _T("Hi"), 0);
UNREFERENCED_PARAMETER(lpCmdLine);
UNREFERENCED_PARAMETER(nCmdShow);}

How can I get this to be bellow 1KB exe?
There are many new things turned on in VS 2005 (that I don't know how to turn off).
I will continue tomorrow.

Hardcore programming
Okay, Windows 2003 SP1/64.
I am coming to get you.

1. "Hello 64 bit World." console
Should be a no-brainer

2. "Delay with cancel" multi-threaded
Without RTL of any kind.
Hope this is possible with the new compiler/linker
Destination release exe should be bellow 2KB
What kind of structure and segment alignments can I have?
What does it look like in the hex-editor?
If I get dependends.exe to run, will be interesting to see what dlls are needed to run a windows program

3. "malloc the hell out of the machine"
Application that uses more than 2GB continuos RAM.
Long lived dream of mine.

OK. time to start.
G.