As part of our ongoing Sharepoint project, I've decided to go all modern - the base OS is 64 bit Windows Server 2008 R2 Enterprise and the database engine is 64 bit SQL Server 2008. However, as soon as you try installing Sharepoint Server 2007 with a vanilla disk, you'll run in to problems - 2008 R2 expects Sharepoint to be service packed up, and at the moment there's no media available with SP1 or SP2 embedded (in fact there's no media avaliable at all on the Volume Licensing site at all, just a note telling you that for the moment they'll ship you the physical media for free - WTF is going on at Redmond at the moment?)

Luckily, there is a pretty easy way to get around the problem.

• Copy your install disk to a hard drive
• Download Sharepoint Server 2007 SP2 here
• Run the following command from the command prompt (cd to the directory where your download is stored first): officeserver2007sp2-kb953334-x64-fullfile-en-us.exe /extract: [Location of files copied from installation disk]\Updates
• Run setup.exe from the copied installation media root

Job done

If you're troubleshooting issues on SQL Server, you may find the following script useful. It will list all of the tables in a given database, giving their size, allocated space, index space, unused space, and a row count... Hope it helps you out!

USE {your_database_name}

SET NOCOUNT ON

CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))

DECLARE @DBname varchar(80)
DECLARE @tablename varchar(80)

SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + @@ServerName + ' / ' + @DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'

OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @tablename

-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @tablename
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

SELECT Tblname 'Table',
TblRows 'Row Count',
TblReserved 'Total Space (KB)',
TblData 'Data Space',
TblIndex_Size 'Index Space',
TblUnused 'Unused Space'
FROM #tblSize
Order by 'Table'


DROP TABLE #TblSize

I spend a large proportion of my day in the guts of various database servers. For most tasks, SQL Management Studio is a fine product. However, as much as Microsoft may protest differently, SQL Server is not the only database product on the market. I therefore need something a bit more flexible. I've tried a lot of the usual RDBMS suspects, but to be honest, none of them have really floated my boat. That is until I found Aqua Data Studio.

ADS supports probably the widest range of database systems I've ever seen. All of the usual suspects are there - SQL 7, 2000, 2005 and 2008 are all supported, as are all flavours of Oracle back to 8i. More important to me though is native support for mySQL. As the shot below shows, there are a huge number of other systems supported too:

If you're working for a number of clients, it's also worth noting that the software is available for Windows, Linux, Solaris, OSX and as a Java binary, meaning that you're always going to be able to use it. This, for me, makes it ideal for a training and education environment.

The big selling point for me is the ability to work with different databases in the same environment. It's a lot easier to visualise migration and data transformation requirements when both interfaces look the same!

If you like the sound of all this, pop over to their site - there is a fully functional demo version available to try.

As for ease of use, well, it has a similar learning curve to its proprietary brothers and sisters - if you don't know how to do something in SQL Management Studio, you probably won't be able to do it here. If you can, you will.

If you're in any way involved with coding, you'll probably have your own preferences with regards to a development environment. IDE's (Integrated Development Environments) range from a simple editor such as Notepad or vi, all the way up to full studios like Microsoft's Visual suite. In many situations, a fully fledged environment is overkill. However, it's also nice to have a few time saving features. This is where Notepad++ sits.

The program supports all of the popular programming and scripting languages, and will highlight them appropriately. There are also a vast range of community produced plugins available, as well as the standard integrated ones like the Hex editor and file explorer.

Obviously, you wouldn't want to write an entire operating system using it, but for some down and dirty editing, it's a life saver.

You can download Notepad++ from SourceForge

As you'll probably know, vbScript has no defined data types - they're all interpreted as required. Sometimes 12.3 may be a number, sometimes it's a string. This is all well and good, but what happens when you try to pass variables from vbScript into a more structured environment?

At the moment, I am currently writing a web front end to sit over some archived financial data. The brief is fairly simple - the back end database is to be read only, and should be searchable by invoice amount, customer name, customer reference, and postcode. All fairly simple. As it's going to be used only sparsely, I've decided to go with URL variables. The web front end will be accessible to certain people via directory security, so there is no need to obfuscate the data.

So, we create the ODBC link, put together a front end page for the users, then pass the variables via the URL to the backend processing page. Here, the URL variables are used to create a dynamic SQL SELECT statement like this:

strSQL = "select gl_account, gl_amount, gl_batch_ref, gl_date, gl_description, gl_contra, gl_ref, gl_sub_ledger, gl_year from dbo.gl_transactions where gl_amount = " & amount2 & " order by gl_contra ASC"

So we then pass this to SQL Server, and this happens...

Problem - as you can see from the error message, SQL is treating the variable as a string and using this to do a select against a numeric field... Result - dead app!

So, how do we get SQL to recognise this as a number?

The first thing to try is CAST. This is an in-built SQL function that is used to transform variables from one type to another. So, we rewrite the query like this:

strSQL = "select gl_account, gl_amount, gl_batch_ref, gl_date, gl_description, gl_contra, gl_ref, gl_sub_ledger, gl_year from dbo.gl_transactions where gl_amount = cast(" & amount2 & " as decimal) order by gl_contra Asc"

You would think that converting the variable to a decimal would solve the problem. Well, you're wrong. If you have a look at this page (beware - it's huge, and will open in a new window) - Cast as Decimal, you'll see that the constrained column, gl_amount, contains hundreds of different values, not just the one we passed, which in this case was 18.69. If you look closer, you'll see that all of the values are in the range 18.50 to 19.49. It appears that even though you've specified a decimal, it's rounded it to the nearest integer, 19. Therefore, again, SQL is technically correct, as all of the values in the gl_amount column will round to 19...

We need to get more accurate. Fortunately, SQL includes a couple of useful data types. We could use float, but this would give us far more precision than we need. Instead, we'll use money. This'll give us the precision we need, without eating up too many resources. Have a look at this page Cast as Money for the results.

Hopefully you should now see why it's important to keep track of your variables, and wherever possible to declare them explicitly - it'll save you a lot of headaches in the long term!

My day to day job is working with the backend of our CRM and Finance systems to make sure they do what they are supposed to do, and on occasion what they are not supposed to do.

Our enrolment system contains the details for all of our students, so I decided to link the enrolment database to Active Directory to create student accounts (part of the code was in the previous post).

With this in mind, I knocked together the following SQL query to get the data I needed in to it's own table (note that I've removed some sensitive data and replaced it with asterisks):

SQL Query

I've stripped out most of the stuff that took me hours to write and left the WHERE clause below...

WHERE (((dbo.StudentDetail.AcademicYearID)='07/08')
AND ((dbo.Offering.AcademicYearID)='07/08')
AND ((dbo.Enrolment.CompletionStatusID)='1')
AND ((dbo.Enrolment.IsWBL)=0)
AND ((dbo.Offering.OfferingStatusID)='1')
AND ((CollegeLevel_1.LevelNum)='2')
AND ((dbo.CollegeLevel.LevelNum)='1'))
AND (dbo.site.description !='*****')
AND (dbo.site.description ='*****')
AND (dbo.site.description !='*****')
AND (dbo.site.description !='*****')
AND (dbo.site.description !='*****')
AND (dbo.site.description !='*****')
AND (dbo.site.description !='*****')
AND (dbo.site.description !='*****')
AND (dbo.site.description !='*****')
AND (dbo.site.description !='*****')
ORDER BY dbo.StudentDetail.RefNo, dbo.StudentDetail.Surname, dbo.StudentDetail.FirstForename, dbo.Offering.AnnualGLH DESC;

The code was duly integrated in to our network, and at the start of enrolments I was expecting about 600 new accounts a day...

After a week, 4 accounts!

This is why it always pays to bug check your code, because if I had, I would have noticed the missing ! and saved a week of headscratching and swearing at the server...

For 6 months, I'd had intermittent reports of a problem with one of our bespoke apps. Every so often, the server would grind to a halt, and would stay frozen for anything up to 5 minutes. The box at the time was running SQL Server 2000 on a dual Xeon setup with 4GB of RAM.
As part of a general server refresh, and in part to alleviate this problem, I decided to shift the app over to a dedicated blade in our new HP Blade Centre. I also got sign off from the application developer to shift it over to SQL Server 2005. With that in mind, I configured the mother of all servers - Dual Quad Core Xeons, 16GB RAM, Teamed Dual 1GB LAN, two 15000RPM mirrors (for the OS and Transaction logs) and 500GB in a RAID5 array for the data files.
I installed the OS (2003 Enterprise), and SQL Server 2005 (again the Enterprise version) and transferred the data over...

For the first 3 or 4 hours, everything ran smoothly. I then started getting calls that the app was locking up, and that it was refusing new login attempts. Putting this down to some initial teething problems, I restarted the server... Everything was rosy again... For another couple of hours...

Firing up Activity monitor, one thing looked wrong instantly - RESOURCE_SEMAPHORE wait types (see below)

This wait type, as you may well know, indicates a lack of available resources (either processor time or RAM usually) to complete the query! It appeared that quadrupling the amount of RAM and the number of cores available had crippled the app!

Over the next three or four days, in conjunction with the application developer, I tried just about everything - setting the MAXDOP value to pretty much every possible value as outlined by MS here, restricting the app to fewer cores, reconfiguring AWE - you name it, I tried it!

Over the course of the week, I started collecting the wait stats - if you're familiar with SQL 2005 at all, you'll know that these indicate a major problem!

As you can see, the RESOURCE_SEMAPHORE waits appear to be generated by MS Access - the front end of the app is an MDE file that uses passthrough queries to communicate with the server. Having had major headaches with Access before, this was where I decided to focus my energy. Running some Wireshark network traces showed one particular query whose transmission seemed to coincide with a server slowdown. I won't list it in full - you can see it here if you really like! Every time it ran, a corresponding kernel CPU spike appeared on the SQL server (in red on the graph).

It appears that the query in question is fairly CPU intensive, and this, coupled with the way Access passthrough queries work, meant that huge amounts of memory and CPU time were being used to no purpose - this is shown by the huge ASYNC_NETWORK_IO wait times.

To prove this was the case, we created passthrough queries using the rogue code on a few machines...

We killed an 8 core, 16GB state-of-the-art blade with 23 connections!

The query in question ran when a specific screen was opened. Due to changes in the way resources are handled between SQL 2000 and SQL 2005, the failure threshold on 2000, even on a lower spec machine, was around 45 connections, which is why we'd seen it much less frequently.

All in all, myself and the developers identified another three queries that caused the same issue, and we managed to rewrite them. The application now handles 200 desktop users and hundreds more web connections without breaking a sweat...

It just goes to prove though that bigger is not always better!

This is a long video (nearly 2 hours!) but if you've got the time, it's worth watching...

We run a "bespoke" application - bespoke in that it seems to be a load of Access databases badly ported in to SQL Server. It also allows users to create their own reports by dragging and dropping columns. At 4:30, I get a call saying that the server has ground to a halt, and all 200 users have been kicked out of the app...

God knows how many cross joins you need to make a file that size from 2mb of initial data...