So its been a fair while since my last blog post and boy have I been busy. Here are a few items that I have to write about soon, so as not to forget what the hell it is I’ve been so busy with lately.

1. Upgrade to Visual Studio 2005 (yes I know this may seem long overdue but I think my timing is about right)
2. Upgrade to SQL Server 2005
3. Upgrade of an existing ASP.NET project from VS 2003 to VS2005 (man, I can’t believe what a pain in the arse this has been)
4. System development update. Its been a busy month, as you can probably tell…

Well, the good news is that I’ve done all of the above and everything is working well.

The upgrade to SQL 2005 was fairly straighforward actually and yes, I’m running both SQL 2000 & 2005 happily on the same server.

The Visual Studio upgrade was less than satisfacory. Especially as I installed a version pre SP1 and then found that VS 2005 pre SP1 had a totally different concept of what the structure of a web project should be, so when I tried to upgrade an existing project from 2003 to 2005 the result was an utter mess. I then found that the in order to create a VS 2003 project in 2005 there was an add-in? A freakin add-in Microsoft? WTF?

I then found that VS SP1 had the add-in built in (YEY! Way to go MS!), so I ripped the offending pile of cack that was VS2005 pre SP1 off my PC and installed VS 2005 sans SP1. This was much more satisfactory.

I then opened an existing project and it coverted into what resembled an ASP.NET web application. I don’t know what MS was thinking when they created the new website project (well, actually I do and I’m going to save that rant for another post).

Anyway, I now have VS2003 and VS2005 IDEs installed and happily co-existing, which is a real plus. Also, the SQL Server 2005 client tools, which used to be called enterprise manager, are installed on the same PC as the old Enterprise manager and they both work fine. The bset thing is that I can use the new SQL 2005 client tools to administer both 2000 and 2005 databases, good stuff.

I’ve been putting this off for as long as possible, but had to bite the bullet mid 2007 as VS 2008 and associated .NET platforms will be with us soon and I didn’t fancy the idea of ugrading from Visual Studio 2003 to 2008 (nightmare!)

I’m currently working on a project where we are importing data transactions on a daily basis and need to compare the imported file with the current file. One of my favorite SQL clauses is the NOT IN clause, which I have used many times to do comparison type queries. Quite often, this powerful clause will help to simplify a system so much that its almost unbelievable.

This is how it looks;

SELECT DISTINCT
 resort,
 account,
 seqno,
 amount,
 pcode,
 ptype,
 pdate
FROM dbo.revenueMaster_import
WHERE dbo.revenueMaster_import.rowKey NOT IN(
 SELECT DISTINCT
  dbo.revenueMaster_archive.rowKey
 FROM
  dbo.revenueMaster_archive)
ORDER BY dbo.revenueMaster_import.seqno asc

This query shows all of the distinct rows from dbo.revenueMaster_import that are NOT IN dbo.revenueMaster_archive. There are many ways you might need to compare data like this. I’ve created many systems that utilize this technique to help keep a systems table structure to a minimum and therefore reduce a systems complexity.

Happy memorial Weekend!

I’ve had to use the SQL Server Reporting Services Web Services for a number of projects recently. Just to recap, if you don’t want to or can’t use the url based report access feature, you can render reports using a call to the reportService web service.

Here’s the code that will do that for you;

‘now we are going to try and create the report
‘using rs.web services
Dim rs As New trstpw1.reportservice.ReportingService

’setup the ntlm login credential
Dim myCred As New NetworkCredential(”DomainUser”,”DomainPassword”, “SQLDomain”)
Dim myCache As New CredentialCache

myCache.Add(New Uri(”ReportServerURL”), “NTLM”, myCred)
rs.Credentials = myCache

Dim format As String = “PDF”
Dim result As Byte() = Nothing
Dim credentials As DataSourceCredentials() = Nothing
Dim warnings As Warning() = Nothing
Dim streamIDs As String() = Nothing
Dim sh As New SessionHeader
rs.SessionHeaderValue = sh

‘we have 1 parameter, which is the type of letter

Dim params(0) As trstpw1.reportservice.ParameterValue
params(0) = New trstpw1.reportservice.ParameterValue
params(0).Name = “LETTER”
params(0).Value = Me.dlLetters.SelectedItem.ToString

‘first we open the envelope
result = rs.Render(”ReportServerReportPath” + “ENV”, format, Nothing, Nothing, params, credentials, Nothing, “”, “”, Nothing, warnings, streamIDs)

Dim estream As FileStream = File.Create(Request.PhysicalApplicationPath + “\PDF\” + “ENV-” + Me.dlLetters.SelectedItem.ToString.ToUpper + “.pdf”, result.Length)

estream.Write(result, 0, result.Length) estream.Close()

‘now we do the report
result = rs.Render(”ReportServerReportPath” + Me.dlLetters.SelectedItem.ToString.ToUpper, format, Nothing, Nothing, Nothing, credentials, Nothing, “”, “”, Nothing, warnings, streamIDs)

Dim stream As FileStream = File.Create(Request.PhysicalApplicationPath + “\PDF\” + Me.dlLetters.SelectedItem.ToString.ToUpper + “.pdf”, result.Length)

stream.Write(result, 0, result.Length)
stream.Close()

I’m looking at a new project for an existing client that will incorporate SQL Server Reporting Services. I did a project some time ago for another client using reporting services and its a very cool product. About time we had a decent tool for deploying reports over the web from Microsoft.

Anyway, I’m converting about 8 reports from a Microsoft Access system to reporting services and am doing a quote now. The system is actually an intranet/ extranet service, so the report are not publicly available.

You can find out more about SQL Server Reporting Services at the Microsoft website. Well, that link is for SQL 2005. Judging by the amount of info you can now find regarding SQL 2000 reporting services, it looks like MS have killed it! Good old MS!

I posted a while ago about a client of mine that has a custom built system with SQL Server 2000 on the back-end. Well, I was working on it today and thought I might post a little TSQL code here for your viewing pleasure…

CREATE PROC stpDropDownEmployees_MGR (@FirstRowText nvarchar(50) = ‘—ALL ACCOUNTS—’ )
AS
SELECT 1, ‘-1′ AS [VALUE], @FirstRowText AS [TEXT]
UNION ALL
SELECT 2, EmployeeID as [VALUE],
CASE WHEN EMP_Terminated = 1 THEN EMP_Lastname + ‘, ‘ + EMP_Firstname + ‘ *’
ELSE EMP_Lastname + ‘, ‘ + EMP_Firstname
END as [TEXT]
FROM EMPLOYEES
WHERE EMP_Terminated=0 AND EmployeeID NOT IN(2,5)
ORDER BY 1, 3
GO

I’ve been working as a professional developer since 1991 (yikes!). I started out developing databases using Microsoft Access 1.1 and quickly moved to SQL Server on the backend.

One of the cool things about SQL Server is the power of Transact SQL. I’m a Visual Basic programmer and don’t profess to be an expert in TSQL. I’ve just been doing some updates on my new clients database and the previous developers where experts in TSQL. Seeing it being used to its full potential is really an eye opener. So powerful and so flexible.

One of my newest clients is a debt collection agency and they have developed an intranet system for managing their business.

I have just taken over the day-to-day management of the system, providing code updates & modifications. This is one of those scenarios where you kind of hope that you will be able to get up-to-speed quite quickly on what the previous developers have done…

So far, so good!