SQL Server 2005 : Mutliple Common Table Expressions (CTE)

Couple of hints using one of my favorite features of SQL server 2005 - Common table expressions. If you find this of use, leave a comment :)

USING MULTIPLE CTES IN A SINGLE SELECT STATEMENT
If you want to use more than one CTE, and let's face it - who wouldnt? Simply drop the WITH when adding the definition of the second (or more) CTE as shown below:

;with cte1 as
(select getdate() as field)
,cte2 as
(select getdate() as field)

select *
from cte1
inner join cte2 on cte1.field = cte2.field

CREATING A CTE BASED UPON ANOTHER CTE
If you dont like to have a derived table joined to the CTE, you might want to create a second CTE based on the first, here is how it can be done. Note that cte3 is just a select of CTE1 - simple but effective:

;with cte1 as
(select getdate() as field)
,cte2 as
(select getdate() as field)
,cte3 as
(select field as field
from cte1)

select *
from cte1
inner join cte2 on cte1.field = cte2.field
inner join cte3 on cte1.field = cte3.field

Enjoy!

Application Icons and PNG images

As you do, I find I spend too much time trawling the internet for that right image to spice up the look and feel of my application.

If only there was a site I could go directly to and search for the image I was after...

Well, I have found one! http://www.iconfinder.net

It's a fairly extensive archive and should be able to provide you with what you are after and more importantly there is a filter which you can select to show only those icons available for commercial use.

There maybe other sites out there, which I invite you to inform me of if you have knowledge of such sites, but if not... fill your boots!

SQL Server 2005 : Using XML Columns

Had cause to play around with XML Columns in SQL Server 2005 over the weekend on a personal project im working on (shameless plug : www.milanxbl.com).

To cut a long story short, I ended up having a (well formed) XML document saved into an XML Column in the database that I needed to break down into its component parts in order to process the info into tables which already existed. This XML contained 2 parts in a master/detail style - the detail section contained a collection of items and the master section was just a header with several values.

I broke apart the master chunk of the xml using the following TSQL

SELECT UniqueIDColumn
,xmlcolumn.value('(/Root/XMLValue1)[1]', 'varchar(max)') AS XMLValue1
,xmlcolumn.value('(/Root/XMLValue2)[1]', 'varchar(max)') AS XMLValue2
,xmlcolumn.value('(/Root/XMLValue3)[1]', 'varchar(max)') AS XMLValue3
,xmlcolumn.value('(/Root/XMLValue4)[1]', 'varchar(max)') AS XMLValue4

FROM dbo.TableName WITH (Nolock)

In the example above, we query the content of the xml column (xmlcolumn) for the each value and bring back a normal looking table for further manipulation.

Obviously, traversing the nodes within the detail elements of the xml was slightly more complex and was done using cross apply to return to the node which contained the collection, like this:

SELECT UniqueIDColumn
,cast(R.i.query('./XMLValue1[1]/text()') as varchar(max)) as XMLValue1
,cast(R.i.query('./XMLValue2[1]/text()') as varchar(max)) as XMLValue2
,cast(R.i.query('./XMLValue3[1]/text()') as varchar(max)) as XMLValue3
,cast(R.i.query('./XMLValue4[1]/text()') as varchar(max)) as XMLValue4
,cast(R.i.query('./XMLValue5[1]/text()') as varchar(max)) as XMLValue5

from dbo.TableName WITH (NOLOCK)
cross apply xmlcolumn.nodes('/Root/Node2/Node3/Node4') R(i)

In this example we don't see any reference to the xmlcolumn until we see the 'cross apply' - there we see that the xml column has a different syntax, we are joining the column to itself using the table valued function (R) and navigating down the (i) axis for the nodes. This all begins to look a little 'CUBE-esce' when you think about it.

Microsofts official word on the use of the APPLY keyword, says
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

For more info on the APPLY operator, refer to MSDN SQL Server Developer center

I suspect this will be very useful, its just a matter of time before an application outside of my pet project warrants its usage.

Not yet had a chance to look at performance of this process - its not really an issue for my project - this is all happending 'under the hood' in a pre-processed manner, rather than being done at runtime in any live queries.

Comments are welcome!

Object Search in SQL Server Management Studio

When the query writing tool of choice switched from Query Analyzer (SQL Server 2000 and below (8.0) ) to SQL Server Management Studio (SQL Server 2005+ (9.0)), along with it went the ability to search for columns in tables in a quick way.

Thankfully, there is a way to search the database you are currently in for those hard to find columns. This is particularly handy when getting to grips with new databases where you only know a bit of the column name. It's just a bit of a shame the GUI has gone.

Below is a script which will create you an SP that you can pass a partial column name. Obviously, this is geared towards a SQL Server 2005 box, change the nvarchar(max) to nvarchar(255) or something similar in SQL Server 2000.

It will return you the column which matches your search and the table its in.

Syntax for this is : EXEC TSQLColumn_Search 'search_term_here'

use
go

if exists (select 1 from sysobjects where name='TSQLColumn_Search')
begin
drop procedure TSQLColumn_Search
end
go

create procedure TSQLColumn_Search (@strColumnName nvarchar(max))
as
set transaction isolation level read uncommitted
select c.name as Column_Name
,o.name as Table_Name

from sys.all_columns c with (nolock)
inner join sysobjects o with (nolock) on o.id = c.object_id
inner join syscomments co with (nolock) on co.id = o.id
where c.name like '%'+@strColumnName+'%'
go


Enjoy!

Design Time Stylesheets for User Controls

Some people can be slightly pedantic when it comes to writing code and would like to have the utopian idea of a build with zero errors and zero warnings. I'm definately on this spectrum.

Visual Studio doesn't make this easy for you in some circumstances. Styling and Theming has had some excellent advancements in the last couple of iterations of the Visual Studio environment but how do you get round the issue of design time css warnings? You don't want to add the stylesheet to every control as it will output to the page in runtime but if you don't add it the VS doesn't know about it and can't validate your class names.

Well the first option is to turn off the warnings. This I wouldn't call elegant as you are then missing out on part of the validation process and the warnings can be used to your benefit. To do this click Tools -> Options. Expand the Text Editor item, then HTML, then Validation. Untick the 'as warnings (HTML, CSS)'. This plasters over the cracks which might be enough for you. But eventually the cracks will widen and it would have been really useful to see into the crack and find out what was pushing it wider.

The most elegant way is to take advange of Visual Studios rendering architecture. This code shows you how, those eagle eye'd among you will see the solution straight away.

<link href="mainStyle.css" rel="stylesheet" type="text/css" runat="server" id="lnkcss" visible="false" />

Link in the stylesheet to the user control and add the runat, id and then the attribute which makes all this worth while the visible="false". Using the visible=false the rendering engine will not output any part of the tag and then you've made a design time stylesheet. As the style sheet has no state management and no events there would only be a marginal increase to the memory usage for the site. And if you're that bothered about memory management then shut up and refactor your code again.

I'm not saying this is a solution for every situation but if the shoe fits....

Script to kill all processes for a database except your own

Declare @sql varchar(100)
Declare @dbname varchar(100)
Declare @spid int
set @dbname = 'PeninsulaukSP1'
DECLARE CursorQuery CURSOR FOR
SELECT spid FROM master..sysprocesses WHERE dbid = DB_ID(@dbname) AND spid != @@SPID
OPEN CursorQuery

FETCH NEXT FROM CursorQuery
INTO @spid
PRINT 'Starting Kill Process'
WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = RTRIM(convert(varchar(3),@spid))
PRINT 'Killing Process ' + @sql
EXEC('KILL '+ @sql)
FETCH NEXT FROM CursorQuery
INTO @SPID
END
CLOSE CursorQuery
DEALLOCATE CursorQuery

Derived Controls and Maintaining State

Sometimes you need to extend controls. One way is to add properties to a control. I wanted to use a checkbox in a item template in a gridview. I wanted the checkbox to fire an event similar to the row command event. Unfortunately the checkbox does not fire a rowcommand event or has a commandname/command argument.

The solution I came up with was to a add an extended checkbox with a commandargument property. This is straight forward. Just include this in your App_Code folder.


Public Class GridviewCheckBox
Inherits CheckBox
Private _CommandArgument As String
Property CommandArgument() As String
Get
Return _CommandArgument
End Get
Set(ByVal value As String)
_CommandArgument = value
End Set
End Property


Protected Overrides Function SaveViewState() As Object
Dim arrState(2) As Object
arrState(0) = MyBase.SaveViewState()
arrState(1) = Me.CommandArgument 'that is my custom property to be saved
Return arrState
End Function

Protected Overrides Sub LoadViewState(ByVal savedState As Object)
If Not savedState Is Nothing Then
Dim arrState() As Object = savedState
Me.CommandArgument = CType(arrState(1), String)
MyBase.LoadViewState(arrState(0))
End If
End Sub



End Class

In order for your property to be persisted across postback you need to override the LoadViewState and SaveViewState functions.

To use the control in your page register it at the top of the page (or in the web.config)
<%@ Register Namespace="CustomControl" TagPrefix="cc" %>


This can now be placed in the item template of a gridview and fire off an event when checked

<cc:GridviewCheckBox runat="server" ID="chkCheckList" AutoPostBack="true"OnCheckedChanged="chkCheckList_CheckedChanged" Checked='<%# eval("deleteflag") %>' CommandArgument='<%# eval("CheckListID") %>'/>



This can be handled in the code behind and you can use Naming container properties of the checkbox to access the parent gridviewrow and gridview to get the datakey values.


Protected Sub chkCheckList_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)

Dim chk As CustomControl.GridviewCheckBox = CType(sender, CustomControl.GridviewCheckBox)
Dim wsBW As New wsBusinesswise.BusinessWise
Dim CheckListID As Integer

Dim row As GridViewRow = CType(chk.NamingContainer, GridViewRow)
Dim gvw As GridView = CType(row.NamingContainer, GridView)

CheckListID = gvw.DataKeys(row.RowIndex).Values("CheckListID")

End Sub

Breaking on Handled exceptions

Sometimes errors get trapped and nothing gets done with them so it can take a little while to find where the error is being swallowed. When in debug mode you can set VS to break on any exception even if is in a try catch statement. Go to Debug > Exceptions and you get the window below. If you tick the "Thrown" checkbox for Common Langauge Runtime Exceptions, VS will break on .net exceptions.



I haven't used this in detail so I don't know how useful this is or whether it works when calling a web service.

Dataset Visualiser in debug mode

This is a nice tip for looking at datasets in debug mode. Sorry the images are so small. Step into your code after the dataset has been filled and put the name of the dataset in the watch window like below.



If you look at the dataset in the watch widow you will see a little magnifying glass in the right of the row. Click on that and you get an option for a dataset visualiser. The dataset now pops up in a grid making it easy to look at. There are also XML and Text visualisers.

What's in a Title?

Here's a scoop - check out the daft things users type when given a 'Title' textbox next to Forename and Surname ones. The importance of data entry validation shows itself...

I want to find the single Chief that we have and shake his hand. How!

(null) 95499
MR 52971
Mrs 31700
Miss 16545
Ms 9052
Dr 388
Mr & Mrs 265
Mother 59
Mr and Mrs 38
wife 38
mum 34
Mr/Mrs 31
Sister 30
PARTNER 26
FATHER 25
Husband 23
Emp 22
XXXX 22
123 18
- 17
M/s 17
Mr&Mrs 16
Daughter 15
Mis 14
M 14
a 13
Mrs 12
Brother 11
Mr/s 11
Son 11
Test 10
Mrs (Wife) 10
Nr 10
Mr 10
xxx 9
tba 8
Rev 8
f 8
H 8
n/a 8
MR / MRS 7
Friend 7
D 6
Dad 6
MR+MRS 6
Revd 6
Prof 6
PARENTS 6
Revd Dr 5
RMN 5
Mr Mrs 5
Mt 5
Mrs (mum) 5
Mrs/Mr 5
c 5
Canon 4
Ms 4
g 4
Mrs & Mr 4
Mr. & Mrs. 4
Playcarer 4
t 4
xx 4
y 4
TBC 3
q 3
r 3
Revd Canon Dr 3
Reverend 3
RGN 3
Mr. 3
MR OR MRS 3
Mrd 3
mre 3
Mrs & Mrs 3
office manager 3
matron 3
Me 3
j 3
fd 3
gg 3
Director 3
Customer Rep 3
e 3
. 3
213 3
b 3
Boyfriend 3
Childcare Worker 2
Baron 2
Baroness 2
0 2
(Friend) 2
(mother) 2
Title 2
miss 2
EN 2
Doctor 2
Drs 2
dtrdrtd 2
Dr & Mrs 2
CLEANER 2
Fr 2
gf 2
hj 2
Joiner 2
k 2
LORD 2
Kitchen Manager 2
Lady 2
MIISS 2
Mirs 2
Mr + Mrs 2
Mr (Dad) 2
Missq 2
Office Supervisor 2
n 2
nursery assistant 2
nMr 2
Mrs` 2
MSS 2
Mr,Mrs 2
Mrt 2
mrr 2
Mra 2
Mrage 2
MRS / MR 2
Mr. & Mrs 2
RMN/RGN 2
s 2
Receptionist 2
Playleader 2
ON FILE 2
uygyguy 2
vv 2
The Revd 2
The Revd Dr 2
tdtrd 2
yguy 1
yuiy 1
The Ven 1
Tile 1
Title 1
ton 1
trtMs 1
tt 1
u 1
unknown 1
uyb 1
uyguyg 1
WAITER 1
vbv 1
vdfs 1
Working 1
x 1
xcv 1
tbq 1
t Miss 1
Tania 1
Test bb 2 1
Test PB 1
tfyf 1
The Rev 1
Spare 1
ss 1
Stacey 1
STEP FATHER 1
Step-father 1
Stores Manager 1
SUPERVISOR 1
support worker 1
ooo 1
p 1
p.t waitress 1
PARENT 1
Playleader/Co-ordina 1
Plumber 1
Production Editor 1
Rev'd 1
QA Manager 1
PCO 1
SAMEMRS 1
SAsa 1
school 1
sd 1
sdf 1
SIR 1
Rob 1
rrt 1
Ruby 1
ruir 1
RHR 1
REVEREND CANON 1
rewq 1
Revd Prof 1
Mr, 1
Mr, Mrs 1
Mr./Mrs. 1
Mr.Mrs 1
Mr/ Mrs 1
Mr//Mrs 1
MR S 1
MR& MRS 1
Mr and Mrs Stokes 1
MR BONSER 1
Mr Cox 1
Mr Krys 1
Mrs and Mr 1
Mrs and Mrs 1
Mrs Emilia Sobon 1
Mrs Rai 1
Mrs Ruth 1
Mrs Stacey 1
MRS WEBB 1
Mrs, 1
Mrs. 1
Mrs/ Mr 1
Mr` 1
Mr/Mrs Biggs 1
Mr/Mrs/Miss 1
Ms (mum) 1
Ms, 1
Ms. 1
Mrsd 1
Mrsel; 1
MRSS 1
Mrs (sis) 1
NO ONE 1
NONE 1
Not Advised 1
NAN 1
New 1
NK 1
oijoi 1
Nrs 1
My 1
Mz 1
Mn 1
Monika 1
Mother-in-Law 1
Mr &Mrs 1
Mr ( husband) 1
Mr (Grampa) 1
Mr (Husband) 1
Mr (Son) 1
Mr * Mrs 1
Mr / Miss 1
Mr 3 1
Mr /s 1
Misses 1
Minn 1
M s 1
M.D. 1
Messers 1
Mf 1
Mfrs 1
Mg 1
Miaa 1
Miis 1
Laird 1
Laura 1
Lesley 1
li 1
Logistics 1
kjh 1
kkijdhh 1
kpok 1
Kylie 1
Lynn 1
Karl 1
MA 1
Major 1
MANAGERESS 1
Managing Director 1
Joss 1
hjHJ 1
hjj 1
Jamie 1
Janine Smith 1
jhgfhfg 1
jij 1
jim 1
i 1
Ian 1
IE test 1
IEMRS 1
ii 1
iijio 1
ij 1
ijo 1
IN LAWS 1
ineMrs 1
ITHMR 1
iuhuihu 1
IV12 4SUMs 1
gfhjg 1
head of department 1
hg 1
hgfhgf 1
Hh 1
ghffghfgh 1
GIRLFRIEND 1
Gp Capt 1
Gran 1
grandma 1
GRANDMOTHER 1
GRIFM 1
fdeb 1
fdfsdf 1
fdsaf 1
fdxfdxfd 1
fg 1
fgbfgb 1
fgccf 1
fh 1
Finance Manger 1
Financial Controller 1
fitter 1
Fleet Maintenance 1
FMRS 1
gbed 1
Gemma 1
EMPLOYEE 1
fbf 1
Cllr 1
Club Secretary 1
COUSINE 1
coverer 1
Cptn 1
DO NOT USE 1
dafgfdg 1
dd 1
ddd 1
Debbra 1
Denneyu 1
Dennis 1
Deputy Matron 1
Deputy Playleader 1
Design Editor 1
df 1
dfbv 1
DICKINSONMRS 1
Dr / Mrs 1
Driver 1
ennet RoadMrs 1
er 1
Escort 1
efv 1
Eleonor Calciati 1
emma 1
MISS 1
(fiance) 1
(flatmate) 1
(Parents) 1
(wife) 1
,MBMNV 1
? 1
` 1
`MR 1
|Mr 1
|Mr & Mrs 1
000 1
00123 1
0018 1
01407 740035Mr 1
02380 704478Mr 1
036 1
07801 245060Mrs 1
0788 263 6231Mr 1
0MRS 1
1 Miss 1
1192 1
231 1
255 1
28 7128Mr 1
3 1
5 1
716329 1
7803854349Mrs 1
7875 294758Mrs. 1
BDE 1
BiMr 1
bob 1
1234 1
147 1
189 1
2 1
204 1
aa 1
Admin Manager 1
Airport Supervisor 1
alison 1
Assistant Editor 1
Assistant Manager 1
AUNT 1
Aunty 1
Chris 1
Business Dev Manager 1
Bright 1
B IN LAW 1
Canon Dr 1
Capt 1
CARER 1
Centre Coordinator 1
CentreCoordinator 1
CEO 1
Chef 1
Childcare coordinato 1