Not the sexiest blog title in the world but I thought I’d knock up a little post on the behaviour of MySQL and SQL Server with integer subtraction. How would you expect a database system to behave with positive and negative data types? Microsoft SQL Server doesn’t really have unsigned data types. All integer types [...]

Colleague: “Can you have foreign keys referencing other databases?” Me: “Erm… I don’t know, but it I’ll find out.” I’ve always thought tables with these relationships should exist in the same database so I’ve never attempted this. But I don’t like not knowing things so I set to find out. The answer, as it often [...]

I’ve always been aware of SQLCMD mode in SQL Server Management Studio but until a few days ago I never considered using it. So what is SQLCMD? SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and [...]

This error message really irritates me. Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated. The statement has been terminated. I should probably open a Microsoft Connect item about this but would it really be that hard to tell you the column name? When you’re importing a data from [...]

Recently I needed to setup a SQL Server box so it had access to a mapped drive to support a legacy application. I created the below stored procedure, which utilises the subst command. to get this done. ?View Code TSQLCREATE PROCEDURE usp_mapDDrive AS BEGIN EXEC master.dbo.xp_cmdshell ‘Subst d: c:\’, no_output; END GO I needed a [...]

There’s few SQL techniques you seem to keep in the cupboard gathering dust. I don’t think I’ve ever needed to use RIGHT JOIN outside of the classroom. I can recall using FULL OUTER JOIN, just once, to show an employer how not-in-sync their "integrated system" was. Today I broke my professional Non-Equi JOIN cherry! I [...]

I’ve blogged before about documenting databases. I’m very much a fan of extracting documentation from systems themselves so it’s as up-to-date as it can be. That’s probably why I’m such a big fan of Powershell a tool that excels at this task. This week I was thinking about how to get at the comments often [...]

If you ever encounter this SQL Server error when selecting from a view then somebody has probably dropped columns from the base table. Here’s a quick run through of the problem. ?View Code TSQLCREATE TABLE dbo.Contact ( Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DOB DATETIME [...]

Today I received the following SQL Server error which I had never encountered before. Msg 4429, Level 16, State 1, Line 1 View or function ‘Table_1′ contains a self-reference. Views or functions cannot reference themselves directly or indirectly. Msg 4413, Level 16, State 1, Line 1 Could not use view or function ‘Test.Table_1′ because of [...]

This week I was tasked with testing a stored procedure that was meant to output data on certain days. This was over a 120 day period, so I wanted to find some automated way of doing this, rather than changing the server date manually for each execution. The method I came up with involves the [...]