Sunday 11 March 2012
Oops
Crikey I'd forgotten I even had a blog. Needless to say, I've come a long way in the 18-24 months or so since I last updated it - starting a new job and graduating to use SQL Server 2008 R2, for one thing - so I'll hopefully be putting some more interesting and challenging topics up on here in the next few weeks.
Monday 20 September 2010
Using embedded VB code in a SQL report
Sometimes, you will find that you can't do what you need in T-SQL and you need to do some high level programming at report level. One way to acheive this is by using embedded Visual Basic.net code in the BIDS or Vsiual Studio design layer.
Most of the examples you'll find on the net will assume you can already program in VB.net and will simply show you how to create a vb function and incorporate it into your report. Well I'll attempt to show you both.
First of all, the basics - how to get to the code editor and write a function. In Visual Studio or BIDS (the included Visual Studio interface which comes with SQL Server), select Report from the top menu, and click on Report Properties. In there click on the Code tab and you'll see a VB Editor. There's no wizards or anything like that to help you out here so you'll need to code it by hand from scratch.
For starters, we'll create a basic function so I can show you how it works. In your code editor type the following to create a programming classic, the 'Hello World' app:
Public Function HelloWorld() as String
Return "Hello World"
End Function
Now in your report, create a new textbox, right click and select Expression. In the expression box type '=Code.HelloWorld()".
Preview your report and you can see how easy it is to get started.
Now we'll up our game and do something more complicated and more useful.
The problem I faced today was having a table in which one of the metrics was a field showing a number of seconds. I wanted to display this in words. For example, instead of showing 12494 seconds, I needed it to display as '3 hour(s), 28 minute(s) & 14 second(s)'. Its much more readable and meaningful for the end user.
As I was displaying this in a table, for the main details of the report I could do this calulation in T-SQL and in fact I created a functionto do this in T-SQL but when displaying the data in a table, I created a totals column. I can't then revert back to my SQL code to get the total. I tried using WITH ROLLUP but as I was working with averages, this didn't really work as I wanted so this left me with the problem of having to do the calculation in the report. To this end I created an embedded custom function in Visual Studio and the code is below.
If you take the time to compare this to my T-SQL equivalent, you'll see that its almost the same. The only real differences are syntactical so if you can code in SQL, you can do this level of VB function.
The main differences are:
1. Declaration of variables. In VB, use Dim instead of DECLARE
2. Use System.Convert.ToString(fieldname) instead of CAST or CONVERT
3. Use double speech marks instead of single
That's pretty much it. I'm not going to go into more detail because if I can work it out then so can you. Its really not that hard although it may seem scary at first.
Most of the examples you'll find on the net will assume you can already program in VB.net and will simply show you how to create a vb function and incorporate it into your report. Well I'll attempt to show you both.
First of all, the basics - how to get to the code editor and write a function. In Visual Studio or BIDS (the included Visual Studio interface which comes with SQL Server), select Report from the top menu, and click on Report Properties. In there click on the Code tab and you'll see a VB Editor. There's no wizards or anything like that to help you out here so you'll need to code it by hand from scratch.
For starters, we'll create a basic function so I can show you how it works. In your code editor type the following to create a programming classic, the 'Hello World' app:
Public Function HelloWorld() as String
Return "Hello World"
End Function
Now in your report, create a new textbox, right click and select Expression. In the expression box type '=Code.HelloWorld()".
Preview your report and you can see how easy it is to get started.
Now we'll up our game and do something more complicated and more useful.
The problem I faced today was having a table in which one of the metrics was a field showing a number of seconds. I wanted to display this in words. For example, instead of showing 12494 seconds, I needed it to display as '3 hour(s), 28 minute(s) & 14 second(s)'. Its much more readable and meaningful for the end user.
As I was displaying this in a table, for the main details of the report I could do this calulation in T-SQL and in fact I created a functionto do this in T-SQL but when displaying the data in a table, I created a totals column. I can't then revert back to my SQL code to get the total. I tried using WITH ROLLUP but as I was working with averages, this didn't really work as I wanted so this left me with the problem of having to do the calculation in the report. To this end I created an embedded custom function in Visual Studio and the code is below.
Public Function SecondsToWords (ByVal Seconds as Integer) as String
Dim Days as Integer
Dim Hours as Integer
Dim Minutes as Integer
Dim Words as String
Words=""
IF Seconds < 60 Then
Words = System.Convert.ToString(Seconds) + " seconds"
ElseIf Seconds >= 60 AND Seconds < (3600) Then
Minutes = Seconds / 60
Seconds = Seconds mod 60
Words = System.Convert.ToString(Minutes) + " minute(s), " + System.Convert.ToString(Seconds) + " seconds"
ElseIf Seconds >= 3600 AND Seconds <= 86399 Then
Hours = Seconds / 3600
Seconds = Seconds mod 3600
Minutes = Seconds / 60
Seconds = Seconds mod 60
Words = System.Convert.ToString(Hours) + " hour(s), " + System.Convert.ToString(Minutes) + " minute(s), " + System.Convert.ToString(Seconds) + " seconds"
ElseIf Seconds >= 86400 Then
Days = Seconds / 86400
Seconds = Seconds mod 86400
Hours = Seconds / 3600
Seconds = Seconds mod 3600
Minutes = Seconds / 60
Seconds = Seconds mod 60
Words = System.Convert.ToString(Days) + " day(s), " + System.Convert.ToString(Hours) + " hour(s), " + System.Convert.ToString(Minutes) + " minute(s), " + System.Convert.ToString(Seconds) + " seconds"
End If
Return Words
End Function
If you take the time to compare this to my T-SQL equivalent, you'll see that its almost the same. The only real differences are syntactical so if you can code in SQL, you can do this level of VB function.
The main differences are:
1. Declaration of variables. In VB, use Dim instead of DECLARE
2. Use System.Convert.ToString(fieldname) instead of CAST or CONVERT
3. Use double speech marks instead of single
That's pretty much it. I'm not going to go into more detail because if I can work it out then so can you. Its really not that hard although it may seem scary at first.
Thursday 2 September 2010
T-SQL function to convert seconds to words
Here's something I wrote to convert seconds as an integer into a usefully descriptive text format e.g. 2 hours, 3 minutes, 12 seconds.
There are various versions of this sort of thing on the web but until today I couldn't find this thing exactly so I decided to write it myself.
If you'd like some sort of an explanation as to how it works then here goes:
First job is to determine of the number of seconds is less than 60 because that's easy. Just show the number of seconds and concatenate the word ' second(s)' onto the end.
Next is to deal with anything between a minute and an hour. in terms of seconds that's 60 to 3600 except we're going to use 3599 so we're just dealing with seconds and minutes. The line that says SET @Minutes = @Seconds / 60 is calculating the number of minutes simply by dividing the number of seconds by 60.
The code SET @Seconds = @Seconds % 60 uses the MOD(%) function which is a funny by extremely helpful fella. MOD will return the remainder when you divide a number by another number.
For example, if we were dealing with 95 seconds, the line SET @Seconds = @Seconds % 60 divides 95 by 60 and returns the remainder which is of course 35.
So we've got the result of 95 DIVIDED BY 60 (1) and 95 MOD 60 (35) and that outputs 1 minute and 35 seconds (95 seconds).
The rest of the function simply expands on this premise to calculate hours and even days. You now have the tools to add weeks and years onto it or to convert it so that its converting minutes or miliseconds or whatever you want.
There are various versions of this sort of thing on the web but until today I couldn't find this thing exactly so I decided to write it myself.
CREATE FUNCTION fn_SecondsToWords (
@Seconds int)
RETURNS varchar(500)
AS
BEGIN
DECLARE @Words varchar(500)
SET @Words = ''
DECLARE @Minutes int
DECLARE @Hours int
DECLARE @Days int
-- less than a minute
IF @Seconds < 60
BEGIN
SET @Words = CAST(@Seconds AS VARCHAR(2)) + ' seconds'
END
-- between a minute and an hour
IF @Seconds BETWEEN 60 AND ((60*60)-1)
BEGIN
SET @Minutes = @Seconds / 60
SET @Seconds = @Seconds % 60
SET @Words = CAST(@Minutes AS VARCHAR(2)) + ' minute(s), '
+ CAST(@Seconds AS VARCHAR(2)) + ' seconds'
END
-- between an hour and a day
IF @Seconds BETWEEN 3600 AND 86399
BEGIN
SET @Hours = @Seconds / 3600
SET @Seconds = @Seconds % 3600
SET @Minutes = @Seconds / 60
SET @Seconds = @Seconds % 60
SET @Words = CAST(@Hours AS VARCHAR(2)) + ' hour(s), '
+ CAST(@Minutes AS VARCHAR(2)) + ' minute(s), '
+ CAST(@Seconds AS VARCHAR(2)) + ' seconds'
END
-- over a day
IF @Seconds >= 86400
BEGIN
SET @Days = @Seconds / 86400
SET @Seconds = @Seconds % 86400
SET @Hours = @Seconds / 3600
SET @Seconds = @Seconds % 3600
SET @Minutes = @Seconds / 60
SET @Seconds = @Seconds % 60
SET @Words = CAST(@Days AS VARCHAR(3)) + ' day(s), '
+ CAST(@Hours AS VARCHAR(2)) + ' hour(s), '
+ CAST(@Minutes AS VARCHAR(2)) + ' minute(s), '
+ CAST(@Seconds AS VARCHAR(2)) + ' seconds'
END
RETURN @Words
END
If you'd like some sort of an explanation as to how it works then here goes:
First job is to determine of the number of seconds is less than 60 because that's easy. Just show the number of seconds and concatenate the word ' second(s)' onto the end.
Next is to deal with anything between a minute and an hour. in terms of seconds that's 60 to 3600 except we're going to use 3599 so we're just dealing with seconds and minutes. The line that says SET @Minutes = @Seconds / 60 is calculating the number of minutes simply by dividing the number of seconds by 60.
The code SET @Seconds = @Seconds % 60 uses the MOD(%) function which is a funny by extremely helpful fella. MOD will return the remainder when you divide a number by another number.
For example, if we were dealing with 95 seconds, the line SET @Seconds = @Seconds % 60 divides 95 by 60 and returns the remainder which is of course 35.
So we've got the result of 95 DIVIDED BY 60 (1) and 95 MOD 60 (35) and that outputs 1 minute and 35 seconds (95 seconds).
The rest of the function simply expands on this premise to calculate hours and even days. You now have the tools to add weeks and years onto it or to convert it so that its converting minutes or miliseconds or whatever you want.
Friday 27 August 2010
Reporting Services Errors?
I'm constantly getting various errors within reporting services. If you're a high end user, its going to happen quite a lot. More often than not the error messages themselves are less than useful such as 'Done: n processed of n total; n errors' which is the issue I've been fighting with today.
That's all very well but if you're scratching your head wondering what the actual errors are and berating Microsoft for not being a bit more descriptive and helpful, all you need to do is check your log files.
They'll be somewhere like C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles and when you crack open these text files and manage to locate the report you're playing with (CTRL + F), you can see much more detail about your error. Of course its a drag if, like me, you're working in a system where there's five webfarms and you have to check them all but in the end you'll get there.
So the moral of this story is CHECK YOUR LOG FILES.
That's all very well but if you're scratching your head wondering what the actual errors are and berating Microsoft for not being a bit more descriptive and helpful, all you need to do is check your log files.
They'll be somewhere like C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles and when you crack open these text files and manage to locate the report you're playing with (CTRL + F), you can see much more detail about your error. Of course its a drag if, like me, you're working in a system where there's five webfarms and you have to check them all but in the end you'll get there.
So the moral of this story is CHECK YOUR LOG FILES.
Thursday 3 June 2010
SQL Server 2005 Collation conflict
Ever been faced with this error message?
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This arises from different databases being built on diffferent versions of SQL Server by different developers and when you wanht to make them talk to each other one of them says something like "I've no idea what he's on about, slap a Babel Fish in my ear and let's try again". The Babel Fish in question is a simple piece of code.
Usually this works: COLLATE database_default but sometimes for me I need to use COLLATE Latin1_General_CS_AS depending on what systems i'm working with.
Where is it used? Here:
SELECT
a.fieldname1
,a.fieldname2
,b.fieldname3
FROM table1 a
INNER JOIN table2 b ON a.id=b.id COLLATE database_default
WHERE id = 1
In case you didn't spot it, its sitting on the end of the INNER JOIN line. Sometimes you'll just have to experiment with where it goes. If you're using an inline query, it'll go in there. If you engaging in some complicated WHERE clause fruitiness, then it may need to go in there. Wherever it goes, its your saviour.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This arises from different databases being built on diffferent versions of SQL Server by different developers and when you wanht to make them talk to each other one of them says something like "I've no idea what he's on about, slap a Babel Fish in my ear and let's try again". The Babel Fish in question is a simple piece of code.
Usually this works: COLLATE database_default but sometimes for me I need to use COLLATE Latin1_General_CS_AS depending on what systems i'm working with.
Where is it used? Here:
SELECT
a.fieldname1
,a.fieldname2
,b.fieldname3
FROM table1 a
INNER JOIN table2 b ON a.id=b.id COLLATE database_default
WHERE id = 1
In case you didn't spot it, its sitting on the end of the INNER JOIN line. Sometimes you'll just have to experiment with where it goes. If you're using an inline query, it'll go in there. If you engaging in some complicated WHERE clause fruitiness, then it may need to go in there. Wherever it goes, its your saviour.
Tuesday 1 June 2010
So here we go with some useful DATETIME functions.
Dates and times are to be found at the very core of SQL programming, especiually when, like me, you're dealing with SQL Reports.
To convert a date time into zero hours and mins on that date:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
The example above uses GETDATE() but you could replace that with any DATETIME field to make the conversion scaleable.
You may want to set a report date start and end parameters to automatically be the first and last day of the previous week, or month, or the current month to date. These two cheeky little monkeys will give you the first and last days of the previous month:
SELECT dDATEADD(mm,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as FirstDayOfPreviousmonth
SELECT DATEADD(s,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as LastDayOfPreviousmonth
To get the dates for current month so far, use this as your start date:
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE()),0) as FirstDayOfCurrentmonth
and GETDATE() as your end date
What about the last full week (assuming here that you work from Monday to Sunday)?
SELECT DATEADD(ss,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as MondayMorningOfLastWeek
SELECT DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as LateLastSundayEvening
To find the start of the current financial year (In the UK, the financial year runs from 1st April):
SELECT DATEADD(month,-CASE WHEN DATEPART(month,GETDATE())>3 THEN DATEPART(month,GETDATE())-4 ELSE DATEPART(month,GETDATE()) + 8 END,DATEADD(month, DATEDIFF(month,0, GETDATE()),0)) as FiscalYearStart (This probably doesn't need to be quite so complicated as this, why don't you have a go at simplifying it?)
There are all sorts of cunning tricks you can do with DATETIMEs and some can be a bit confusing, at least until someone explains it so that's what we'll do here. Let's break down the first function I showed you:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
We'll work through it in tutorial style, so type SELECT GETDATE() into your SQL Server 2005 Management Studio query pane.
When I do this it gives me: 2010-06-01 15:26:37.937 which is the current date and time to the nearest milisecond. Now wrap that GETDATE function in a DATEDIFF so it looks like this: SELECT DATEDIFF(DAY,0,GETDATE()) and when you press execute it should output something like this: 40328
This number represents the DATE DIFFERENCE in DAYS between day zero and today. Day zero is January 1st 1900. Now we wrap that code in a DATEADD like so: SELECT DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())) which outputs 2010-06-01 00:00:00.000. What we've done there is to say take day zero, and add 40328 days onto it and because day zero is set to midnight, then when we add forty odd thousand days onto it, it still comes out as midnight.
So to put all that in laymans terms, we're saying How many days is it since 1st January 1900? Let's add that number of days ONTO 1st Jan 1900 to get the date and time of midnight today (or last night).
You can break down all the code above in similar terms and you'll soon have an understanding of how it all works.
Happy squirelling.
Dates and times are to be found at the very core of SQL programming, especiually when, like me, you're dealing with SQL Reports.
To convert a date time into zero hours and mins on that date:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
The example above uses GETDATE() but you could replace that with any DATETIME field to make the conversion scaleable.
You may want to set a report date start and end parameters to automatically be the first and last day of the previous week, or month, or the current month to date. These two cheeky little monkeys will give you the first and last days of the previous month:
SELECT dDATEADD(mm,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as FirstDayOfPreviousmonth
SELECT DATEADD(s,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as LastDayOfPreviousmonth
To get the dates for current month so far, use this as your start date:
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE()),0) as FirstDayOfCurrentmonth
and GETDATE() as your end date
What about the last full week (assuming here that you work from Monday to Sunday)?
SELECT DATEADD(ss,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as MondayMorningOfLastWeek
SELECT DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as LateLastSundayEvening
To find the start of the current financial year (In the UK, the financial year runs from 1st April):
SELECT DATEADD(month,-CASE WHEN DATEPART(month,GETDATE())>3 THEN DATEPART(month,GETDATE())-4 ELSE DATEPART(month,GETDATE()) + 8 END,DATEADD(month, DATEDIFF(month,0, GETDATE()),0)) as FiscalYearStart (This probably doesn't need to be quite so complicated as this, why don't you have a go at simplifying it?)
There are all sorts of cunning tricks you can do with DATETIMEs and some can be a bit confusing, at least until someone explains it so that's what we'll do here. Let's break down the first function I showed you:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
We'll work through it in tutorial style, so type SELECT GETDATE() into your SQL Server 2005 Management Studio query pane.
When I do this it gives me: 2010-06-01 15:26:37.937 which is the current date and time to the nearest milisecond. Now wrap that GETDATE function in a DATEDIFF so it looks like this: SELECT DATEDIFF(DAY,0,GETDATE()) and when you press execute it should output something like this: 40328
This number represents the DATE DIFFERENCE in DAYS between day zero and today. Day zero is January 1st 1900. Now we wrap that code in a DATEADD like so: SELECT DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())) which outputs 2010-06-01 00:00:00.000. What we've done there is to say take day zero, and add 40328 days onto it and because day zero is set to midnight, then when we add forty odd thousand days onto it, it still comes out as midnight.
So to put all that in laymans terms, we're saying How many days is it since 1st January 1900? Let's add that number of days ONTO 1st Jan 1900 to get the date and time of midnight today (or last night).
You can break down all the code above in similar terms and you'll soon have an understanding of how it all works.
Happy squirelling.
Introducing the SQL Soldier
Hi, I'm Nick Holt. I'm 37 and I'm an ICT Management Information Specialist working for a company in Manchester, UK. I use SQL Server 2000 and 2005, plus Visual Studio 2003 and 2005. Reporting Services is what takes up most of my time but i'm currently working towards certification in the form of a MCTS qualification in SQL Server 2005 Business Intelligence Implementation and Maintenance which incorporates Reporting Services, Integration Services and Analysis Services. I'll keep you updated on those as I come up with anything.
I also use MySQSL which, in conjunction with HTML, CSS and PHP, I use to code my own personal website which is dedicated to Bury Football Club and can be found at http://www.mannyroadend.co.uk.
The intention of this blog is to share bits of code I create, cool functions and cunning solutions to the sorts of problems you might face in your day to day SQL lives.
I also use MySQSL which, in conjunction with HTML, CSS and PHP, I use to code my own personal website which is dedicated to Bury Football Club and can be found at http://www.mannyroadend.co.uk.
The intention of this blog is to share bits of code I create, cool functions and cunning solutions to the sorts of problems you might face in your day to day SQL lives.
Subscribe to:
Posts (Atom)