Our Blog

SSIS – Error 0xC020200E – Cannot open the datafile “PathToFile”

This error presented itself when attempting to run an SSIS package that needed to write to a file share across the network to another server. The package worked when executed using my local account from Visual Studio but not when invoked from a stored procedure or via SQL Server Agent. A fully qualified UNC path was being used. The Agent job Owner…

SSRS 2008 – index was outside the bounds of the array / Data source does not exist

Experienced this generic message from Bids / Visual Studio 2008 during a Reports migration project. No further information was provided. Most discussions online mention resolutions around report caching which wasn’t applicable in my case. I resorted to the classic trial and error method – hack away removing objects from the report until the error message changed! After removing everything from the report bar the main…

SQL Server – Dangers of Delete

To minimise the risks that go with deleting data, I’m in the habit of writing out and testing the WHERE  clause in a separate query first. Couple that with an explicit BEGIN TRANSACTION / ROLLBACK / COMMIT  and your data should be pretty safe from unintentional deletions. A piece of buggy T-SQL code that was shared with me from a production Stored Procedure illustrated an…

pgSQL vs T-SQL – Database Shrinking to reclaim space for the OS

Databases typically grow over time. One occasion where they may counter that trend is if obsolete data is permanently removed. In Postgres, Dropping or Truncating a table does clear its data file on disk (see $PGDATA/base/YourDbOID/ ). However in SQL Server it doesn’t. Nor does running a regular VACUUM in Postgres following bloat introduced from Updates and Deletes. Internally, the…

pgSQL vs T-SQL – Enforce uniqueness on a column but allow multiple nulls

A Unique constraint is used to enforce unique values across one or many columns, often applied against Alternate keys of a table. Sometimes you want uniqueness enforced only where data for a column is present, and not where values are Null, for example, when storing optional Person attributes such as National Insurance numbers or Driving Licence numbers. Section 5.3.3. Unique…

SQL Server – Casting Integers to Asterisk

An odd ‘feature’ of T-SQL that once sent me on a wild goose chase. If you try to cast an integer to a smaller length char/varchar data type, the database engine silently casts the value to an asterisk ‘*’. This is also true for when you rely on an implicit cast such as during an Insert.  

   …
SQLIO

You’ve spent thousands on a shiny new server. Before you go ahead and configure your database environment, now is a good time to tune the software side of your storage subsystem for optimal performance. SQLIO is a Command Line IO benchmarking utility for Windows which can help you do just that. For Linux, consider utilities such as fio or bonnie++.…

X2Go Connection Failure – Failure negotiating the session in stage ‘7’

X2Go is my favourite remote desktop solution for Linux, mainly because its so responsive. Hopefully I’ll write up a quick How To on getting it setup soon, but for now, here’s an issue I’ve just overcome after a couple of days of stress!   Issue SSH connection to my home server was working fine but X2go had stopped working. This…

Missing Sql Server Startup parameters

I once came across an issue with a local SQL Server instance that refused to start on my laptop. The Windows Event viewer reported ‘initerrlog: Could not open error log file ”. Operating system error = 3(The system cannot find the path specified.).‘ At the time I searched around on the net but couldn’t find any thing quick so I…