The DaxMindMapper Reloaded

Looking at the S#arp Architecture for a new project I’m working on.

The example it supplies requires the Northwind database. Hey no problem I think .. it comes with SQL Server so shouldnt be an issue. First problem .. Microsoft no longer install this example database with their later SQL Server versions (actually this no bad thing for a whole bunch of reasons so I’m not angry at this point). However they then go onto make it rather difficult to actually recreate this standard example database.

Googling for “northwind database for sql 2005″ will come up with links to the old Microsoft download area, which then directs you to codeplex, which then offers you a large download for all of the old example databases that, unfortunately, does not actually include the bl**dy Northwind database.

Finally found this link .. looks like the way to go. I’ll try out the SQL script install and see how it goes.

Looks like an excellent overview to indexes

Always difficult to find good info on the technology that goes into databases (ie stuff thats detailed enough but not so dry that you fall asleep trying to wade through it). So this appears to be an good article for developers wanting more info on Indexes.

Test-Driven Development of T-SQL Code - an article on using TSQLUnit to TDD the database layer. TSQLUnit is a open source framework to write tests for applications written in Transact-SQL.

Will need to investigate this technique the next time I come to create some T SQL. It’s an area (along with UI testing patterns) that I’ve been wondering about for some time. Just never had the excuse to get my hands dirty and come up with a position on it.

Well I’ve made it through my first 6 months of Oracle usage .. and it’s been an eye opener.

I suppose the main thing I’ve learnt is what a pile of old rubbish developers using Oracle as their datastore have to put up with. Jeez .. I’m sure Oracle is the dog’s danglees from a DBA point of view but from your plain ol developers perspective it’s been horrible. I appreciate there will always be clash between different systems solving the same sorts of problems; different user cultures, different tools, different reference websites .. even different philosophies. But just about everything from the documentation (aaaggghh!) to the tools with which your able to create, update and debug an Oracle database .. it’s all so painfully slow .. it’s been embarrassing.

WORD OF WARNING: IF YOUR USED TO THE DOCUMENTATION FOR SQL SERVER YOUR IN FOR A NASTY SURPRISE WITH ORACLE DOCUMENTATION .. IT IS ALL OVER THE PLACE.

Oracle Data Provider for .NET : Part II

http://www.ora-code.com/ - bloody usefull Oracle error code look up site.

Oracle Helper Site - Yep this a good one, includes;
- System Objects

Oracle 9i Operators - Very good.
Oracle 9i Functions - Very good.

SQL SELECT TOP N equivalent in ORACLE and MySQL

User Security

Oracle9i Database Online Documentation - Not sure how well written this is yet.

Oracle Pipelined Table Functions - I’m on the look out for a feature within Oracle that lets me do something similar to SQL Server’s table data type. These pipelined table functions appear to be a half way house.

Returning Rows Through a Table Function in Oracle

Thomas Eibner’s SQL Plus Command Line Documentation - Thanks. I’ll have to wait and see how usefull it is. It appears to be a good start

Calling a Oracle Stored Procedure from within VB.NET

Creating Stored Procedures and Functions

Oracle 9i SQL Reference

Blog

Dates

Helpful list of TO_CHAR date formatters.

DECODE (otherwise known as CASE/IFF) functionality

Usefull Scripts
Build Insert Statements for the Existing Data in Tables
create or replace Function ExtractData(v_table_name varchar2) return varchar2 As
b_found boolean:=false;
v_tempa varchar2(8000);
v_tempb varchar2(8000);
v_tempc varchar2(255);
begin
for tab_rec in (select table_name from user_tables where table_name=upper(v_table_name))
loop
b_found:=true;
v_tempa:='select ''insert into '||tab_rec.table_name||' (';
for col_rec in (select * from user_tab_columns
where
table_name=tab_rec.table_name
order by
column_id)
loop
if col_rec.column_id=1 then
v_tempa:=v_tempa||'''||chr(10)||''';
else
v_tempa:=v_tempa||',''||chr(10)||''';
v_tempb:=v_tempb||',''||chr(10)||''';
end if;
v_tempa:=v_tempa||col_rec.column_name;
if instr(col_rec.data_type,'CHAR') > 0 then
v_tempc:='''''''''||'||col_rec.column_name||'||''''''''';
elsif instr(col_rec.data_type,'DATE') > 0 then
v_tempc:='''to_date(''''''||to_char('||col_rec.column_name||',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';
else
v_tempc:=col_rec.column_name;
end if;
v_tempb:=v_tempb||'''||decode('||col_rec.column_name||',Null,''Null'','||v_tempc||')||''';
end loop;
v_tempa:=v_tempa||') values ('||v_tempb||');'' from '||tab_rec.table_name||';';
end loop;
if Not b_found then
v_tempa:='-- Table '||v_table_name||' not found';
else
v_tempa:=v_tempa||chr(10)||'select ''-- commit;'' from dual;';
end if;
return v_tempa;
end;

The Rational Guide to SQL Server 2005 Service Broker by Roger Wolter (ISBN 1-932577-27-0)

Excellent overview, short and sweet.

As with most technical books it helps if you have experience in the problem domain that the subject is attempting to solve. In this case I’ve spent a fair few years using MSMQ as a messaging environment, I’ve also used Biztalk to orchestrate some simple business rules and I’ve also seen queuing implemented in a number of SQL Server databases via tables and sprocs .. so yes I’d say I have just a little bit of experience in the problem domain area.

This book is not a reference book, it’s not hard core and it doesn’t offer loads of practical advice.

That said I’d still recommend this book to anyone that wants a gentle introduction to Service Broker.

Couple of points around Service Broker itself:
1. It looks great .. can’t wait to use it in anger. It solves so many messaging issues.
2. How does SB fit into the architecture along side .NET 3 Work Flow? I’m going to be looking into WF and hopefully it’ll become clearer.
3. I’m interested in the external application activation but it’s not clear from the book how this would work - probably the one thing I’d mark the book down on becuase it’s something everyone will want to do for any decent sized, real world application of Service Broker. Apparently there’s more info on this area at http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319
4. I’m interested in scalability .. the book talks about it and I’m sure the people that brought us SB will have thought it through but I’d like to see more and check more real world experiences.
5. I’m sure there will be more updates to this technology - it’s so extensable I’d be amazed if it isn’t.

Microsoft relaxes virtual SQL Server terms - Users can now run an unlimited number of SQL Server 2005 Enterprise Edition virtual instances on servers. Servers must already be fully licensed for SQL Server 2005 Enterprise Edition.

Good news surely

One of my other pet interests, at the moment, is the way in which SQL Server 2005 can be used in a SOA Architecure. I’m particularly interested in the Server Broker technology.

How SQL Server 2005 Enables Service-Oriented Database Architectures - Good overview.

Microsoft SQL Server Development Customer Advisory Team’s Best Practice for OLTP profiling

Interesting list of checks for SQL Server(?) based OLTP databases .. it’s always a little difficult to find concrete guidelines for optimising databases, so it’s nice to see an actua checklist from the peopel that know.

It runs now but very flakey!

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_CreateDataLoadScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_CreateDataLoadScript]
GO

exec (@cmd1 + @cmd2)
drop table #a
GO