Database documentation tool--SchemaSpy

Recently, I came across a tool which is exactly what I am looking for... It allows you to document the whole database and all the relationships among tables... The link below gives a detailed How-to for this tool... http://randomactsofcoding.blogspot.com/2009/01/database-documentation-using-schemaspy.html

Install Sql Server 2008 Sample databases

In order to play around sql server 2008 BI stack, I need to have the sample databases installed. You can find the sample database from codeplex website. Here is the link. A few tips I want to share with you...

SQL SERVER 2008 Port forwarding

Today, I installed a sql server 2008 database standard version and wanted to use SSMS to connect to this database engine. Below are lessons I've learned so far 1. Windows Firewall. On the server machine, have to open two exceptions which are tcp 1433 and udp 1434. 2. On the modem webpage, forward port tcp 1433 and udp 1434 to the server machine which is 192.168.2.10 in my case 3. Create user via sql server authentication. When I installed the sql server 2008, the default authentication mode is through windows authentication. In order to use the mixed mode, right click the server in SSMS and go into security section, click server authentication to sql server and windows authentication mode. Mission accomplised! Next step, I will tackle down the reporting service and analysis service 2008 so that I would be able to access reports/cubes online through my own server...

Remote deskop connection and Home FTP via port forwarding

Working on these during the past long weekend and finally figured that out... (assuming you are using windows xp pro) 1. For the remote desktop connection. It's fairly easy, in the port mapping/forwarding part of the router, map the port 3389 to the machine you want to remote in... In my case , I mapped port 3389 to my desktop which has a LAN ip of 192.168.2.10... You have to fix the ip instead of using DHCP for this purpose 2. For the home ftp server setup, it's kind tricky... First, you have to choose a ftp server software. I chose the Filezilla client/server cause it's opensource. Next, configure the server/client... This article is a must for you to successfully set it up ( http://wiki.filezilla-project.org/Network_Configuration ) In my case, I am with Bell aliant and it seems that they do not want users to use port 21. Therefore, I set up my FTP server to listen on port 50000. On the server configuration: In Passive mode settings, choose retrieve external IP address from and tick checkboxs for the last two items. On the General settings, change to 50000 for the server to listen on. On the client configuration: In FTP section-> active mode, tick the option to limit local ports used by fileZilla, 50000 - 50100 and tick options of the last two items which are getting the external ip and don't use external ip On the NAT/Modem part, port 50000-50100 to 192.168.2.10 and Mission accomplished!

A follow-up on the predicate discussion in the ON and WHERE Clause

This time, I am gonna recap what I've read lately on this topic and try to paraphrase it... First: there are some terms which need to be sorted out Preserving row table (aka PR) means any columns from this table will show up in the result set no matter if the join conditions are met NULL supplying table(aka NS) means any columns from this table will show up as NULL in the result set if the join conditions are not met ( if met, regular values will be pulled out from this table) Here is the example:

Table a

a1   a2
=======
1   10
2   20
9    30

Table b

a1   b1
=======
2    2
1   20

QUEREY

select *
from a
left join b
on a.a1 = b.a1  --Join predicate which you expect to see it anyway
and a.a1 =1    --That's the predicate I am talking about here


RESULT:

1 10 1 20
2 20 NULL NULL
9 30 NULL NULL

Explanation:

When a.a1 =1, b.a1 =1 , a.a1 =1, the join condition was met
all columns from PR a and NS b will be pulled out in the result set
(1 ,10,1,20)


when a.a1 = 2, the join was not met. All columns from a will STILL be pulled out
which is (2,20) because a is a PR table.However, columns from b will be not pulled
out since it's left outer join, only NULL will be provided because table b is a NS
table.so the result set is (2,20,NULL,NULL)

when a.a1 = 9, the join was not met. All columns from a will still be pulled out
which is (9,30).However, b is the NULL-supplying table which returns NULL. So the
result set is (9,30,NULL,NULL).

Therefore, the final result set is

(1 ,10,1,20)
(2,20,NULL,NULL)
(9,30,NULL,NULL)

Predicates at ON-clause and WHERE-clause

I sometimes came across queries that were written by other developers who put predicates at the ON-clause... I seems have difficulty telling the difference between the On-clause predicate and WHERE-clause predicate... Googled around that found the following useful documents: 1. http://www.catterallconsulting.com/2009/07/outer-join-get-predicates-right.html 2.http://www.ibm.com/developerworks/data/library/techarticle/purcell/0112purcell.html 3. http://richardlees.blogspot.com/ Sunday, June 14, 2009 The subtlety of SQL must be appreciated...

Learn ankh SVN

Ankh SVN is a plug-in for Visual Studio IDE... Here is the link I found quite useful...