Fun fact – SQL server truncates

Tl;dr – SQL server 2008R2’s results to grid output truncates string values above 43,680.

Conclusion: if half of your script is missing, it might still be there.

Today was the day

When the server ran out of both memory and disk space. On both drives.

Running sum for last 12 months, SQL style

I’m going to tell you a little secret. Come, sit closer.

You know your running chart for every other dashboard, showing

the SUM / Average / Top / Least / etc.

of Sales / New customers / Quotes  / etc.

for the last…

1 hour / 1 day / 1 week / 4 weeks / 12 months / etc…. ?

In SQL world I’ve been solving the problem with dateadd() getdate().

SELECT
sum(case when month(orderDate) = dateadd( mm, -1, getdate()) then orderAmount end) as [total order amount last month]
FROM
GoatOrders
GROUP BY
month(orderDate)

Of course if you want your chart to contain the last 12 months, you’d have to repeat the select 12 times. No brainer, redundant query.

Here comes this little trick I’ve been using:

SELECT
SUM(orderAmount), month(orderDate)
FROM
GoatOrders
WHERE
orderDate >= dateadd(yy, -1, getdate())
GROUP BY
month(orderDate),
year(orderDate)
ORDER BY –this is where the magic happens
year(orderDate) desc,
month(orderDate) desc

The ORDER BY has it all.

On a completely unrelated note: USA won 2-1 against Ghana!

Conclusion: Now you know how to write even shorter code for your running charts.

Can’t save hosts file under Win Server 2008 r2

I’m just saving this for myself for future reference, and sharing the info with you in case you would run into the same issue.

Brian Smith here has the solution for us, let me clean my throat and gesture him to take the podium:

Click on the windows button, then type in “notepad” in the search box, you will see the notepad icon near the top of the window.  Right click it, “run as administrator” then open the file “%windir%\System32\drivers\etc\hosts” then go ahead and edit it.

Thank you for your valuable input, Brian.

Conclusion: Windows’ security is still stupid easy to bypass.

SQL Server – move database files

By definition SQL Server uses the install directory to store its data, unless you’ve taken care of that aspect during installation. The default directory is

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\DATA

Initially, that solution is absolutely fine, until you realize that SQL Server and its rapidly expanding databases are slowly eating up the disk space dedicated for your OS. That’s when you want to move your databases to another location. Luckily, that’s a rather easy procedure, easier than moving your iTunes database. (Which is a complete pain. Take note, Apple.)

The steps:

1. In SSMS, run the following:

USE MASTER;
GO
ALTER DATABASE MyAwesomeDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N’MyAwesomeDatabase’
GO

That will detach your database from the files.

2. Move the database files from their current location to their new home. You’ll want to copy both files associated with the database, in our case the two files are:

MyAwesomeDatabase.mdf
MyAwesomeDatabaselog.ldf

3. To relink the files with your database, run the following:

CREATE DATABASE [CRM] ON
( FILENAME = N’D:\Data\MyAwesomeDatabase.mdf.mdf’ ),
( FILENAME = N’D:\Data\MyAwesomeDatabase.mdf.ldf’ )
FOR ATTACH
GO

4. Ta-dah! That’s it.

ps – the Internetz is a redundant space. There are two tutorials on this topic, one on Technet, the other one on SQLAuthority. As one would expect, the Microsoft tutorial is absolutely confusing and impossible to follow. The latter on the other hand is an excellent tutorial, as matter of fact you should spend some time reading some of the articles you find there. Full of good stuff.

Conclusion: Now you know how to move your database files.