MSSQL - Saving Changes in Not Permitted

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

When getting this error in SQL Server Management Studio 2008, be aware that when you redesign a table is a way that affects it's structure, the action requires the table to be recreated. By default the Database Management Studio will not allow you to recreate it, therefore no changes allowed.

The solution is simple.
1) Go to Tools -> Options
2) Select the tab Designers and inside of it Tables and designers
3) Uncheck Prevent saving changes that require table re-creation
4) Save


This configuration is very obvious, but the path to it is rather obscure so it is here for future reference.

Count entries in a SQL table by minute

Imagine that you need to count entries on a table by date. That query is rather obvious and probably you already made it sometimes. And what if you need to count entries on a table by minute? For instance, to determine the period when more orders are received.

select count(*), datepart(HH, lastchange), datepart(MI,lastchange)
from ORDERSTABLE
group by datepart(HH, lastchange), datepart(MI,lastchange)
order by datepart(HH, lastchange), datepart(MI,lastchange)

Running that query two things are immediately wrong. The hours separated from minutes, the minutes with one digit, and some periods have no info and therefore no entry saying 0.


The first step is to fill the blanks with 0. For that, we'll generate a temporary table with all the hours and minutes from 0:00 until 23:59. We'll be using Common Table Expressions and a temporary table for that.
Just pick a day, and use DateAdd to increase it minute by minute. With Recursion it will fill the entire table based on that rule.

WITH mycte AS(
SELECT CAST('2011-01-01 0:00' AS DATETIME) DateValue
UNION ALL
SELECT DATEADD(MINUTE, 1, DateValue)
FROM mycte
WHERE DateValue < '2011-01-02'
)

SELECT DATEPART(HH,DateValue) hours,DATEPART(MI,DateValue) minutes
INTO #temptable
FROM mycte OPTION (MAXRECURSION 0)

Finally, we'll left join the complete table and our results, and write the time in a proper format.

SELECT REPLICATE('0',2-LEN(hours))+CAST(hours AS VARCHAR)+
':'+REPLICATE('0',2-LEN(minutes))+CAST(minutes AS VARCHAR),COUNT(lastchange)
FROM #temptable
LEFT JOIN ORDERSTABLE ON hours = DATEPART(HH, lastchange) AND minutes=DATEPART(MI,lastchange)
GROUP BY hours, minutes
ORDER BY hours, minutes

Don't forget to drop the temporary table.

DROP TABLE #temptable

And that's it.