Brad is also available on 'Simple-Talk' and 'Twitter'
Thursday, January 22, 2009
Free E-Book ''Mastering SQL Server 2005 Profiler'
Looks like its a free book frenzy today, Brad McGehee from RedGate has made available his new book 'Mastering SQL Server 2005 Profiler' from the link below.
Brad is also available on 'Simple-Talk' and 'Twitter'
Brad is also available on 'Simple-Talk' and 'Twitter'
Free SSIS 2005 E-Book from MS-Press
Its not often that you can get something for nothing but the good folks at MS-Press have made SQL Server 2005 Integration Services - Step By Step available as a free download from the link below, you do have to register to receive it but im sure most people interested in its contents already receive some mails from MS.
Friday, January 9, 2009
Starting to Study for MCTS 70-445 BI exam
Thanks to Andrew Coates, who has kindly sent me a voucher for a exam, i am jumping straight back into study with 70-445. This is the 2005 flavour of "Business Intelligence - Implementation and Maintenance", i already had my eye on the 2008 version but considering the study material is not released until April 1, I will go with 2005. This is not a major issue as my MCITP is in 2005 and i think its good to keep them all in sync.
My first step is to grab a copy of the MSPress Training Kit, i have used these kits for all my previous exams so will stick with them. after reading through this and doing all the exercises plus plenty of extra hands on work ill grab a sample exam from Self-test, who i have also used before, and ensure that i get around 90-95% before booking the exam with Coatsies magic ticket.
So you can expect to see plenty of blog posts as i work through the material. Fingers Crossed :)
Thanks again to Andrew for the voucher.
My first step is to grab a copy of the MSPress Training Kit, i have used these kits for all my previous exams so will stick with them. after reading through this and doing all the exercises plus plenty of extra hands on work ill grab a sample exam from Self-test, who i have also used before, and ensure that i get around 90-95% before booking the exam with Coatsies magic ticket.
So you can expect to see plenty of blog posts as i work through the material. Fingers Crossed :)
Thanks again to Andrew for the voucher.
Thursday, January 8, 2009
Sending 'FOR XML' into work for the post office
One way that HTML based email bodys have been constructed is by iterating through a cursor and building your HTML with each row, the trouble with this method is that it does not scale very well. In order to over come the lineal growth of a cursor, we can utilise 'For XML' and build our HTML content with a set based operation. This following post will compare both methods to show how this can be done.
Firstly we will build a email body using cursors and see how long this takes to run, We are simply going to build an email that lists all customers* in New South Wales, from the Adventureworks database, along with their email address and phone number.
The following SQL will display this:
This must now be built into a cursor and iterated in order to build our HTML:
The above approach using cursors works as expected and emails our report to the sales manager, however it is not going to scale very well, a more scalable solution is to generate the same html but using a set operation, utilising For XML as below
This code generates the same email many times faster with a lighter impact on our server, moreso, it is scalable as promised.
Firstly we will build a email body using cursors and see how long this takes to run, We are simply going to build an email that lists all customers* in New South Wales, from the Adventureworks database, along with their email address and phone number.
The following SQL will display this:
select
distinct
ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName)CustomerName,
Phone,
EmailAddress
from
Sales.vIndividualCustomer
where
StateProvinceName = 'New South Wales' This must now be built into a cursor and iterated in order to build our HTML:
Declare @Body as varchar(max)
set @Body = '<html><body>' +
'<h1>NSW Customers</h1>
' + '<table border="1">'
DECLARE Customers Cursor FAST_FORWARD FOR
select
distinct
ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName) CustomerName,
Phone,
EmailAddress
from
Sales.vIndividualCustomer
where
StateProvinceName = 'New South Wales'
Declare @CustomerName varchar(200),
@Phone varchar(25),
@EmailAddress varchar(50)
open Customers
Fetch Next From Customers into @CustomerName, @Phone, @EmailAddress
while @@Fetch_STatus = 0
begin
set
@Body = @body + '<tr> '
+ '<td>' + @CustomerName + '</td>'
+ '<td>' + @Phone + '</td>'
+ '<td>' + @EmailAddress + '</td>'
+ '</tr>'
Fetch Next from Customers into @CustomerName, @Phone, @EmailAddress
end
close Customers
Deallocate Customers
set @body = @body + '</table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorksProfile',
@recipients='adventureJo@adventure-works.com',
@subject = 'NSW Customers',
@body = @body,
@body_format = 'HTML' ;
The above approach using cursors works as expected and emails our report to the sales manager, however it is not going to scale very well, a more scalable solution is to generate the same html but using a set operation, utilising For XML as below
DECLARE @body varchar(max),
@Subject varchar(255)
SET @body =
N'<html><body><H1>NSW Customers</H1>' +
'<table border="1">' +
'<tr><th>Customer Name</th>' +
'<th>Phone</th>' +
'<th>EmailAddress</th>' +
(select
Distinct
ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName) td, '',
Phone td, '',
EmailAddress td, ''
from
Sales.vIndividualCustomer
where
StateProvinceName = 'New South Wales'
FOR XML PATH('tr')) +
N'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorksProfile',
@recipients='adventureJo@adventure-works.com',
@subject = 'NSW Customers',
@body = @body,
@body_format = 'HTML' ;
This code generates the same email many times faster with a lighter impact on our server, moreso, it is scalable as promised.
Subscribe to:
Posts (Atom)
_501.jpg)
_507_527_513_512.jpg){kind=small}
